编程知识 cdmana.com

Java daily work poi utils

Make a note of what you use at work poi( It can be used as a tool class )

/**
 *  Handle excel Read in tool class 
 * Created by Liujishuai on 2015/8/5.
 */
public class ExcelUtils {
    /**
     *  requirement excel Version in 2007 above 
     *
     * @param file  file information 
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(File file) throws Exception {
        if(!file.exists()){
            throw new Exception(" No files found ");
        }
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        //  Read the contents of the first table 
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        //String Type return String data 
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        // Date data returns LONG The timestamp of the type 
                        if ("yyyy\" year \"m\" month \"d\" Japan \";@".equals(cell.getCellStyle().getDataFormatString())) {
                            //System.out.println(cell.getNumericCellValue()+": Date format :"+cell.getCellStyle().getDataFormatString());
                            value = DateUtils.getMillis(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())) / 1000;
                        } else {
                            // Numeric type returns double Type of number 
                            //System.out.println(cell.getNumericCellValue()+": Format :"+cell.getCellStyle().getDataFormatString());
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        // Boolean type 
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        // Empty cells 
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    // Cell is not empty , Then add it to the list 
                    linked.add(value);
                }
            }
            if (linked.size()!= 0) {
                list.add(linked);
            }
        }
        return list;
    }
    /**
     *  requirement excel Version in 2007 above 
     *
     * @param fileInputStream  file information 
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(FileInputStream fileInputStream) throws Exception {
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
        //  Read the contents of the first table 
        XSSFSheet sheet = xwb.getSheetAt(1);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if ("yyyy\" year \"m\" month \"d\" Japan \";@".equals(cell.getCellStyle().getDataFormatString())) {
                            //System.out.println(cell.getNumericCellValue()+": Date format :"+cell.getCellStyle().getDataFormatString());
                            value = DateUtils.getMillis(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())) / 1000;
                        } else {
                            //System.out.println(cell.getNumericCellValue()+": Format :"+cell.getCellStyle().getDataFormatString());
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    // Cell is not empty , Then add it to the list 
                    linked.add(value);
                }
            }
            if (linked.size()!= 0) {
                list.add(linked);
            }
        }
        return list;
    }
 
    /**
     *  export excel
     * @param excel_name  Derived excel route ( Need to bring .xlsx)
     * @param headList  excel Title note name of 
     * @param fieldList excel The title field of ( And in the data map The key value corresponds to )
     * @param dataList  excel data 
     * @throws Exception
     */
    public static void createExcel(String excel_name, String[] headList,
                                   String[] fieldList, List<Map<String, Object>> dataList)
            throws Exception {
        //  Create a new Excel  workbook 
        XSSFWorkbook workbook = new XSSFWorkbook();
        //  stay Excel Create a worksheet in the workbook , It's called the default value 
        XSSFSheet sheet = workbook.createSheet();
        //  In the index 0 Create a line at the location of ( The top line )
        XSSFRow row = sheet.createRow(0);
        //  Set up excel head ( first line ) The header name of 
        for (int i = 0; i < headList.length; i++) {
 
            //  In the index 0 The location where the cell was created ( Upper left )
            XSSFCell cell = row.createCell(i);
            //  Define cells as string type 
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            //  Enter something in the cell 
            cell.setCellValue(headList[i]);
        }
        // ===============================================================
        // Add data 
        for (int n = 0; n < dataList.size(); n++) {
            //  In the index 1 Create a line at the location of ( The top line )
            XSSFRow row_value = sheet.createRow(n + 1);
            Map<String, Object> dataMap = dataList.get(n);
            // ===============================================================
            for (int i = 0; i < fieldList.length; i++) {
 
                //  In the index 0 The location where the cell was created ( Upper left )
                XSSFCell cell = row_value.createCell(i);
                //  Define cells as string type 
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                //  Enter something in the cell 
                cell.setCellValue((dataMap.get(fieldList[i])).toString());
            }
            // ===============================================================
        }
        //  Create a new output file stream 
        FileOutputStream fos = new FileOutputStream(excel_name);
        //  Put the corresponding Excel  Save the workbooks 
        workbook.write(fos);
        fos.flush();
        //  End of operation , Close file 
        fos.close();
    }
}

In the use of poi Before importing the relevant jar package (poi Of jar The role of package )

image.png
Import... Before use jar package

<!--poi Yes excel2007 Support for the above versions -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>

In the use of poi When importing excel I encountered ### java.lang.ClassNotFoundException abnormal
The solution is :
Imported jar package

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>4.1.2</version>
</dependency>
<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml-schemas</artifactId>
 <version>4.1.2</version>
</dependency>

Pay attention to the version , Don't create version conflicts .

版权声明
本文为[Sunshiny]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224214844060I.html

Scroll to Top