我就简单的分享一下我常用的工具

这次由于个人问题工具注释全部乱码差点无法还原，也是为了防止数据丢失后期找不到再次保留方法把。

调用工具个别方法

<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
             <artifactId>poi-ooxml</artifactId>
             <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>


public void supplierBankExport(HttpServletResponse response, HttpSession session) throws Exception {
        String typeName="表名";
        String username = "admin";
        String sheetName = "sheet";
        String[] titles=new String[11];
        titles[0]="BBB";//B
        titles[1] = "CCC";//C
    List<Map<String, Object>>  mapData=这个是DAO层获取的数据
    //创建表头
    HSSFWorkbook wb = ExportTool.getHSSFWorkbook(sheetName, titles, null);
    HSSFSheet sheet1 = wb.getSheet(sheetName+ "1");

    for (int n = mapData.size(), i = 1; i < n; i++) {
        HSSFRow row1 = sheet1.createRow(i);

        HSSFCell cell0 = row1.createCell(0);//BBB
        if (mapData.get(i).get("BBB") != null) {
            HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(mapData.get(i).get("BBB")));
            cell0.setCellValue(richString);
        }
        HSSFCell cell1 = row1.createCell(1);//CCC
        if (mapData.get(i).get("CCC") != null) {
            HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(mapData.get(i).get("CCC")));
            cell1.setCellValue(richString);
        }
    }
    //创建文档摘要信息
    ExportTool.setTextInfo(wb, username,typeName+"信息批量添加",typeName+"信息批量添加",typeName+"信息批量添加");
    try {
        String fileName = typeName+"批量新增模板" + System.currentTimeMillis();
        // 设定输出文件头
        response.setHeader("Content-disposition", "attachment; filename=" + new String((fileName + ".xls").getBytes("GBK"), "ISO8859-1"));
        // 定义输出类型
        response.setContentType("application/msexcel");
        // 取得输出流
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}


import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

public class ExportTool {
    /**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        String hiddenSheetName = "sheet1" + workbook.getNumberOfSheets();
        System.out.println(hiddenSheetName);
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
        String nameName = "";
        if (Integer.valueOf(column) <= 116 && Integer.valueOf(column) >90) {
            nameName ="A" + ((char) (column-26)) + "_parent";
        } else {
            nameName = column + "_parent";
        }
        int rowIndex = 0;
        for (Object option : options) {
            int columnIndex = 0;
            Row row = optionsSheet.createRow(rowIndex++);
            Cell cell = row.createCell(columnIndex++);
            cell.setCellValue(option.toString());
        }

        createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);

        DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
    }

    /**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet1" + workbook.getNumberOfSheets();
        System.out.println(hiddenSheetName);
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        List<String> firstLevelItems = new ArrayList<String>();

        int rowIndex = 0;
        for (Map.Entry<String, List<String>> entry : options.entrySet()) {
            String parent = formatNameName(entry.getKey());
            firstLevelItems.add(parent);
            List<String> children = entry.getValue();

            int columnIndex = 0;
            Row row = hiddenSheet.createRow(rowIndex++);
            Cell cell = null;

            for (String child : children) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(child);
            }

            char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

            DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
        }

        addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);

    }

    /**
     * 根据用户在keyColumn选择的key, 自动填充value到valueColumn
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param keyValues   匹配关系 {'百度','www.baidu.com'},{'淘宝','www.taobao.com'}
     * @param keyColumn   要匹配的列（例如 网站中文名称）
     * @param valueColumn 匹配到的内容列（例如 网址）
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addAutoMatchValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, String> keyValues, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet1" + workbook.getNumberOfSheets();
        System.out.println(hiddenSheetName);
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);

        // init the search region(A and B columns in hiddenSheet)
        int rowIndex = 0;
        for (Map.Entry<String, String> kv : keyValues.entrySet()) {
            Row totalSheetRow = hiddenSheet.createRow(rowIndex++);

            Cell cell = totalSheetRow.createCell(0);
            cell.setCellValue(kv.getKey());

            cell = totalSheetRow.createCell(1);
            cell.setCellValue(kv.getValue());
        }

        for (int i = fromRow; i <= endRow; i++) {
            Row totalSheetRow = targetSheet.getRow(i);
            if (totalSheetRow == null) {
                totalSheetRow = targetSheet.createRow(i);
            }

            Cell cell = totalSheetRow.getCell((int) valueColumn - 'A');
            if (cell == null) {
                cell = totalSheetRow.createCell((int) valueColumn - 'A');
            }

            String keyCell = String.valueOf(keyColumn) + (i + 1);
            String formula = String.format("IF(ISNA(VLOOKUP(%s,%s!A:B,2,0)),\"\",VLOOKUP(%s,%s!A:B,2,0))", keyCell, hiddenSheetName, keyCell, hiddenSheetName);

            cell.setCellFormula(formula);
        }

        // init the keyColumn as comboList
        addValidationToSheet(workbook, targetSheet, keyValues.keySet().toArray(), keyColumn, fromRow, endRow);
    }

    /**
     * 创建表单名
     *
     * @param workbook 表
     * @param nameName 名称
     * @param formula  不知道
     * @return 表单名
     */
    public static Name createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
        return name;
    }

    /**
     * 隐藏excel中的sheet页
     *
     * @param workbook
     * @param start    需要隐藏的 sheet开始索引
     */
    public static void hideTempDataSheet(HSSFWorkbook workbook, int start) {
        for (int i = start; i < workbook.getNumberOfSheets(); i++) {
            workbook.setSheetHidden(i, true);
        }
    }

    /**
     * 不可数字开头
     *
     * @param name
     * @return
     */
    public static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }

        return name;
    }

    /**
     * 隐藏列
     *
     * @param sheet
     * @param hiddenColumns
     */
    public static void hideColumns(Sheet sheet, List<Integer> hiddenColumns) {
        if (null != hiddenColumns && hiddenColumns.size() > 0) {
            for (Integer hiddenColumn : hiddenColumns) {
                sheet.setColumnHidden(hiddenColumn, true);
            }
        }
    }

    /**
     * 设置表头
     *
     * @param sheetName 表单名称
     * @param titles    表头数据
     * @param pichText  批注数据
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] titles, String[] pichText) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet1 = workbook.createSheet(sheetName + "1");
        HSSFSheet sheet2 = workbook.createSheet(sheetName + "2");
        // 设置表格默认列宽度为15个字节
        sheet1.setDefaultColumnWidth(15);
        sheet2.setDefaultColumnWidth(15);
        /**
         * 表格标题样式
         */
        HSSFCellStyle style = workbook.createCellStyle();
        //true表示自动换行
        style.setWrapText(true);
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        /**
         * 标题字体
         */
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        /**
         * 表格头部样式
         */
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        /**
         * 表格头部字体
         */
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        font2.setColor(HSSFColor.BLACK.index);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        /**
         * 表格内容样式
         */
        HSSFCellStyle style3 = workbook.createCellStyle();
        style3.setFillForegroundColor(HSSFColor.WHITE.index);
        style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFRow row1 = sheet1.createRow(0);
        for (int n = titles.length, i = 0; i < n; i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellStyle(style2);
            if (titles[i] != null) {
                HSSFRichTextString richString = new HSSFRichTextString(titles[i]);
                cell.setCellValue(richString);

//                设置批注
                if (pichText != null && pichText.length == titles.length) {
                    setRichText(sheet1, cell, pichText[i]);
                }
            }
        }
        return workbook;
    }

    /**
     * 批注设置
     *
     * @param sheet 表单
     * @param cell  单元格
     * @param text  批注内容
     */
    public static void setRichText(HSSFSheet sheet, HSSFCell cell, String text) {
        HSSFPatriarch h = sheet.createDrawingPatriarch();
        //创建批注位置
        HSSFClientAnchor anchor = h.createAnchor(0, 0, 0, 0, cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getColumnIndex() + 3, cell.getRowIndex() + 4);
        //创建批注
        HSSFComment comment = h.createCellComment(anchor);
        //设置批注内容
        comment.setString(new HSSFRichTextString(text));
        //设置批注作者
        comment.setAuthor("admin");
        //设置批注默认显示

//    comment.setVisible(true);
        comment.setVisible(false);
        //把批注赋值给单元格
        cell.setCellComment(comment);
    }

    /**
     * 文档摘要信息
     *
     * @param workbook 表
     * @param username 用户名
     */
    public static void setTextInfo(HSSFWorkbook workbook, String username,String subject,String title,String comments) {
        //创建文档信息
        workbook.createInformationProperties();
        //摘要信息
        DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
        //类别
        dsi.setCategory("Excel");
        //管理者
        dsi.setManager("管理者:admin");
        dsi.setCompany("XXXX");//公司
        //摘要信息
        SummaryInformation si = workbook.getSummaryInformation();
        //主题
        si.setSubject(subject==null?"":subject);
        //标题
        si.setTitle(title==null?"":title);
        //作者
        si.setAuthor(username);
        //备注
        si.setComments(comments==null?"":comments);
    }

}