package com.XXX;


import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.eos.system.annotation.Bizlet;
import commonj.sdo.DataObject;

public class ExcelExportUtil {

    /**
     * 
     * Excel export
     * @author RangoLan
     * @param criteriaEntity DataObject Get the query object that populates the data source
     * @param fileName Generated filename prefix
     * @param response HttpServletResponse response
     * @param colToProperty LinkedHashMap for Table Column Name and DataObject Object Property
     * @param datePattern Date format data format
     * @param HttpServletRequest request Request object for internal jump without data output
     * @param backPage Return to the screen when there is no data after executing the query
     */

    @Bizlet("Data writing Excel Generate Downloads")
    public static void exportExcel(String fileName,HttpServletRequest request,HttpServletResponse response,DataObject criteriaEntity,LinkedHashMap<String,String> colToProperty,String datePattern,String backPage) throws Exception {

        //Fill in the data source according to the criteria of the query object
        DataObject[] datas = (DataObject[])com.eos.foundation.database.DatabaseUtil.queryEntitiesByCriteriaEntity("default", criteriaEntity);
        if (datas != null && datas.length > 0) {

            if(datePattern==null){
                datePattern = "yyyy-MM-dd";
            }

            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);

            /** Format* */
            workbook.setCompressTempFiles(true);

            // Header style
            CellStyle headerStyle = workbook.createCellStyle();

            //horizontally
            headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
            //Vertical centering
            headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            //Set borders
            headerStyle.setBorderTop(CellStyle.BORDER_THIN);
            headerStyle.setBorderRight(CellStyle.BORDER_THIN);
            headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(CellStyle.BORDER_THIN);

            //Set color
            headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
            headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);


            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // cell style
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            //Vertical centering
            cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            //Set borders
            cellStyle.setBorderTop(CellStyle.BORDER_THIN);
            cellStyle.setBorderRight(CellStyle.BORDER_THIN);
            cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);

            //Setting up automatic line break
            cellStyle.setWrapText(true);



            //Set font
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);

            //DataObject Property array
            String[] properties = new String[colToProperty.size()];
            //Header array
            String[] headers = new String[colToProperty.size()];

            int ii = 0;
            for (Iterator<String> iter = colToProperty.keySet().iterator();iter.hasNext();) {
                String fieldName = iter.next();

                headers[ii] = fieldName;

                properties[ii] = colToProperty.get(fieldName);

                ii++;
            }

            // Traversing the collection data, generating data rows, and filling Excel
            int rowIndex = 0;
            Sheet sheet = null;
            for (DataObject data : datas) {

                if (rowIndex == 65535 || rowIndex == 0) {

                    sheet = workbook.createSheet();// If the data exceeds, it will be displayed on the second page.




                    Row headerRow = sheet.createRow(0);// Table header rowIndex=0

                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        //Setting Header Style
                        headerRow.getCell(i).setCellStyle(headerStyle);

                    }



                    rowIndex = 1;// Data content starts with rowIndex=1
                }

                //Create row
                Row dataRow = sheet.createRow(rowIndex);

                for (int i = 0; i < properties.length; i++) {
                    //Creating Cells
                    Cell newCell = dataRow.createCell(i);

                    Object o = data.get(properties[i]);
                    String cellValue = "";
                    if (o == null) {//Empty processing
                        cellValue = "";
                    } else if (o instanceof Date) {//Date formatting
                        cellValue = new SimpleDateFormat(datePattern).format(o);
                    } else {
                        cellValue = o.toString();
                    }
                    //Cell assignment
                    newCell.setCellValue(cellValue);
                    //Cell formatting
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;

            }

            OutputStream out = null;

            //Setting Auto Column Width

            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
            }

            try {
                out = response.getOutputStream();
                fileName = fileName
                        + "_"
                        + new SimpleDateFormat("yyyyMMDDhh24mmssSSS")
                                .format(System.currentTimeMillis()) + ".xlsx";
                response.setContentType("application/x-msdownload");
                response.setHeader("Content-Disposition",
                        "attachment; filename="
                                + URLEncoder.encode(fileName, "UTF-8"));
                // Save report files
                workbook.write(out);
            } catch (IOException e) {

                e.printStackTrace();
            } finally {
                try {
                    if (out != null)
                        out.close();
                    response.flushBuffer();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }


        } else {

            // No Exported Data

            request.setAttribute("noDataToExport", "No data to export!");
            request.getRequestDispatcher(backPage).forward(request, response);


        }



    }

Emphasis is placed on setting the adaptive column width: sheet.autoSizeColumn(i); this setting alone is not very effective. So the following two steps have been taken:

** 1. sheet.autoSizeColumn(i); // Set the automatic column width first
2. sheet. setColumn Width (i, sheet. getColumn Width (i) 17/10); / / / Set the column width to 1.7 times the automatic column width (of course, not strict 1.7 times, division of int is not discussed), this 1.6 or so can also be, which is my test experience value.*

After my test, I can set the column width of excel well and the content of more cells.
 

 

poi generates excel collation (set border / font / color / bold / center /)

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

Setting background color:

SetBorder.setFill ForegroundColor ((short)13);//Set background color
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

2. Setting the border:

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //lower border
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//left border
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); //Upper Border
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//Right Border

Setting in the middle:

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); //Centralization

Setting fonts:

HSSFFont font = wb.createFont();
font.setFontName("blackbody");
font.setFontHeightInPoints((short) 16); // Set font size

HSSFFont font2 = wb.createFont();
font2.setFontName("imitation Song_GB2312");
Font 2. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD); // bold display
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//Select the font format you want to use

Setting column width:

sheet.setColumnWidth(0, 3766); // The first parameter represents the column ID (starting from 0), and the second parameter represents the width value.

Setting up automatic line-changing:

setBorder.setWrapText(true);//Set automatic line break

Setting row height by row object

HSSFRow row = sheet.createRow(0);
// The value set by heightInPoints is always 20 times the value set by the heightInPoints attribute.
row.setHeightInPoints(20);
HSSFRow row1 = sheet.createRow(5);

row1.setHeight((short) (25 * 20));

8. Setting default width and height

HSSFSheet sheet2 =  wb.createSheet("sheet2");             
sheet2.setDefaultColumnWidth(20);      
sheet2.setDefaultRowHeightInPoints(20);

9. Set cell width (set cell width by sheet object, setColumnWidth)

HSSFSheet sheet = wb.createSheet("sheet1");         
sheet.setColumnWidth(0, 20 * 256);

Merge cells:
Region region1 = new Region(0, (short) 0, 0, (short) 6); //parameter 1: row number parameter 2: starting column number parameter 3: row number parameter 4: terminating column number
// This method has been abandoned in POI 3.8. It is recommended to use the following one.
CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);   
// Parametric 1: Starting row parameter 2: Terminating row parameter 3: Starting column parameter 4: Terminating column
However, it should be noted that the parameters of the two constructors are not the same, and which one to use depends on the different versions of POI.  
sheet.addMergedRegion(region1); 

So much has been used at present, and new ones will continue to be added in the future.

XI. BOUNDARY BOUNDARY

  HSSFCellStyle cellStyle= wookBook.createCellStyle();
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

Twelve. Font color

  Font font = wb.createFont();
font.setFontHeightInPoints((short) 12); font height
font.setFontName("Song Style"); font/font
font.setColor(HSSFColor.RED.index);//color
cellStyle.setFont(font); //Select the font format you need to use