Use poi to export data to an Excel template

Exporting and importing data during development is often used. Recently, the company needs to import the specified data to a given excel template and merge cells as required. Here, I wrote a simple demo to summarize the knowledge points I have used for reference.

Download related Demo: poitest

Public class test {
Public static void main (string [] ARGs ){
Try {
Fileinputstream FCM = new fileinputstream ("D:/model.xlsx ");
Xssfworkbook workbook = new xssfworkbook (FS );
String filename = "test _" + system. currenttimemillis () + ". XLSX ";
Outputstream out = new fileoutputstream ("D:/" + filename );
Xssfcellstyle style = cellstyle. getstyle (workbook );
For (Int J = 0; j <2; j ++) {// export a workbook with multiple sheets
Xssfsheet sheet = Workbook. clonesheet (0); // clone the Template
Workbook. setsheetname (J + 1, "sheet" + J); // name sheet
Int rowindex = 8;
Xssfrow ROW = sheet. createrow (rowindex );
For (INT I = 1; I <23; I ++ ){
Xssfcell cell = row. createcell (I );
Cell. setcellvalue (I );

// Merge cells. The parameter is the starting row, ending row, starting column, and ending column.
Sheet. addmergedregion (New cellrangeaddress (rowindex, rowindex + 1, I, I ));
Cell. setcellstyle (style); // Add a style to the cell.
}
}
Workbook. removesheetat (0); // remove the template sheet from the workbook.
Workbook. Write (out );
FCM. Close ();
Out. Flush ();
Out. Close ();
} Catch (exception e ){
E. printstacktrace ();
}
}
}

Public class cellstyle {

Public static xssfcellstyle getstyle (xssfworkbook workbook ){

// Set the style;
Xssfcellstyle style = Workbook. createcellstyle ();
// Set the bottom border;
Style. setborderbottom (hssfcellstyle. border_thin );
// Set the background border color;
Style. setbottombordercolor (hssfcolor. Black. Index );
// Set the left border;
Style. setborderleft (hssfcellstyle. border_thin );
// Set the color of the Left Border;
Style. setleftbordercolor (hssfcolor. Black. Index );
// Set the right border;
Style. setborderright (hssfcellstyle. border_thin );
// Set the color of the right border;
Style. setrightbordercolor (hssfcolor. Black. Index );
// Set the top border;
Style. setbordertop (hssfcellstyle. border_thin );
// Set the top border color;
Style. settopbordercolor (hssfcolor. Black. Index );
// Set automatic line feed;
Style. setwraptext (false );
// Set the horizontal alignment style to center alignment;
Style. setalignment (hssfcellstyle. align_center );
// Set the vertical alignment style to center alignment;
Style. setverticalalignment (hssfcellstyle. vertical_center );
Return style;
}
}

Use poi to export data to an Excel template