JAVA poi export to excel

Import java. io. IOException;
Import java. io. OutputStream;
Import java. util. Date;
Import java. util. HashMap;
Import java. util. Map;

Import org. apache. commons. io. IOUtils;
Import org. apache. poi. hssf. usermodel. HSSFCellStyle;
Import org. apache. poi. ss. usermodel. CellStyle;
Import org. apache. poi. ss. usermodel. DataFormat;
Import org. apache. poi. ss. usermodel. Row;
Import org. apache. poi. ss. usermodel. Workbook;
Import org. apache. poi. ss. util. CellRangeAddress;
Import org. apache. poi. xssf. usermodel. XSSFWorkbook;

Public final class ExcelWriter {
Public static final class Cell {
Private Workbook wb;
Private org. apache. poi. ss. usermodel. Cell target;

Private Cell (Workbook wb, org. apache. poi. ss. usermodel. Cell cell ){
This. wb = wb;
This.tar get = cell;
}
Public void setCellValue (String value ){
This.tar get. setCellValue (value );
}
Public void setCellValue (Date value ){
If (null = value ){
This. setCellValue ("not set ");
Return;
}
CellStyle style = this. wb. createCellStyle ();
DataFormat format = wb. createDataFormat ();
Style. setDataFormat (format. getFormat ("yyyy/m/d hh: mm: ss "));
This.tar get. setCellStyle (style );
This.tar get. setCellValue (value );
}
Public void setCellValue (double value ){
CellStyle style = this. wb. createCellStyle ();
DataFormat format = wb. createDataFormat ();
Style. setDataFormat (format. getFormat ("###,#0 "));
This.tar get. setCellStyle (style );
This.tar get. setCellValue (value );
}

Public void setCellStyle (CellStyle style ){
This.tar get. setCellStyle (style );
}
}

Public static final class Sheet {
Private Workbook wb;
Private org. apache. poi. ss. usermodel. Sheet sheet;
Private Sheet (Workbook wb, org. apache. poi. ss. usermodel. Sheet sheet ){
This. sheet = sheet;
This. wb = wb;
}

Public Cell getOrCreateCell (int rowBasedZero, int cellBasedZero ){
Row row = this. sheet. getRow (rowBasedZero );
If (null = row ){
Row = this. sheet. createRow (rowBasedZero );
}
Return new Cell (this. wb, row. getCell (cellBasedZero, Row. CREATE_NULL_AS_BLANK ));
}

// Merge Cells
Public void mergedRegion (int intFirstRow, int intLastRow, int intFirstColumn, int intLastColumn ){
This. sheet. addMergedRegion (new CellRangeAddress (intFirstRow, intLastRow, intFirstColumn, intLastColumn ));
}

// Add a centered Style
Public CellStyle addStyle (){
CellStyle style = wb. createCellStyle ();
Style. setVerticalAlignment (HSSFCellStyle. VERTICAL_CENTER); // vertical
Style. setAlignment (HSSFCellStyle. ALIGN_CENTER); // horizontal
Return style;
}

}
Private Map <String, Sheet> sheetMap = new HashMap <String, Sheet> ();
Private OutputStream out;
Private Workbook wb;

Public ExcelWriter (OutputStream out ){
This. out = out;
Wb = new XSSFWorkbook ();
}

Public Sheet getOrCreateSheet (String sheet ){
Sheet sheet1 = this. sheetMap. get (sheet );
If (null! = Sheet1 ){
Return sheet1;
}
Org. apache. poi. ss. usermodel. Sheet xlsSheet = this. wb. getSheet (sheet );
If (null = xlsSheet ){
XlsSheet = this. wb. createSheet (sheet );
}
Sheet obj = new Sheet (this. wb, xlsSheet );
This. sheetMap. put (sheet, obj );
Return obj;
}

Public void write () throws IOException {
This. wb. write (this. out );
}

Public void close (){
This. sheetMap. clear ();
IOUtils. closeQuietly (this. out );
}
}

 

Service layer processing:

Public byte [] findExcelData () throws NetflowServiceException {
ByteArrayOutputStream out = new ByteArrayOutputStream ();
ExcelWriter writer = null;
Try {
Writer = new ExcelWriter (out );
/* Assemble the excel header */
ExcelWriter. Sheet sheetAct = writer. getOrCreateSheet (informationPublishForm. getOrdertitle ());

For (int I = 0; I <excelHead. size (); I ++ ){
SheetAct. getOrCreateCell (0, I). setCellValue (excelHead. get (I ));
}

/* Assemble excel content */
For (int I = 0; I <list. size (); I ++ ){
SheetAct. getOrCreateCell (I + 1), j). setCellValue (value );
}
}
Writer. write ();
} Catch (Exception e ){
Throw new NetflowServiceException (e );
} Finally {
If (null! = Writer ){
Writer. close ();
}
}
Return out. toByteArray ();
}

Action processing:

Public StringdownExcel (){
Try {
Byte [] fileNameByte = (informationPublishForm. getOrdertitle () + ". xlsx"). getBytes ("GBK ");
String filename = new String (fileNameByte, "ISO8859-1 ");

Byte [] bytes = commonTableReportService. findExcelData (psForm, reportId );
Response. setContentType ("application/x-msdownload ");
Response. setContentLength (bytes. length );
Response. setHeader ("Content-Disposition", "attachment; filename =" + filename );
Response. getOutputStream (). write (bytes );
Log.info ("generate excel file size (byte):" + bytes. length );
} Catch (Exception e ){
Log. error (e. getMessage (), e );
}
}