POI operation EXCEL object
HSSF: Operation Excel 97 (.xls) format
XSSF: Operate Excel 2007 OOXML (.xlsx) format, operate EXCEL memory usage is higher than HSSF
SXSSF: Supported from POI3.8 beta3, based on XSSF, low memory footprint.
Use the HSSF object of POI to generate Excel 97 (.xls) format, and the generated EXCEL is directly exported without compression.
Online problems: Load server forwards requests to the application server blocking, and memory overflow.
If the system has large data volume report export, consider using POI's SXSSF for EXCEL operation.
The Excel 97 (.xls) format generated by HSSF itself has a limit that each sheet cannot exceed 65,536 pieces.
XSSF generates Excel 2007 OOXML (.xlsx) format, and the number of characters has increased, but during the export process, the memory usage rate is higher than that of HSSF.
SXSSF is an operational EXCEL object based on the low memory footprint provided by XSSF since the 3.8-beta3 version. The principle is that you can set or manually write the EXCEL line in memory to the hard disk, so that only a small number of EXCEL lines are saved in the memory for operation.
EXCEL's compression rate is particularly high, reaching 80%, and only about 2M after 12M file compression. If it is not compressed, it will not only occupy user bandwidth, but will also cause the connection (binary stream forwarding) between the load server (apache) and the application server to occupy a long time (binary stream forwarding), resulting in the load server request blocking and unable to provide services.
Be sure to pay attention to the closing of file streams
Prevent the foreground (page) from continuously triggering the export of EXCEL
1. General Core Export Tool Class
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
public class ExcelUtil{
public static String NO_DEFINE = "no_define";//Undefined fields public static String DEFAULT_DATE_PATTERN="Yyyyy MM month dd day";//Default date format public static int DEFAULT_COLOUMN_WIDTH = 17;
/**
* Export Excel 97 (.xls) format, a small amount of data
* @param title title line
* @param headMap attribute - column name
* @param jsonArray dataset
* @param datePattern date format, null uses the default date format
* @param colWidth column width Default at least 17 bytes
* @param out Output Stream
*/
public static void exportExcel(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
// Declare a workbook HSSFWorkbook workbook = new HSSFWorkbook();
();
().setCompany("*****company");
SummaryInformation si = ();
("JACK"); //Fill in the xls file author information ("Export Program"); //Fill in the xls file to create program information ("Last Save Information"); //Fill in the last saver information of the xls file ("JACK is a programmer!"); //Fill in the xls file author information ("POI Export Excel"); //Fill in the xls file title information ("POI Export Excel");//Fill in the document topic information (new Date());
//Table header style HSSFCellStyle titleStyle = ();
(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = ();
((short) 20);
((short) 700);
(titleFont);
// Column header style HSSFCellStyle headerStyle = ();
(HSSFCellStyle.SOLID_FOREGROUND);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = ();
((short) 12);
(HSSFFont.BOLDWEIGHT_BOLD);
(headerFont);
// Cell style HSSFCellStyle cellStyle = ();
(HSSFCellStyle.SOLID_FOREGROUND);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.ALIGN_CENTER);
(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont cellFont = ();
(HSSFFont.BOLDWEIGHT_NORMAL);
(cellFont);
// Generate a (with title) form HSSFSheet sheet = ();
// Declare a top-level manager for drawing HSSFPatriarch patriarch = ();
// Define the size and position of the comment, see the documentation for details HSSFComment comment = (new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// Set comment content (new HSSFRichTextString("You can add comments in the POI!"));
// Set the comment author. When the mouse moves to the cell, you can see the content in the status bar. ("JACK");
//Set the column width int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//At least bytes int[] arrColWidth = new int[()];
// Generate table title rows and set column width String[] properties = new String[()];
String[] headers = new String[()];
int ii = 0;
for (Iterator<String> iter = ().iterator(); iter
.hasNext();) {
String fieldName = ();
properties[ii] = fieldName;
headers[ii] = fieldName;
int bytes = ().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
(ii,arrColWidth[ii]*256);
ii++;
}
// traverse the collection data and generate data rows int rowIndex = 0;
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ) sheet = ();//If the data exceeds it, it will be displayed on the second page
HSSFRow titleRow = (0);//Table header rowIndex=0 (0).setCellValue(title);
(0).setCellStyle(titleStyle);
(new CellRangeAddress(0, 0, 0, () - 1));
HSSFRow headerRow = (1); //Column header rowIndex =1 for(int i=0;i<;i++)
{
(i).setCellValue(headers[i]);
(i).setCellStyle(headerStyle);
}
rowIndex = 2;//The data content starts with rowIndex=2 }
JSONObject jo = (JSONObject) (obj);
HSSFRow dataRow = (rowIndex);
for (int i = 0; i < ; i++)
{
HSSFCell newCell = (i);
Object o = (properties[i]);
String cellValue = "";
if(o==null) cellValue = "";
else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else cellValue = ();
(cellValue);
(cellStyle);
}
rowIndex++;
}
// Automatic width adjustment /*for (int i = 0; i < ; i++) {
(i);
}*/
try {
(out);
();
} catch (IOException e) {
();
}
}
/**
* Export Excel 2007 OOXML (.xlsx) format
* @param title title line
* @param headMap property - column header
* @param jsonArray dataset
* @param datePattern Date format, if the null value is passed, the default is year, month and date
* @param colWidth column width Default at least 17 bytes
* @param out Output Stream
*/
public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
// Declare a workbook SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//cache (true);
//Table header style CellStyle titleStyle = ();
(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = ();
((short) 20);
((short) 700);
(titleFont);
// Column header style CellStyle headerStyle = ();
(HSSFCellStyle.SOLID_FOREGROUND);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.ALIGN_CENTER);
Font headerFont = ();
((short) 12);
(HSSFFont.BOLDWEIGHT_BOLD);
(headerFont);
// Cell style CellStyle cellStyle = ();
(HSSFCellStyle.SOLID_FOREGROUND);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.BORDER_THIN);
(HSSFCellStyle.ALIGN_CENTER);
(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = ();
(HSSFFont.BOLDWEIGHT_NORMAL);
(cellFont);
// Generate a (with title) form SXSSFSheet sheet = ();
//Set the column width int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//At least bytes int[] arrColWidth = new int[()];
// Generate table title rows and set column width String[] properties = new String[()];
String[] headers = new String[()];
int ii = 0;
for (Iterator<String> iter = ().iterator(); iter
.hasNext();) {
String fieldName = ();
properties[ii] = fieldName;
headers[ii] = (fieldName);
int bytes = ().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
(ii,arrColWidth[ii]*256);
ii++;
}
// traverse the collection data and generate data rows int rowIndex = 0;
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ) sheet = ();//If the data exceeds it, it will be displayed on the second page
SXSSFRow titleRow = (0);//Table header rowIndex=0 (0).setCellValue(title);
(0).setCellStyle(titleStyle);
(new CellRangeAddress(0, 0, 0, () - 1));
SXSSFRow headerRow = (1); //Column header rowIndex =1 for(int i=0;i<;i++)
{
(i).setCellValue(headers[i]);
(i).setCellStyle(headerStyle);
}
rowIndex = 2;//The data content starts with rowIndex=2 }
JSONObject jo = (JSONObject) (obj);
SXSSFRow dataRow = (rowIndex);
for (int i = 0; i < ; i++)
{
SXSSFCell newCell = (i);
Object o = (properties[i]);
String cellValue = "";
if(o==null) cellValue = "";
else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
else if(o instanceof Float || o instanceof Double)
cellValue= new BigDecimal(()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
else cellValue = ();
(cellValue);
(cellStyle);
}
rowIndex++;
}
// Automatic width adjustment /*for (int i = 0; i < ; i++) {
(i);
}*/
try {
(out);
();
();
} catch (IOException e) {
();
}
}
//Web export excel public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
(title,headMap,ja,null,0,os);
byte[] content = ();
InputStream is = new ByteArrayInputStream(content);
// Set the response parameters to open the download page ();
("application/;charset=utf-8");
("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1"));
();
ServletOutputStream outputStream = ();
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(outputStream);
byte[] buff = new byte[8192];
int bytesRead;
while (-1 != (bytesRead = (buff, 0, ))) {
(buff, 0, bytesRead);
}
();
();
();
();
}catch (Exception e) {
();
}
}
public static void main(String[] args) throws IOException {
int count = 100000;
JSONArray ja = new JSONArray();
for(int i=0;i<100000;i++){
Student s = new Student();
("POI"+i);
(i);
(new Date());
(i);
(i);
(i/2==0?false:true);
(s);
}
Map<String,String> headMap = new LinkedHashMap<String,String>();
("name","Name");
("age","age");
("birthday","Birthday");
("height","height");
("weight","weight");
("sex","gender");
String title = "test";
/*
OutputStream outXls = new FileOutputStream("E://");
("Exporting xls......");
Date d = new Date();
(title,headMap,ja,null,outXls);
("Total"+count+" pieces of data, execute "+(new Date().getTime()-())+"ms");
();*/
//
OutputStream outXlsx = new FileOutputStream("E://");
("Exporting xlsx......");
Date d2 = new Date();
(title,headMap,ja,null,0,outXlsx);
("common"+count+"Story of data, execute"+(new Date().getTime()-())+"ms");
();
}
}
class Student {
private String name;
private int age;
private Date birthday;
private float height;
private double weight;
private boolean sex;
public String getName() {
return name;
}
public void setName(String name) {
= name;
}
public Integer getAge() {
return age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
= birthday;
}
public float getHeight() {
return height;
}
public void setHeight(float height) {
= height;
}
public double getWeight() {
return weight;
}
public void setWeight(double weight) {
= weight;
}
public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
= sex;
}
public void setAge(Integer age) {
= age;
}
}
2. How to write controller controller
//Export Accessories List @RequestMapping(value = "partExport")
@ResponseBody
public void partExportHttpServletResponse response){
JSONArray ja = ();//Get business data set Map<String,String> headMap = ();//Get attributes-column header String title = "Accessories Statistics";
(title,headMap,ja,response);
}
3. How to write front-end pages (do not request asynchronously, such as $.post)
// You can click a button event to trigger the following code to export("partExport","_blank");
// Or you can submit a form$('#form').attr('action','partExport');
$('#form').attr('target','_blank');
$('#form').submit();
Depend on jar package (maven pom)
<dependency> <groupId></groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
5. Local testing
Export 10w pieces of data to the local hard disk. The HSSF method takes about 14s and the SXSSF method takes about 24s. Despite this, it is recommended to use SXSSF to export .xlsx excel.
The reason why JSONArray is used as the data set and does not use java collection class is because JSONObject uses get(key) method when obtaining data, which corresponds to the attribute column. This is highly flexible and the attribute column does not have to match the fields of the java object. If you use java classes, you need to apply reflection and piece together the get method, which is more complicated.
The above is a detailed explanation and integration of the POI universal export of Excel (.xls, .xlsx) method introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support for my website!