A complex POI processing merged cells and their style alignment issues

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.UnsupportedEncodingException;

import java.text.DateFormat;

import java.text.DecimalFormat;

import java.text.ParseException;

import java. text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Date;

import java.util.List;

import java.util.Map;

import javax.inject.Inject;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFPrintSetup;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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.DataFormat;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.PrintSetup;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.ss.util.CellUtil;

import org.springframework.beans.factory.config.ConfigurableBeanFactory;

import org.springframework.context.annotation.Scope;

import org.springframework.stereotype.Controller;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.RequestMapping;

import com.tshn.hydrology.base.BaseUtil;

import com.tshn.hydrology.base.Consts;

import com.tshn.hydrology.entity.system.Organization;

import com.tshn.hydrology.service.modules.StPptnRService;

@Controller

@RequestMapping ("stPptnR")

@Scope (ConfigurableBeanFactory.SCOPE_PROTOTYPE)

public class StPptnRDetailsExcelExport {

     @Inject

     private StPptnRService stPptnRservice;

     private final int ZONE_NAME_CELL_STYLE = 1;

     private final int COMMON_CEELL_STYLE = 2;

     private final int AVG_RAIN_CELL_STYLE = 3;

     private final int NUMBERIC_CELL_STYLE = 4;

     private String decimalFormatPattern = "0.0"; //

     private String rainDetailExcelName = "Barometer year. Issue xx.xls"; // Excel name

     private DateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd HH");

     private DateFormat chineseDateFormat = new SimpleDateFormat ("Yyyy year MM month dd day HH hour");

     private DateFormat chineseDateFormatNoYear = new SimpleDateFormat ("MM month dd day HH hour");

     private Calendar cal = Calendar.getInstance ();

     private int [] queryDatePosition = {3,5}; // The position of the excel template to place the query time period (3 rows and 5 columns)

     private String templatePath = BaseUtil.getDefultAttPrefixPath () + Consts.STANDARD_REPORT_TEMPLATE; // Excel template location

     private int rowZoneNum = 3; // Number of counties stored in each row

     private String [] [] allAreaRainStatistics =

             new String [] [] {{"Average", "parentAvg", "AVERAGE"}, {"Max", "parentMax", "MAX"}, {"Min", "parentMin", "MIN"}}; // Regional statistics

     

     @RequestMapping ("exportRegularRainDetailsExcel")

      public void exportRegularRainDetailsExcel (HttpServletRequest request, HttpServletResponse response, Model model) {

        String startTime = request.getParameter ("startTime"); // Start query time

        String endTime = request.getParameter ("endTime"); // End query time

        try {

            Date startDate = dateFormat.parse (startTime);

            Date endDate = dateFormat.parse (endTime);

            rainDetailExcelName = rainDetailExcelName.replaceAll ("year", getQueryYear (startDate)); // Reset the excel sheet name according to the query date

            String queryDateSection = chineseDateFormat.format (startDate) + "~" + chineseDateFormatNoYear.format (endDate); // Set query time period

            stPptnRservice.getStPptnRDetails (model, request);

            Map <String, Object> data = model.asMap (); // rainfall information

            Workbook excelBook = getWrittenExcelbook (queryDateSection, data); // Write excel

            

            response.setContentType ("application / x-msdownload"); // notify the client of the MIME type of the file:

             try {

                response.setHeader ("Content-disposition", "attachment; filename =" + new String (rainDetailExcelName.getBytes (), "ISO-8859-1"));

                excelBook.write (response.getOutputStream ());

            } catch (UnsupportedEncodingException e) {

                e.printStackTrace ();

            } catch (IOException e) {

                e.printStackTrace ();

            }

        } catch (ParseException e) {

            e.printStackTrace ();

        }

      }

     

     / **

      * Calculate the year of the starting query date

      * @param startDate

      * @return

      * /

     private String getQueryYear (Date startDate) {

        cal.setTime (startDate);

        return String.valueOf (cal.get (Calendar.YEAR));

     }

     

     

 

     

     

     @SuppressWarnings ("unchecked")

    private Workbook getWrittenExcelbook (String queryDateSection, Map <String, Object> data) {

         int startWirteRowNum = 5; // Start line number

         StringBuffer calculateArea = new StringBuffer ();

         Workbook workbook = null;

        try {

            workbook = new HSSFWorkbook (new FileInputStream (templatePath));

        } catch (FileNotFoundException e) {

            e.printStackTrace ();

        } catch (IOException e) {

            e.printStackTrace ();

}

         Sheet sheet = workbook.getSheetAt (0);

         sheet.setColumnWidth (1, 2180);

         sheet.setColumnWidth (4, 2180);

         sheet.setColumnWidth (7, 2180);

         // Write the query time at the position specified by queryDatePosition

         sheet.getRow (queryDatePosition [0]). getCell (queryDatePosition [1]). setCellValue (queryDateSection);

         

         List <Map <String, Object >> stpps = (List <Map <String, Object >>) data.get ("stpps");

         

         int [] currentRowNums = new int [rowZoneNum];

         for (int m = 0; m <currentRowNums.length; m ++) {

             currentRowNums [m] = startWirteRowNum;

         }

         for (int i = 0; i <stpps.size (); i ++) {

               Map <String, Object> sttp = stpps.get (i);

               String mainOrgName = String.valueOf (sttp.get ("orgName"));

               

               List <Organization> childOrgs = (List <Organization>) sttp.get ("child");

               int rowNum = childOrgs.size () + 1; // rainfall in each township + average rainfall in counties

               int columnZoneIndex = i% rowZoneNum;

               int startRowNum = currentRowNums [columnZoneIndex];

               int endRowNum = currentRowNums [columnZoneIndex] + rowNum-1;

               int startAndEndColumnNum = i% rowZoneNum * rowZoneNum;

            

               // Merge county cell names

               CellRangeAddress cellRangeAddress = new CellRangeAddress (startRowNum, endRowNum, startAndEndColumnNum, startAndEndColumnNum);

               sheet.addMergedRegion (cellRangeAddress);

           

               

               Row currentRow = null;

               Cell startCalCell = null;

               Cell endCalCell = null;

               for (int j = 0; j <rowNum; j ++) {

           

                   if (sheet.getRow (currentRowNums [columnZoneIndex]) == null) {

                       currentRow = sheet.createRow (startWirteRowNum ++);

                       currentRowNums [columnZoneIndex] = startWirteRowNum;

                   } else {

                       currentRow = sheet.getRow (currentRowNums [i% rowZoneNum]);

                       currentRowNums [columnZoneIndex] = currentRowNums [columnZoneIndex] +1;

                   }

                   

                   Cell orgNameCell = currentRow.createCell (startAndEndColumnNum + 1); // Storage station name cell

                  

                   Cell rainCell = currentRow.createCell (startAndEndColumnNum + 2); // Save rain rainfall

                   rainCell.setCellType (Cell.CELL_TYPE_NUMERIC);

                rainCell.setCellStyle (this.getCustomedCellStyle (workbook, NUMBERIC_CELL_STYLE));

                  if (rowNum-1> j) {

                       Organization org = childOrgs.get (j);

                       orgNameCell.setCellStyle (this.getCustomedCellStyle (workbook, COMMON_CEELL_STYLE));

                       orgNameCell.setCellValue (org.getOrganizationName ()); // Set the station name

                       if (j == 0) {

                          if (! mainOrgName.equals ("Development Zone")) {

                              orgNameCell.setCellValue ("City");

                          }

                           startCalCell = rainCell;

                       } else if (j == rowNum-2) {

                           endCalCell = rainCell;

                       }

                       rainCell.setCellValue (org.getAvgOfStpptnR ()); // Set rainfall

                  } else {// Set the average rainfall of the county

                      orgNameCell.setCellStyle (this.getCustomedCellStyle (workbook, AVG_RAIN_CELL_STYLE));

                      orgNameCell.setCellValue (mainOrgName.substring (mainOrgName.length ()-1) + "average");

                      rainCell.setCellValue (Double.parseDouble (String.valueOf (sttp.get ("avg"))));

                      String excelArea = this.tranlateExcelArea (startCalCell, endCalCell); // Get excel area

                      calculateArea.append (excelArea) .append (",");

                      this.setFormula (rainCell, "AVERAGE", excelArea); // Set the formula for the average rainfall cell

                  }

                 

               }

               

            for (int m = cellRangeAddress.getFirstRow (); m <= cellRangeAddress.getLastRow (); m ++) {// Set header cell style

                CellUtil.getCell (sheet.getRow (m), startAndEndColumnNum) .setCellStyle (this.getCustomedCellStyle (workbook, ZONE_NAME_CELL_STYLE));

            }

             Cell areaCell = CellUtil.getCell (sheet.getRow (cellRangeAddress.getFirstRow ()), startAndEndColumnNum);

             

             areaCell.setCellValue (getStringWithEnter (mainOrgName));

             

               

             

         }

         

         int minArrayValueIndex = 2;

         int currentRowNum = currentRowNums [minArrayValueIndex];

         int lastRowColumnIndex = minArrayValueIndex * 3;

         // Merge vertical row cells

         CellRangeAddress cellRangeAddress = new CellRangeAddress (currentRowNum, currentRowNum + 2, lastRowColumnIndex, lastRowColumnIndex);

         sheet.addMergedRegion (cellRangeAddress);

         

         calculateArea.deleteCharAt (calculateArea.length ()-1); // Delete the last comma

         for (int k = 0; k <3; k ++) {

             if (sheet.getRow (k + currentRowNum) == null) {

                 sheet.createRow (k + currentRowNum);

             }

             RowtempRow = sheet.getRow (k + currentRowNum);

             Cell dataNameCell = tempRow.createCell (lastRowColumnIndex + 1); // Data name

             dataNameCell.setCellStyle (this.getCustomedCellStyle (workbook, ZONE_NAME_CELL_STYLE));

             dataNameCell.setCellValue (allAreaRainStatistics [k] [0]);

             Cell dataContentCell = tempRow.createCell (lastRowColumnIndex + 2); // Data content

             dataContentCell.setCellType (Cell.CELL_TYPE_NUMERIC); // Set cell type

             dataContentCell.setCellStyle (this.getCustomedCellStyle (workbook, NUMBERIC_CELL_STYLE));

             dataContentCell.setCellValue (Double.parseDouble (String.valueOf (data.get (allAreaRainStatistics [k] [1]))));

             this.setFormula (dataContentCell, allAreaRainStatistics [k] [2], calculateArea.toString ());

         }

         currentRowNums [minArrayValueIndex] = currentRowNums [minArrayValueIndex] + 3;

         for (int m = cellRangeAddress.getFirstRow (); m <= cellRangeAddress.getLastRow (); m ++) {// Set header cell style

             CellUtil.getCell (sheet.getRow (m), lastRowColumnIndex) .setCellStyle (this.getCustomedCellStyle (workbook, ZONE_NAME_CELL_STYLE));

         }

         Cell areaCell = CellUtil.getCell (sheet.getRow (cellRangeAddress.getFirstRow ()), lastRowColumnIndex);

         

         areaCell.setCellValue ("City \ r \ nStatistics");

         

         // Complete cells dynamically

         int maxArrayValueIndex = getMaxArrayValueIndex (currentRowNums);

         int maxArrayValue = currentRowNums [maxArrayValueIndex];

         for (int i = 0; i <currentRowNums.length; i ++) {

             if (i! = maxArrayValueIndex && maxArrayValue> currentRowNums [i]) {// If the number of rows in this column is less than the number of cells with the most rows, then the cells in this column will be filled in dynamically

                  int startMergedRegionRow = currentRowNums [i] -1; // Start line number

                  int endMergedRegionRow = maxArrayValue-1; // End line number

                  for (int m = 0; m <3; m ++) {

                      CellRangeAddress cellRange = new CellRangeAddress (startMergedRegionRow, endMergedRegionRow, i * rowZoneNum + m, i * rowZoneNum + m);

                      sheet.addMergedRegion (cellRange);

                      for (int x = startMergedRegionRow; x <= endMergedRegionRow; x ++) {

                          CellUtil.getCell (sheet.getRow (x), i * rowZoneNum + m) .setCellStyle (

                                  getCustomedCellStyle (workbook, i * rowZoneNum + m == 0? ZONE_NAME_CELL_STYLE: (i * rowZoneNum + m == 1? AVG_RAIN_CELL_STYLE: NUMBERIC_CELL_STYLE))

                            );

                      }

                  }

                  

                  

             }

         }

         

         /*--Printer settings--*/

        PrintSetup printSetUp = sheet.getPrintSetup ();

        sheet.setAutobreaks (true);

        printSetUp.setFitHeight ((short) 1); // Print one page

        printSetUp.setPaperSize (PrintSetup.A4_PAPERSIZE); // Set

        sheet.setHorizontallyCenter (true); // Set the printed page to be horizontally centered

        sheet.setVerticallyCenter (true); // Set the printed page to be vertically centered

         

         return workbook;

         

     }

     

     

     / **

      * According to the start cell and end cell, get the Excel range between the two cells

      * @param startCalCell

      * @param endCalCell

      * @return

      * /

     private String tranlateExcelArea (Cell startCalCell, Cell endCalCell) {

         int startCellColumnIndex = startCalCell.getColumnIndex (); // Get cell column index

         int startCellRowIndex = startCalCell.getRowIndex (); // Get cell row index

         

         int endCellColumnIndex = endCalCell.getColumnIndex (); // Get cell column index

         int endCellRowIndex = endCalCell.getRowIndex (); // Get cell row index

         

         String startCellStr = BaseUtil.transformArabNoToEnglishNo (startCellColumnIndex, true)

                                 + (startCellRowIndex + 1);

         String endCellStr = BaseUtil.transformArabNoToEnglishNo (endCellColumnIndex, true)

                 + (endCellRowIndex + 1);

         return startCellStr + ":" + endCellStr;

     }

     

     private void setFormula (Cell cell, String formulaName, String calculateArea) {

         cell.setCellFormula (formulaName + "(" + calculateArea + ")");

     }

     

     

     / **

      * Append carriage return character after each character

      * @param inputStr

      * @return

      * /

     private String getStringWithEnter (String inputStr) {

          String [] mainOrgNames = inputStr.split ("");

          String finalMainOrgName = ""; // Achieve the vertical effect of the county

          for (int x = 0; x <mainOrgNames.length; x ++) {

              finalMainOrgName + = mainOrgNames [x] + "\ r \ n";

          }

          return finalMainOrgName;

     }

     

     private CellStyle getCustomedCellStyle (Workbook workbook, int cellStyleType) {

         CellStyle newCellStyle = workbook.createCellStyle ();

         Font font = workbook.createFont ();

         font.setFontName ("Imitation Song_GB2312");

         

         if (cellStyleType == ZONE_NAME_CELL_STYLE) {

             font.setFontName ("Bold");

             font.setFontHeightInPoints ((short) 12);

             font.setBoldweight (Font.BOLDWEIGHT_BOLD);

             newCellStyle.setFont (font);

             newCellStyle.setWrapText (true);

             //newCellStyle.setRotation((short)-90); // The font is rotated 90 degrees

         } else if (cellStyleType == AVG_RAIN_CELL_STYLE) {

font.setFontHeightInPoints ((short) 12);

             font.setBoldweight (Font.BOLDWEIGHT_BOLD);

             font.setColor (HSSFColor.ROYAL_BLUE.index);

             newCellStyle.setFont (font);

             newCellStyle.setFillBackgroundColor (HSSFColor.GREY_80_PERCENT.index);

         } else if (cellStyleType == NUMBERIC_CELL_STYLE) {

            DataFormat format = workbook.createDataFormat ();

            newCellStyle.setDataFormat (format.getFormat (decimalFormatPattern));

         } else {

             font.setFontHeightInPoints ((short) 12);

             font.setBoldweight (Font.BOLDWEIGHT_NORMAL);

             newCellStyle.setFont (font);

         }

         

         newCellStyle.setVerticalAlignment (CellStyle.VERTICAL_CENTER); // Vertical center

         newCellStyle.setAlignment (CellStyle.ALIGN_CENTER); // center horizontally

         this.setBorder (newCellStyle, CellStyle.BORDER_THIN); // Set the cell border

         

         return newCellStyle;

     }

     

     / **

      * Get the array index corresponding to the smallest element in the array

      * @param array

      * @return

      * /

     private int getMaxArrayValueIndex (int [] array) {

         int maxValue = array [0];

         int maxValueIndex = 0;

         for (int i = 0; i <array.length; i ++) {

             if (maxValue <array [i]) {

                 maxValue = array [i];

                 maxValueIndex = i;

             }

         }

         

         return maxValueIndex;

         

     }

     

     / **

         * Set the cell border width

         * @param cellStyle

         * @param borderWidth

         * /

        private void setBorder (CellStyle cellStyle, short borderWidth) {

            cellStyle.setBorderTop (borderWidth);

            cellStyle.setBorderBottom (borderWidth);

            cellStyle.setBorderLeft (borderWidth);

            cellStyle.setBorderRight (borderWidth);

        }

        

         

 }