I am using apache poi
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
.......
.......
And then I do the rest of my code. My data is looking good. But my rows are too long. So the client want us to split the sheet into pages as shown in the template below.
How do I achieve this in a HSSFGWorkbook?
I tried several things like:
sheet.setAutobreaks(true);
sheet.setFitToPage(true);
etc.
Nothing worked.
I want the user to see the XLS sheet on screen in the format shown in the template below.
As you can see the pages are splitted from left to right.
Here is everything I am trying to in detail. Currently with the code I have written, I create the XLS all dynamically. I do not use any templates etc. So with the current code I have (shown below) I create the following.
As you can see, my data rows are all in one page in the sheet. What I need to do is to create the date as shown in the first image I published where you can see pages are split from left to right.
Here is the full code I am using....
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
public class XlsxMaker
{
public static void Main (String [] args)
{
List<String> columnModelList = ArrayList<String>;
List<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>;
String outXlsxFileName = "outXlsFile.xlsx");
//Add the test column names
columnModelList.add("Col1"); columnModelList.add("Col2");
columnModelList.add("Col3"); columnModelList.add("Col4");
columnModelList.add("Col5"); columnModelList.add("Col6");
columnModelList.add("Col7"); columnModelList.add("Col8");
columnModelList.add("Col9"); columnModelList.add("Col10");
columnModelList.add("Col11"); columnModelList.add("Col12");
columnModelList.add("Col13"); columnModelList.add("Col14");
columnModelList.add("Col15"); columnModelList.add("Col16");
columnModelList.add("Col17"); columnModelList.add("Col18");
columnModelList.add("Col19"); columnModelList.add("Col20");
columnModelList.add("Col21"); columnModelList.add("Col22");
//Add the test data rows
ArrayList<String> realData = new ArrayList<String>();
realData.add("Data-1-01"); realData.add("Data-1-02");
realData.add("Data-1-03"); realData.add("Data-1-04");
realData.add("Data-1-05"); realData.add("Data-1-06");
realData.add("Data-1-07"); realData.add("Data-1-08");
realData.add("Data-1-09"); realData.add("Data-1-10");
realData.add("Data-1-11"); realData.add("Data-1-12");
realData.add("Data-1-13"); realData.add("Data-1-14");
realData.add("Data-1-15"); realData.add("Data-1-16");
realData.add("Data-1-17"); realData.add("Data-1-18");
realData.add("Data-1-19"); realData.add("Data-1-20");
realData.add("Data-1-21"); realData.add("Data-1-22");
dataList.add(realData);
realData = new ArrayList<String>();
realData.add("Data-2-01"); realData.add("Data-2-02");
realData.add("Data-2-03"); realData.add("Data-2-04");
realData.add("Data-2-05"); realData.add("Data-2-06");
realData.add("Data-2-07"); realData.add("Data-2-08");
realData.add("Data-2-09"); realData.add("Data-2-10");
realData.add("Data-2-11"); realData.add("Data-2-12");
realData.add("Data-2-13"); realData.add("Data-2-14");
realData.add("Data-2-15"); realData.add("Data-2-16");
realData.add("Data-2-17"); realData.add("Data-2-18");
realData.add("Data-2-19"); realData.add("Data-2-20");
realData.add("Data-2-21"); realData.add("Data-2-22");
dataList.add(realData);
//Now make the XLS
ByteArrayOutputStream baou = combineXlsComponents(columnModelList, dataList);
try(OutputStream outputStream = new FileOutputStream(outXlsxFileName)) {
baou.writeTo(outputStream);
}
}
private static ByteArrayOutputStream combineXlsComponents(List<String> columnsList, List<ArrayList<String>> dataList)
{
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try(HSSFWorkbook workbook = populateXlsData(dataList, prepareColomnHeaders(columnsList))){
workbook.write(baos);
}catch(IOException e){
System.out.println("combineXlsComponents: "+e.getMessage());
}
return baos;
}
private static HSSFWorkbook prepareColomnHeaders(List<String> columnsList) throws IOException
{
HSSFWorkbook workbook = new HSSFWorkbook ();
Sheet sheet = workbook.createSheet();
/*This block try to put all in one page while printing */
sheet.setAutobreaks(true);
sheet.setFitToPage(true);
sheet.getPrintSetup().setFitHeight((short)0);
sheet.getPrintSetup().setFitWidth((short)1);
sheet.setColumnBreak(6);
/*Freeze Scrolling first 2 rows */
sheet.createFreezePane(0,2);
/*Mergers the row 0 up to column 21 */
sheet.addMergedRegion(new CellRangeAddress(0,0,0,21));
Row row0 = sheet.createRow(0);
row0.setHeightInPoints(23);
Cell cell1 = row0.createCell(0);
cell1.setCellValue("For Official Use Only.");
/* Style top privacy act row(0) */
cell1.setCellStyle(styleFouoRow(workbook));
/* -----------------Styling All other headers ------------------------*/
Font boldFont = workbook.createFont();
boldFont.setBold(true);
CellStyle styleForHeaders = workbook.createCellStyle();
styleForHeaders.setFont(boldFont);
styleForHeaders.setWrapText(true);
styleForHeaders.setVerticalAlignment(VerticalAlignment.CENTER);
/*Set the colors for header cells */
styleForHeaders.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
styleForHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);
styleForHeaders.setFillBackgroundColor(IndexedColors.DARK_BLUE.getIndex());
/*Set the borders for header cells */
styleForHeaders.setBorderLeft(BorderStyle.THIN);
styleForHeaders.setLeftBorderColor(IndexedColors.BLACK.getIndex());
styleForHeaders.setBorderRight(BorderStyle.THIN);
styleForHeaders.setRightBorderColor(IndexedColors.BLACK.getIndex());
styleForHeaders.setBorderTop(BorderStyle.THIN);
styleForHeaders.setTopBorderColor(IndexedColors.BLACK.getIndex());
styleForHeaders.setBorderBottom(BorderStyle.THIN);
styleForHeaders.setBottomBorderColor(IndexedColors.BLACK.getIndex());
HSSFFont fontHeaders = workbook.createFont();
fontHeaders.setColor(IndexedColors.WHITE.getIndex());
fontHeaders.setFontHeightInPoints((short) 10);
fontHeaders.setBold(true);
styleForHeaders.setFont(fontHeaders);
Row row = sheet.createRow(1);
row.setHeightInPoints(25);
int nextCell = -1;
for (String strObj: columnsList)
{
Cell cell = row.createCell(++nextCell);
cell.setCellValue(strObj);
cell.setCellStyle(styleForHeaders);
}
/*Set column widths */
for(int i = 0; i <= 21; i++){
sheet.setColumnWidth(i, 6000);
}
return workbook;
}
private static HSSFWorkbook populateXlsData(List<ArrayList<String>> dataList, HSSFWorkbook workbook)
{
try
{
if (dataList != null)
{
Sheet sheet = workbook.getSheetAt(0);
/* First two rows (0 and 1) are already used for the titles and the headers.
So data starts in row 2 */
int rowNum = 2;
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
style.setVerticalAlignment(VerticalAlignment.CENTER);
/* Cycle thru the ROWs and write them to the workbook */
for (ArrayList<String> dataRow : dataList)
{
if (dataRow != null)
{
Row row = sheet.createRow(rowNum++);
int nextCell = -1;
/*Cycle thru each value in each row */
for (String nextString : dataRow){
Cell cell = row.createCell(++nextCell);
cell.setCellValue(nextString);
cell.setCellStyle(style);
}
}
}
/*Add the final FOUO */
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,21));
Row lastRow = sheet.createRow(rowNum);
lastRow.setHeightInPoints(23);
Cell cell1 = lastRow.createCell(0);
cell1.setCellValue("For Official Use Only.");
/* Style top privacy act row(rowNum) */
cell1.setCellStyle(styleFouoRow(workbook));
}
}catch (Exception exp){
throw exp;
}
return workbook;
}
private static CellStyle styleFouoRow(HSSFWorkbook inWorkBook)
{
CellStyle styleForFouoRow = inWorkBook.createCellStyle();
styleForFouoRow.setWrapText(true);
styleForFouoRow.setVerticalAlignment(VerticalAlignment.CENTER);
styleForFouoRow.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font = inWorkBook.createFont();
font.setFontHeightInPoints((short) 11);
font.setFontName("Calibri");
styleForFouoRow.setFont(font);
return styleForFouoRow;
}
}
