Write the Code. Change the World.

8月 05

0.00 avg. rating (0% score) - 0 votes

一般操作excel 有两种方式,
一种是画好表格了,填固定的空,这种情况直接找到格子,填入就行了
另外一种是只画好表头了,但实际表内容的行数是不固定的,需要程序根据数据的实际行数来添加行。
这种加行在 XSSF模式下,直接写入单元格会报错。要先加行,再加单元格,再写入,才可以。

public class ExcelUtil {
    private XSSFWorkbook wb = null;

    public void OpenXLSXFile(String FilePath) throws IOException {
        InputStream ExcelFileToRead = new FileInputStream(FilePath);
        wb = new XSSFWorkbook(ExcelFileToRead);
    }

    public void CreateRow(String SheetName, int rownum) {
        Sheet ws = wb.getSheet(SheetName);
        ws.createRow(rownum);
    }

    public void SetCellValue(String SheetName, String CellName, double value) {
        Sheet ws = wb.getSheet(SheetName);
        CellReference cr = new CellReference(CellName);
        Row row = ws.getRow(cr.getRow());
        Cell cell = row.getCell(cr.getCol());
        if (cell == null) {
            cell = row.createCell(cr.getCol());
        }
        cell.setCellValue(value);
    }

    public void SetCellValue(String SheetName, String CellName, String Value) {
        Sheet ws = wb.getSheet(SheetName);
        CellReference cr = new CellReference(CellName);
        Row row = ws.getRow(cr.getRow());
        Cell cell = row.getCell(cr.getCol());
        if (cell == null) {
            cell = row.createCell(cr.getCol());
        }
        cell.setCellValue(Value);

    public void SaveAs(String FilePath) {
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(FilePath);
            // 保存之前刷新公式
            XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
            wb.write(fileOut);
            fileOut.flush();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {

            try {
                wb.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                fileOut.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    }

参考
writing a new cell to a sheet apache poi
https://stackoverflow.com/questions/12754641/writing-a-new-cell-to-a-sheet-apache-poi
Java POI(第一讲):POI中HSSF用法大全
https://blog.51cto.com/zangyanan/1837229
How to insert a row between two rows in an existing excel with HSSF (Apache POI)
https://stackoverflow.com/questions/5785724/how-to-insert-a-row-between-two-rows-in-an-existing-excel-with-hssf-apache-poi
POI Excel 插入新的行,下面的行动态移动
https://www.cnblogs.com/zwdx/p/7878523.html
POI shiftRows方法
https://www.cnblogs.com/staticxy/p/6122336.html

© 2019, 新之助meow. 原创文章转载请注明: 转载自http://www.xinmeow.com

0.00 avg. rating (0% score) - 0 votes