POI excel export and poiexcel Export

POI excel export and poiexcel Export

**************************************** **************************************** **********

Export an excel table using POI

**************************************** **************************************** **********

 

Export Steps

-- Configure and export an excel template. We recommend that you use the 03 xls format, which is compatible with the advanced version.

 

-- Process the exported data List <Map <String, String>

private List<Map<String, String>> getData(){    List<Map<String, String>> data = new ArrayList<>();    Map<String, String> map = null;    for (int i = 0; i < 5; i++) {        map = new HashMap<String, String>();        map.put("listNo", i + "");        map.put("userName", "name" + i);        map.put("userAge", "" + (i + 20));        data.add(map);    }    return data;}

 

-- Get the template xls file and read it to the HSSFWorkbook object through the input stream

private HSSFWorkbook workbookTemplate() {    File file = new File("f:/etom/pmms/src/test/resources/bdRoute.xls");    InputStream is = null;    HSSFWorkbook workbook = null;    try {        is = new FileInputStream(file);        workbook = new HSSFWorkbook(is);    } catch (IOException e) {        e.printStackTrace();    }finally {        if (null != is) {            try {                is.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    return workbook;}

 

-- Obtain the tab using the page Signature

Private HSSFSheet getSheet (HSSFWorkbook workbook) {String sheetName = "User Information"; HSSFSheet sheet = workbook. getSheet (sheetName); if (null! = Sheet) {return sheet;} return null ;}

 

-- Obtain rules, such as $ {userName}

private String getValue(HSSFCell cell){    int type = cell.getCellType();    switch (type) {        case Cell.CELL_TYPE_NUMERIC:            return Double.toString(cell.getNumericCellValue());        case Cell.CELL_TYPE_BOOLEAN:            return Boolean.toString(cell.getBooleanCellValue());        case Cell.CELL_TYPE_STRING:            return cell.getStringCellValue();        default:            return "";    }}

 

-- Set rules, such as $ {userName}

private void setCellRule(HSSFRow firstDataRow, HSSFRow newRow) {    HSSFCell cellInRule = null;    HSSFCell newCell = null;    for (int i = 0, cellNum = firstDataRow.getLastCellNum(); i < cellNum; i++) {        cellInRule = firstDataRow.getCell(i);        newCell = newRow.createCell(i);        HSSFCellStyle style = cellInRule.getCellStyle();        newCell.setCellStyle(style);        String rule = getValue(cellInRule);        newCell.setCellValue(rule);    }}

 

-- Set the tab template to copy the content rows. The number of rows is consistent with the number of data rows.

// The number of rows to be copied by the loop Based on rowCount. In the preceding example, the row to be copied starts from row 4.
Private void setSheet (HSSFSheet sheet, int loop, int rowCount ){
HSSFRow newRow = null;
HSSFRow firstDataRow = sheet. getRow (loop-1 );
For (int I = loop, maxRow = loop + rowCount; I <maxRow-1; I ++ ){
NewRow = sheet. createRow (I );
SetCellRule (firstDataRow, newRow );
}
}

 

-- Specify the cell content

private void setCellValue(HSSFRow row, Map<String, String> rowData) {    for (int i = 0, cellNum = row.getLastCellNum(); i < cellNum; i++) {        HSSFCell cell = row.getCell(i);        if (null == cell) {            continue;        }        String oldValue = getValue(cell);        Pattern pattern = Pattern.compile(patternRule);        Matcher matcher = pattern.matcher(oldValue);        if (matcher.find()) {            String key = matcher.group(1);            String value = rowData.getOrDefault(key, "");            cell.setCellValue(value);        }    }}

 

-- Set the row content (by setting the cell content)

// Set the content. The unit range starts from the loop row to the end of the loop + data. size () Row.
Private void setValue (HSSFSheet sheet, int loop, List <Map <String, String> data ){
HSSFRow row = null;
For (int I = loop-1, rowNum = loop + data. size (), index = 0; I <rowNum-1; I ++ ){
Row = sheet. getRow (I );
SetCellValue (row, data. get (index ++ ));
}
}

 

-- Generate an excel file in the xls Format

private void createExcel(HSSFWorkbook workbook,String targetPath){    File excelFile = new File(targetPath);    OutputStream os = null;    try {        os = new FileOutputStream(excelFile);        workbook.write(os);    } catch (IOException e) {        e.printStackTrace();    }finally {        if (null != os) {            try {                os.flush();                os.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }}

 

-- Test

@ Testpublic void test () {List <Map <String, String> data = getData (); // obtain the exported data String templatePath = "f: /etom/pmms/src/test/resources/user.xls "; // template address: HSSFWorkbook workbook = workbookTemplate (templatePath ); // convert the template to the HSSFWorkbook object HSSFSheet sheet = getSheet (workbook); // obtain the template tab setSheet (sheet, 5, data. size (); // setValue (sheet, 5, data) on the settings tab; // set the cell content Long timeMillis = System. currentTimeMillis (); // export the file name String targetPath = String. format ("f:/etom/pmms/src/test/resources/eclips.xls", Long. toString (timeMillis); // export file xls address createExcel (workbook, targetPath); // export file}

 

-- Result Display

 

-- Merge Cells

Private void setRegion (HSSFSheet sheet ){
Int firstRow = 1; // start row of the Unit
Int lastRow = 2; // terminate the row in the cell
Int firstCol = 3; // start column of the cell
Int lastCol = 4; // cell termination Column
CellRangeAddress cellRangeAddress = new CellRangeAddress (firstRow, lastRow, firstCol, lastCol );
Sheet. addMergedRegion (cellRangeAddress );
}