POI操作Excel

• 获取Excel对象，并写值
• 如何插入行？
• 如何保存更改或下载

POI操作Excel，读取Excel并对单元格进行赋值

//设置文件路径
String filePath = "xxx";
//根据文件路径获取输入流
FileInputStream input = new FileInputStream(new File(filePath));
//获取文档对象
Workbook wb = WorkbookFactory.create(input);
//获取第一个Sheet页，也有方法可以根据Sheet的名字获取
Sheet sheet = wb.getSheetAt(0);
//获取该Sheet的第一行对象
Row row = sheet.getRow(1);
//获取第一个单元格元素
 Cell cell = row2.getCell(0);
//设置单元格的值
cell.setCellValue("your value");

当需要插入行时，需要两步

1 先用shiftRows方法将行向下移动，
/**
	参数1：startRow从第几行开始移动，
	参数2：从参数一的行数到sheet的最后一行，一并向下移动
	参数3：count：移动几行
	参数4与参数5与行高有关，是Boolean类型，根据情况自行设置
 * Shifts rows between startRow and endRow n number of rows.
 * If you use a negative number, it will shift rows up.
 * Code ensures that rows don't wrap around
 *
 * <p>
 * Additionally shifts merged regions that are completely defined in these
 * rows (ie. merged 2 cells on a row to be shifted).
 * <p>
 * @param startRow the row to start shifting
 * @param endRow the row to end shifting
 * @param n the number of rows to shift
 * @param copyRowHeight whether to copy the row height during the shift
 * @param resetOriginalRowHeight whether to set the original row's height to the default
 */
   void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);

2 当移动行后，在Sheet对象中row会空出来几个比如从1~3  8~15.这时3~8是没有row对象的。
    需要我们来自己创建row对象，并依据上面的样式补充单元格。
	 Row rowSource = sheet.getRow(n);//复制哪一行的样式 n就是几
	//遍历中间的行数
    for (int i = startRow; i < startRow+count; i++)
    {
        Row rowInsert = sheet.createRow(i);
        rowInsert.setRowStyle(rowSource.getRowStyle());
        rowInsert.setHeight(rowSource.getHeight());
		//遍历每一行的单元格数
        for (int col = 0; col < rowSource.getLastCellNum(); col++)
        {
            Cell cellsource = rowSource.getCell(col);
            Cell cellInsert = rowInsert.createCell(col);
            cellInsert.setCellStyle(cellsource.getCellStyle());
            //这几句话是由于我需要合并单元格
            CellRangeAddress region = new CellRangeAddress(i, i, 3, 4);
            CellRangeAddress region2 = new CellRangeAddress(i, i, 6, 8);
            sheet.addMergedRegion(region);
            sheet.addMergedRegion(region2);
        }

    }

保存或下载

1 保存

//设置文件路径
String filePath = "xxx";
//根据文件路径获取输入流
FileInputStream input = new FileInputStream(new File(filePath));
Workbook wb = WorkbookFactory.create(input);
//根据文件路径获取输出流
FileOutputStream output = new FileOutputStream(new File(filePath));

//中间写文件的操作.....
    doSomeThing();

//写出
output.flush();
wb.write(output);
//关闭
output.close();
input.close();

2 下载

//处理Excel
private void dealExcel(HttpServletResponse response) {
    //设置文件路径
    String filePath = "xxx";
    //根据文件路径获取输入流
    FileInputStream input = new FileInputStream(new File(filePath));
    Workbook wb = WorkbookFactory.create(input);
	//中间写文件的操作.....
    doSomeThing();
    //下载Excel
    expotExcel(wb,"文件名",response);
    //关闭流
    input.close();
}

 //下载Excel
 private void   expotExcel(Workbook wb,String fileName,HttpServletResponse response) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        response.setContentType("application/octet-stream;charset=utf-8");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        ServletOutputStream outputStream = response.getOutputStream();
        try{
            wb.write(outputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            outputStream.close();
        }
    }