POI操作EXCEL实战

一、POI概述

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

结构：

• HSSF － 提供读写Microsoft Excel格式档案的功能。
• XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

HSSF 常用于处理.xls（带宏/VBA的表格）
XSSF 常用于处理.xlsx （不带宏/VBA的表格）

• HWPF － 提供读写Microsoft Word格式档案的功能。
• HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
• HDGF － 提供读写Microsoft Visio格式档案的功能。

在这里我们以HSSF为例，进行介绍。

我们需要实现的功能是：
已经存在一个.xls的excel模板存放在本地，我们需要拿到这个模板，根据用户的不同，在上面动态写入用户信息，并从数据库中取出一堆信息，作为模板中某列单元格点击时触发弹框的内容，让用户可以在弹框中复选想要得信息。然后后台校验用户上传的表格，如果有错误，返回错误信息；没有错误，则存入数据库。

二、实战代码

//1. 找到我们存放模板的路径
//注：File.separator 与系统相关的分隔符字符，以方便在不同系统都能运行
String pathFrom = ".." + File.separator + "webserver" + File.separator + "webapps" + File.separator + "portal" + File.separator + "apps" + File.separator + AppIdConstant.APPID + File.separator + "model" + File.separator + "BatchElevator.xlsx";

//2. 获取到它
File fileIn = new File(pathFrom );

//3. 写好后的模板存放的路径
String pathTo = ".." + File.separator + "doccenter" + File.separator + AppIdConstant.APPID + File.separator + "downLoadEquInfoTemp";

//4. 获取到（如果不存在，则创建此路径）
File fileCheck = new File(pathTo );
if (!fileCheck .exists()) {
    if (!fileCheck .mkdir()) {
        throw new RuntimeException("创建目录失败！");
    }
}

//5. 创建我们的目标模板
File fileOut = new File(pathTo + File.separator + "新取一个名字.xlsx");

//6. fileIn复制到fileOut  可用文件流FileStreams操作，但是效率低。这里我们使用FileChannel进行复制
copyFile(fileIn, fileOut);

//方法内容如下：
private static void nioTransferCopy(File source, File target) throws IOException {
    FileChannel in = new FileInputStream(source).getChannel();
    FileChannel out = new FileOutputStream(target).getChannel();
    in.transferTo(0, in.size(), out);
    in.close();
    out.close();
}

//7.获取XSSFWorkbook对象并输出
FileInputStream is = new FileInputStream(fileOut.getPath());
XSSFWorkbook wb = new XSSFWorkbook(is);
wb.write(new FileOutputStream(fileOut.getPath()));

注：实际上有以下方法可以构造对象，建议使用is构建，之前用path构造，其实需要调用close()方法将新建的xslx对象关闭，而关闭了该对象之后无法将文件输出，如果不关闭该对象又一直占用着这个地址导致输出流会报空指针错误，所以后来使用了XSSFWorkbook(java.io.InputStream is)这个构造方法，也建议使用该构造方法，调用该方法之后及时将输入流关闭，再输出文件的时候就不会报空指针错误了。

三、如何用VBA做复选弹框

三、使用常用POI方法操作模板

1. 得到Excel常用对象

is = new FileInputStream(fileOut.getPath());
//得到Excel工作簿对象
XSSFWorkbook wb = new XSSFWorkbook(is);
//得到Excel工作表对象    
XSSFWorkbook sheet = wb.getSheetAt(0);   
//得到Excel工作表的行（下标从0开始）    
XSSFRow row = sheet.getRow(i);  
//得到Excel工作表指定行的单元格（下标从0开始） 
XSSFCell cell = row.getCell((short) j);
//得到单元格样式
cellStyle = cell.getCellStyle();  

2. 建立Excel常用对象

//创建Excel工作簿对象   
HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象     
HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行   
cellStyle = wb.createCellStyle();//创建单元格样式   
row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格   
row.createCell((short)0).setCellValue(1); //设置Excel工作表的值 

需要注意的是 getRow、getCell方法可能会获取不到未编辑过的单元格，报空指针，此时要用createRow、createCell方法！

3. 添加区域，合并单元格

//获取区域(下标从0开始)   
CellRangeAddress region= new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
// 到rowTo行columnTo的区域
sheet.addMergedRegion(region);      
//获得所有合并单元格的数量
int num = sheet.getNumMergedRegions();   

四、写入数据库数据

通过POI提供的基础方法，可以根据工作需要，写一个FilesUtil，或者创建一个FileProc实体类，提高工作效率。
举例：

/**
 * 处理.xlsx的sheet写入字典信息
 *
 * @param appId
 * @param dicName
 * @param sheet
 * @author fangwanchen
 * @date 2019/8/9 10:28
 */
public static void writeDicIntoXLSXSheet(String appId, String dicName, XSSFSheet sheet){
    JSONArray jsonArray = SDK.getDictAPI().queryValues(appId, dicName);
    if (jsonArray != null && jsonArray.size() > 0) {
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject obj = jsonArray.getJSONObject(i);
            String name = obj.getString("CNNAME");
            XSSFRow row = sheet.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(name);
        }
    }
}

//sheet1可以隐藏掉，sheet0下拉列表获取sheet1第一列的序列范围，ojbk
XSSFSheet sheetXMYT = wb.getSheetAt(1);
FilesUtil.writeDicIntoXLSXSheet(AppIdConstant.APPID, "MARKETSUBJECT.CODE.PURPOSE", sheetXMYT);