(For my notes ... but I hope it helps someone ...)
Excel output that is occasionally encountered in business applications. Hmm. I like CSV.
so, Since Maven is used, it is defined in pom.xml.
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
Define and Maven install.
Maven dependency
poi-ooxml-3.17.jar
poi-3.17.jar
commons-codec-1.10.jar
commons-collections4-4.1.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
curvesapi-1.04.jar
Will be included. (If it's a closed environment, you have to collect it from scratch ...)
The source is ↓
Excel output
private static void outputExcel() throws Exception {
String outputFilePath = "[Output destination]\\tmp.xlsx";
Workbook book = null;
FileOutputStream fout = null;
try {
book = new SXSSFWorkbook();
Font font = book.createFont();
font.setFontName("MS gothic");
font.setFontHeightInPoints((short) 9);
DataFormat format = book.createDataFormat();
//Style for header string
CellStyle style_header = book.createCellStyle();
style_header.setBorderBottom(BorderStyle.THIN);
App.setBorder(style_header, BorderStyle.THIN);
style_header.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
style_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style_header.setVerticalAlignment(VerticalAlignment.TOP);
style_header.setFont(font);
//Style for strings
CellStyle style_string = book.createCellStyle();
App.setBorder(style_string, BorderStyle.THIN);
style_string.setVerticalAlignment(VerticalAlignment.TOP);
style_string.setFont(font);
//Style for strings with line breaks
CellStyle style_string_wrap = book.createCellStyle();
App.setBorder(style_string_wrap, BorderStyle.THIN);
style_string_wrap.setVerticalAlignment(VerticalAlignment.TOP);
style_string_wrap.setWrapText(true);
style_string_wrap.setFont(font);
//Style for integers
CellStyle style_int = book.createCellStyle();
App.setBorder(style_int, BorderStyle.THIN);
style_int.setDataFormat(format.getFormat("#,##0;-#,##0"));
style_int.setVerticalAlignment(VerticalAlignment.TOP);
style_int.setFont(font);
//Decimal style
CellStyle style_double = book.createCellStyle();
App.setBorder(style_double, BorderStyle.THIN);
style_double.setDataFormat(format.getFormat("#,##0.0;-#,##0.0"));
style_double.setVerticalAlignment(VerticalAlignment.TOP);
style_double.setFont(font);
//Style for circle display
CellStyle style_yen = book.createCellStyle();
App.setBorder(style_yen, BorderStyle.THIN);
style_yen.setDataFormat(format.getFormat("\"\\\"#,##0;\"\\\"-#,##0"));
style_yen.setVerticalAlignment(VerticalAlignment.TOP);
style_yen.setFont(font);
//Style for percentage display
CellStyle style_percent = book.createCellStyle();
App.setBorder(style_percent, BorderStyle.THIN);
style_percent.setDataFormat(format.getFormat("0.0%"));
style_percent.setVerticalAlignment(VerticalAlignment.TOP);
style_percent.setFont(font);
//Style for date and time display
CellStyle style_datetime = book.createCellStyle();
App.setBorder(style_datetime, BorderStyle.THIN);
style_datetime.setDataFormat(format.getFormat("yyyy/mm/dd hh:mm:ss"));
style_datetime.setVerticalAlignment(VerticalAlignment.TOP);
style_datetime.setFont(font);
Row row;
int rowNumber;
Cell cell;
int colNumber;
//Creating a sheet(Try to make 3 sheets)
Sheet sheet;
for (int i = 0; i < 3; i++) {
sheet = book.createSheet();
if (sheet instanceof SXSSFSheet) {
((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();
}
//Sheet name setting
book.setSheetName(i, "Sheet" + (i + 1));
//Creating a header line
rowNumber = 0;
colNumber = 0;
row = sheet.createRow(rowNumber);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("No.");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("String");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Character string with line breaks");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("integer");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Decimal");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Circle");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("percent");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Date and time");
cell = row.createCell(colNumber);
cell.setCellStyle(style_header);
cell.setCellType(CellType.STRING);
cell.setCellValue("Circle(8%Tax included)");
//Fixed window frame
sheet.createFreezePane(1, 1);
//Auto filter settings in header line
sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, colNumber));
//Automatic adjustment of column width
for (int j = 0; j <= colNumber; j++) {
sheet.autoSizeColumn(j, true);
}
//Data row generation(Try to make 10 lines)
for (int j = 0; j < 10; j++) {
rowNumber++;
colNumber = 0;
row = sheet.createRow(rowNumber);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_int);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(j + 1);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_string);
cell.setCellType(CellType.STRING);
cell.setCellValue("this is" + (j + 1) + "The data in the line.");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_string_wrap);
cell.setCellType(CellType.STRING);
cell.setCellValue("this is\n" + (j + 1) + "Line\n data.");
cell = row.createCell(colNumber++);
cell.setCellStyle(style_int);
cell.setCellType(CellType.STRING);
cell.setCellValue((j + 1) * 1000);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_double);
cell.setCellType(CellType.STRING);
cell.setCellValue((double) (j + 1) * 1000);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_yen);
cell.setCellType(CellType.STRING);
cell.setCellValue((j + 1) * 1000);
cell = row.createCell(colNumber++);
cell.setCellStyle(style_percent);
cell.setCellType(CellType.STRING);
cell.setCellValue((double) (j + 1));
cell = row.createCell(colNumber++);
cell.setCellStyle(style_datetime);
cell.setCellType(CellType.STRING);
cell.setCellValue(new Date());
cell = row.createCell(colNumber);
cell.setCellStyle(style_yen);
cell.setCellType(CellType.FORMULA);
cell.setCellFormula("ROUND(" + App.getExcelColumnString(colNumber - 3) + (rowNumber + 1) + "*1.08, 0)");
//Automatic adjustment of column width
for (int k = 0; k <= colNumber; k++) {
sheet.autoSizeColumn(k, true);
}
}
}
//Try erasing sheet 3
book.removeSheetAt(2);
//File output
fout = new FileOutputStream(outputFilePath);
book.write(fout);
}
finally {
if (fout != null) {
try {
fout.close();
}
catch (IOException e) {
}
}
if (book != null) {
try {
/*
Because SXSSFWorkbook generates a lot of temporary files at the cost of saving memory space
You need to dispose and delete temporary files when you no longer need them
*/
((SXSSFWorkbook) book).dispose();
}
catch (Exception e) {
}
}
}
}
private static void setBorder(CellStyle style, BorderStyle border) {
style.setBorderBottom(border);
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
}
private final static String[] LIST_ALPHA = {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
};
private static String getExcelColumnString(int column) {
String result = "";
if (column >= 0) {
if (column / App.LIST_ALPHA.length > 0) {
result += getExcelColumnString(column / App.LIST_ALPHA.length - 1);
}
result += App.LIST_ALPHA[column % App.LIST_ALPHA.length];
}
return result;
}
It looks like VBA. That said, I have to look at the pages that are helpful.
Recommended Posts