Because there are more reports to export recently , All of them become a tool class , The scenario used by this tool class is   Export according to the template provided Excel report form

And according to the template provided Sheet Page So as to realize multiple Sheet Page needs ,

When using this tool class , If the demand is every Sheet The data in the page is inconsistent , But the table style and template are the same

Then we only need to base on the actual situation sql To query the data source to be added ( Just change the data source )

The technology used is POI export , Because of the class , Currently only supported 2003 Version of Excel.

Please download the corresponding jar package !

If there is time later, it will be further improved , First time to write tools , If there is any imperfection, please forgive me !

Let's take a look at the template style and running results first , And then go straight to the code

This is a Excel The template style of

This is the export result

In terms of specific implementation demo

package com.sgcc.xyz.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem; import com.sgcc.uap.persistence.impl.HibernateDaoImpl; /**
* Report export tool class
*
* @author JYLiu
@ The rainy season in Paris
This tool is based on POI Yes Excel2003 Export the report This tool class can be implemented according to the template Excel Export of
* And according to the template provided Sheet Page So as to realize multiple Sheet Page needs
* When using this tool class , If the demand is every Sheet The data in the page is inconsistent , But the table style and template are the same
* Then we only need to base on the actual situation sql To query the data source to be added ( Just change the data source ) */
public class ExcelUtil { /**
* Export report according to template , You can export multiple Sheet page
*
* @param Derived Excel file name
* @param Template path ( The full path )
* @param data source
* @param Return request
* @param Generated Sheet The collection of page names
* @param The data source Map A collection of key value (key Value corresponding value The order of values should be consistent with the order of column names )
* @param Start Loop write data The number of rows ( Write data from the first few lines )
*/
public static void ExcelByModel(String ExcelName, String ModelURl, List<Map<String, String>> dataSource,
HttpServletResponse response, String[] sheetNames, String[] keyNames, int rowNum) throws Exception { // Set export Excel The export form of the report
response.setContentType("application/vnd.ms-excel");
// Set export Excel The response file name of the report
String fileName = new String(ExcelName.getBytes("utf-8"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
// Create an output stream
OutputStream fileOut = response.getOutputStream();
// Read template file path
File file = new File(ModelURl);
FileInputStream fins = new FileInputStream(file);
POIFSFileSystem fs = new POIFSFileSystem(fins);
// Read Excel Templates
HSSFWorkbook wb = new HSSFWorkbook(fs); // Set border style
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// Set the color of the border style
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setTopBorderColor(HSSFColor.BLACK.index); // Template page
HSSFSheet sheetModel = null;
// New Sheet page
HSSFSheet newSheet = null;
// Create lines
HSSFRow row = null;
// Create column
HSSFCell cell = null; // Cycle building Sheet page
for (int i = ; i < sheetNames.length; i++) {
// Read template in template Sheet What's on the page
sheetModel = wb.getSheetAt();
// Set up new Sheet Page name of
newSheet = wb.createSheet(sheetNames[i]);
// Copy the contents of the template to the new Sheet Page
copySheet(wb, sheetModel, newSheet, sheetModel.getFirstRowNum(), sheetModel.getLastRowNum()); // Get the new Sheet The first line in the page, in which the columns are assigned values
row=newSheet.getRow();
row.getCell().setCellValue(" This is the value assigned to the table code ");
// Be careful The merged cells should also be calculated according to the number of cells before merging
row.getCell().setCellValue(" This is the value assigned to the external code "); // Get the second column in the template , And the assignment
row=newSheet.getRow();
row.getCell().setCellValue(" Table name assignment ");
// Be careful The merged cells should also be calculated according to the number of cells before merging
row.getCell().setCellValue(" This is the value assigned to the system table "); // Traverse the data source Start writing data ( because Excel From 0 Start , So subtract one )
int num = rowNum - ;
for (Map<String, String> item : dataSource) {
// Loop traversal , New Construction Bank
row = newSheet.createRow((short) num);
// Judge how many columns of data there are
for (int j = ; j < keyNames.length; j++) {
// Set the data for each column Set the style of each column Set the value of each column
cell = row.createCell(j); cell.setCellStyle(style); cell.setCellValue(item.get(keyNames[j]));
}
num++;
}
// break Add break You can test to add just one Sheet Page
}
// Write stream
wb.write(fileOut);
// Closed flow
fileOut.close();
} /**
*
* @param Excel Workbook object
* @param Templates Sheet page
* @param newly build Sheet page
* @param The first line of the template page
* @param The last line of the template page
*/
private static void copySheet(HSSFWorkbook wb, HSSFSheet fromsheet, HSSFSheet newSheet, int firstrow, int lasttrow) { // Copy a cell style to a new cell
if ((firstrow == -) || (lasttrow == -) || lasttrow < firstrow) {
return;
}
// Copy merged cells
Region region = null;
for (int i = ; i < fromsheet.getNumMergedRegions(); i++) {
region = fromsheet.getMergedRegionAt(i);
if ((region.getRowFrom() >= firstrow) && (region.getRowTo() <= lasttrow)) {
newSheet.addMergedRegion(region);
}
}
HSSFRow fromRow = null;
HSSFRow newRow = null;
HSSFCell newCell = null;
HSSFCell fromCell = null;
// Set column width
for (int i = firstrow; i < lasttrow; i++) {
fromRow = fromsheet.getRow(i);
if (fromRow != null) {
for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
int colnum = fromsheet.getColumnWidth((short) j);
if (colnum > ) {
newSheet.setColumnWidth((short) j, (short) colnum);
}
if (colnum == ) {
newSheet.setColumnHidden((short) j, true);
} else {
newSheet.setColumnHidden((short) j, false);
}
}
break;
}
} // Copy rows and fill in data
for (int i = ; i < lasttrow; i++) {
fromRow = fromsheet.getRow(i);
if (fromRow == null) {
continue;
}
newRow = newSheet.createRow(i - firstrow);
newRow.setHeight(fromRow.getHeight());
for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
fromCell = fromRow.getCell((short) j);
if (fromCell == null) {
continue;
}
newCell = newRow.createCell((short) j);
newCell.setCellStyle(fromCell.getCellStyle());
int cType = fromCell.getCellType();
newCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(fromCell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(fromCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
newCell.setCellValue(fromCell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(fromCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
newCell.setCellValue(fromCell.getErrorCellValue());
break;
default:
newCell.setCellValue(fromCell.getRichStringCellValue());
break;
}
}
}
} }

The above is the core code of the whole tool class

The test data are as follows

/**
* Many tests Sheet Page export data table method
*/
public static void ExcelTest(HttpServletResponse response){ // Building data sources
List<Map<String, String>> dataSourceList=new ArrayList<Map<String,String>>(){
{
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "BUSINESS_ID");
put(" Field meaning ", " Business id");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " yes ");
put(" Main code ", "");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "PROC_INST_ID");
put(" Field meaning ", " Process instance number ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", "");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "PROC_STATE");
put(" Field meaning ", " Process status ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "APPLICANT");
put(" Field meaning ", " Applicant ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "LEAVE_TYPE");
put(" Field meaning ", " Leave type ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "REASON");
put(" Field meaning ", " The reason for asking for leave is ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "BEGIN_TIME");
put(" Field meaning ", " Starting time ");
put(" data type ", "TIMESTAMP");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "END_TIME");
put(" Field meaning ", " End time ");
put(" data type ", "TIMESTAMP");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "INSERT_PERSON");
put(" Field meaning ", " The registrar ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
add(new HashMap<String, String>(){{
put(" Field number ", "");
put(" Field code ", "APPROVEDBY");
put(" Field meaning ", " Approved by ");
put(" data type ", "VARCHAR");
put(" length ", "");
put(" Primary key ", " ");
put(" Main code ", " ");
}});
}
};
// Build... In the data source key value
String[] keysStrings={" Field number "," Field code "," Field meaning "," data type "," length "," Primary key "," Main code "};
// The name of each page
String [] sheetNameStrings={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6"};
String modelURLString="D:\\model\\model.xls";
try {
ExcelUtil.ExcelByModel(" Test template export ", modelURLString, dataSourceList, response, sheetNameStrings, keysStrings, );
} catch (Exception e) {
e.printStackTrace();
} }

That's about Excel The report is exported and generated according to the template Sheet It's also my little tool , Need to refer to the code , According to the actual business needs Make code adjustments .

Export... Based on template Excel Report and copy template to generate multiple Sheet More related articles on page

  1. Export... Based on template Excel Report and generate multiple Sheet page

    Because there are more reports to export recently , All of them become a tool class , The scenario used by this tool class is   Export according to the template provided Excel report form And according to the template provided Sheet Page So as to realize multiple Sheet Page needs , When using this tool class ...

  2. Export by template Excel

    explain : development environment vs2012 asp.net mvc4 c# Be careful :Excel There are multiple templates sheet page , export Excel When , Give more than one at a time sheet Page fill data 1. Project structure 3.Excel Templates ( Be careful she ...

  3. NPOI Copy template export Excel

    I am a novice intern , The company has arranged for me to excel Export function . Requirements are as follows :1. export excel The file has style requirements :2. export excel Contains one or more worksheets :3. Function makes life ( My understanding is that excel Styles may change in the future ...

  4. ASP.NET Core 2.2 : sixteen . Grab a new one Endpoint Routing scheme try.dot.net Correct use of posture .Net NPOI according to excel Template export excel、 Directly generate excel .Net NPOI Upload excel file 、 Submit to the background to get excel The data in

    ASP.NET Core 2.2 : sixteen . Grab a new one Endpoint Routing scheme   ASP.NET Core from 2.2 Version start , A new name has been adopted Endpoint Routing scheme , There is no difference with the original scheme in use ...

  5. .Net NPOI according to excel Template export excel、 Directly generate excel

    One . according to Excel Template export excel 1. Import NPOI.dll  2.DAL Add a class ExportExcel.cs using NPOI.SS.UserModel; using System; usin ...

  6. apache poi Export... Based on template excel

    You need to create and edit a new one in advance excel file , Set the style . Edit the output data , according to excel The coordinates correspond one to one . Support list data output , The columns in the list are merged . The code is as follows : package com.icourt.util; im ...

  7. aspose.cells Export... Based on template excel

    I haven't blogged for more than ten days , I've been busy with projects recently , Personnel changes in the company are also relatively large , One more project on hand . What's new aspose.cells Export... Based on template excel Function of quotation , By the way, write down the relevant core , First on the template and export effect ...

  8. POI Export... Via template EXCEL file

    General EXCEL Export using POI So let's create one HSSFWorkbook, And then by constantly creating HSSFRow,HSSFCell After setting the cell content, you can complete the export . This time we need to use templates in the project , The exported contents include (1. In the template ...

  9. Java No template export Excel,Apache-POI Plug-in implementation

    development environment jdk 1.8 Maven 3.6 Tomcat 8.5 SpringBoot 2.1.4.RELEASE Apache-POI 3.6 Idea Be careful : I am based on the existing SpringBoo ...

Random recommendation

  1. JDIC visit Web when NullPointerException

    Exception in thread "EventThread" java.lang.NullPointerException            at org.jdeskto ...

  2. Start page setup ,icon Icon settings

    For more dimensions, how to place pixels, see :http://chicun.jammy.cc/ How to set up App Startup map of , That is to say Launch Image? Create a new one iosLaunchImage Folder

  3. javaIO( 3、 ... and )

  4. [Locked] Verify Preorder Sequence in Binary Search Tree

    Verify Preorder Sequence in Binary Search Tree Given an array of numbers, verify whether it is the c ...

  5. DNN Paper sharing - Item2vec: Neural Item Embedding for Collaborative Filtering

    Pre comments : This article is relatively simple , Innovation is not high , It's basically a reference google Of word2vec Method , Applied to recommended scenarios i2i In similarity computing , But the actual effect is still improved . The main method is to put item As word, User behavior sequence ...

  6. netcore Use jenkins + supervisor Realization standalone Automatic release of multiple copies in the next generation

    Last time we used jenkins Did a simple automated release , stay shell It is used in BUILD_ID=dontKillMe nohup dotnet xxx.dll &   This simple background bearer , If your ...

  7. nginx Log cutting (logrotate or shell Script )

    nginx I won't cut the log file myself , It can be done in two different ways , Namely : adopt logrotate And through the shell Script . If it is yum Installed by nginx, The system will automatically pass by default logrotate This log tube ...

  8. hdu 4651 Partition &amp;&amp; hdu 4658 Integer Partition—— Partition fraction and Pentagon theorem

    subject :http://acm.hdu.edu.cn/showproblem.php?pid=4651 Reference resources :https://blog.csdn.net/u013007900/article/detail ...

  9. Linux Mint The problem of italics

    A lot of people have met the ones that have just been installed Linux Mint The interface font is bold , And then somehow it turns into the problem of regular script . Let's not talk about the technical reasons , Just how to solve it . To cause this , Most of it is when I install input method or some other software , At the same time installed AR ...

  10. python json Module usage details

    python Other knowledge catalog #json. Data exchange uses json file .json It's a special string . Visit website , Back to you json 1.json brief introduction : Definition :JSON(JavaScript Object Notat ...