Recently, I received a request. The customer was not satisfied with the csv file originally exported and wanted to export an excel file. Don't you just export Excel files? It's a little fun, so you're full of confidence. Pick up the exported code from the Internet, a CV method, and get it done! The code is as follows:
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
@Slf4j
public class ExcelUtils {
// New Excel file suffix
private static final String EXCEL_SUFFIX = ".xlsx";
/**
* Export core implementation
*
* @param fileName
* @param headers
* @param dataList
* @return XSSFWorkbook
*/
@Deprecated
private static XSSFWorkbook writeDataToWorkbook(String fileName, List<String> headers, List<Map<String, Object>> dataList) {
// Create a workbook
XSSFWorkbook workbook = new XSSFWorkbook();
try {
// Create a worksheet
XSSFSheet sheet = workbook.createSheet(fileName);
// Format header font
XSSFFont headersFont = workbook.createFont();
headersFont.setColor(new XSSFColor(java.awt.Color.DARK_GRAY));
headersFont.setFontHeightInPoints((short) 14);
headersFont.setBold(true);
// Format body font
XSSFFont dataSetFont = workbook.createFont();
dataSetFont.setColor(new XSSFColor(java.awt.Color.BLACK));
dataSetFont.setBold(false);
// Create header style
XSSFCellStyle headersStyle = workbook.createCellStyle();
headersStyle.setBorderTop(BorderStyle.THIN);
headersStyle.setBorderBottom(BorderStyle.THIN);
headersStyle.setBorderLeft(BorderStyle.THIN);
headersStyle.setBorderRight(BorderStyle.THIN);
headersStyle.setFont(headersFont);
// Header content alignment: Center
headersStyle.setAlignment(HorizontalAlignment.CENTER);
// Create text style
XSSFCellStyle textStyle = workbook.createCellStyle();
textStyle.setBorderBottom(BorderStyle.THIN);
textStyle.setBorderRight(BorderStyle.THIN);
textStyle.setBorderLeft(BorderStyle.THIN);
textStyle.setFont(dataSetFont);
// Data content alignment: left
textStyle.setAlignment(HorizontalAlignment.LEFT);
// Create a number style
XSSFCellStyle numeralStyle = workbook.createCellStyle();
numeralStyle.setBorderBottom(BorderStyle.THIN);
numeralStyle.setBorderRight(BorderStyle.THIN);
numeralStyle.setBorderLeft(BorderStyle.THIN);
numeralStyle.setFont(dataSetFont);
// Data content alignment: right
numeralStyle.setAlignment(HorizontalAlignment.RIGHT);
// Format data here
XSSFDataFormat df = workbook.createDataFormat();
int index = 0;
// Create and style headers
XSSFRow row = sheet.createRow(index);
for (int i = 0; i < headers.size(); i++) {
sheet.setColumnWidth(i, 20 * 256);
XSSFCell cell = row.createCell(i);
cell.setCellStyle(headersStyle);
XSSFRichTextString text = new XSSFRichTextString(headers.get(i));
cell.setCellValue(text);
}
// Export body data and set its style
for (Map<String, Object> data : dataList) {
index++;
row = sheet.createRow(index);
int column = 0;
for (String key : data.keySet()) {
XSSFCell cell = row.createCell(column++);
Object value = data.get(key);
if (value == null) {
continue;
}
String dataType = value.getClass().getName();
if (dataType.endsWith("BigDecimal") || dataType.endsWith("Double")) {
// Number format with decimal point
cell.setCellStyle(numeralStyle);
numeralStyle.setDataFormat(df.getFormat("0.00"));//Keep two decimal places
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (dataType.endsWith("Integer") || dataType.endsWith("Long")) {
// Integer numeric format
cell.setCellStyle(numeralStyle);
numeralStyle.setDataFormat(df.getFormat("General"));//The data format is conventional
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (dataType.endsWith("Date")) {
//Date to string
cell.setCellStyle(textStyle);
cell.setCellValue(date2Str((Date) value, "yyyy-MM-dd HH:mm:ss"));
} else {
// Text format
cell.setCellStyle(textStyle);
cell.setCellValue(value.toString());
}
}
}
} catch (Exception e) {
log.error("writeDataToWorkbook error, ", e);
}
return workbook;
}
/**
* Test class
*/
public static void main(String[] args) {
String filename = date2Str(new Date(), "yyyyMMddHHmmss");
List<String> headers = Arrays.asList("Customer number", "cell-phone number", "full name", "Creation time", "Account balance", "Account balance 1", "Account balance 2", "Age");
List<Map<String, Object>> datas = new ArrayList<>();
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", 123);
map.put("mobile", "0833344545");
map.put("name", "jfdkdb ft#E@");
map.put("time", new Date());
map.put("amount", new BigDecimal("34276.8601"));
map.put("amount1", 34276.8);
map.put("amount2", 323455.10);
map.put("days", 24);
datas.add(map);
XSSFWorkbook wb = writeDataToWorkbook(filename, headers, datas);
try {
File file = new File("/Users/test/" + filename + EXCEL_SUFFIX);
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
System.err.println(e.getMessage());
}
System.out.println("export excel " + filename + " succeed!");
}
public static String date2Str(Date date, String pattern) {
if (null == date) return "";
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
return sdf.format(date);
}
}
Run the project and test the wave locally to see the effect:
There seems to be nothing wrong with it.... The decimal places of the balance columns seem to be wrong!
Looking back on the code, I set it. Why didn't it take effect???
So I continued to pick up information on the Internet and tried all kinds of theories:
1. As shown in the figure below with celltype setting, the test effect is the same as the first time, and it fails;
2. I also guessed whether it was caused by changing to double type, so I changed it to the following figure, and the assignment was in string format:
The test effect is the same as the first time. No, it's worse than the first time. Even if the decimal places are wrong, there is a small green arrow in the upper left corner of the cell, which means that the customer can't directly check the total amount. It fails;
Such a simple function, I actually stuck here, mentality.. It broke....
3. You forced me. I'm going to make a unique move. I process the data myself, so I changed it to this:
Running, the effect is good, the decimal places are right, but the cell is still in text format....
4. The data formats are all right. The cell format is simple. Just set the input parameters. So easy! Immediately change to the following figure:
Run again and the result is as follows: collapse... Completely collapse.. It's over!
Surrendered.. I decided that the text format should be the text format. If the data format is right, I'm ready to use scheme 3.
Just when I wanted to commit, I suddenly saw that the balance data in result 4 was divided by "," and I wiped it. Isn't this the integer format I set??? I decided to try again. I created a new number format for floating-point data, and the code was changed as follows:
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
@Slf4j
public class ExcelUtils {
// New Excel file suffix
private static final String EXCEL_SUFFIX = ".xlsx";
/**
* Export core implementation
*
* @param fileName
* @param headers
* @param dataList
* @return XSSFWorkbook
*/
@Deprecated
private static XSSFWorkbook writeDataToWorkbook(String fileName, List<String> headers, List<Map<String, Object>> dataList) {
// Create a workbook
XSSFWorkbook workbook = new XSSFWorkbook();
try {
// Create a worksheet
XSSFSheet sheet = workbook.createSheet(fileName);
// Format header font
XSSFFont headersFont = workbook.createFont();
headersFont.setColor(new XSSFColor(java.awt.Color.DARK_GRAY));
headersFont.setFontHeightInPoints((short) 14);
headersFont.setBold(true);
// Format body font
XSSFFont dataSetFont = workbook.createFont();
dataSetFont.setColor(new XSSFColor(java.awt.Color.BLACK));
dataSetFont.setBold(false);
// Format data here
XSSFDataFormat df = workbook.createDataFormat();
// Create header style
XSSFCellStyle headersStyle = workbook.createCellStyle();
headersStyle.setBorderTop(BorderStyle.THIN);
headersStyle.setBorderBottom(BorderStyle.THIN);
headersStyle.setBorderLeft(BorderStyle.THIN);
headersStyle.setBorderRight(BorderStyle.THIN);
headersStyle.setFont(headersFont);
// Header content alignment: Center
headersStyle.setAlignment(HorizontalAlignment.CENTER);
// Create text style
XSSFCellStyle textStyle = workbook.createCellStyle();
textStyle.setBorderBottom(BorderStyle.THIN);
textStyle.setBorderRight(BorderStyle.THIN);
textStyle.setBorderLeft(BorderStyle.THIN);
textStyle.setFont(dataSetFont);
// Data content alignment: left
textStyle.setAlignment(HorizontalAlignment.LEFT);
// Create floating point number style
XSSFCellStyle floatStyle = workbook.createCellStyle();
floatStyle.setBorderBottom(BorderStyle.THIN);
floatStyle.setBorderRight(BorderStyle.THIN);
floatStyle.setBorderLeft(BorderStyle.THIN);
floatStyle.setAlignment(HorizontalAlignment.RIGHT);
floatStyle.setFont(dataSetFont);
floatStyle.setDataFormat(df.getFormat("#,##0.00"));
// Create an integer number style
XSSFCellStyle integerStyle = workbook.createCellStyle();
integerStyle.setBorderBottom(BorderStyle.THIN);
integerStyle.setBorderRight(BorderStyle.THIN);
integerStyle.setBorderLeft(BorderStyle.THIN);
integerStyle.setAlignment(HorizontalAlignment.RIGHT);
integerStyle.setFont(dataSetFont);
integerStyle.setDataFormat(df.getFormat("0"));
int index = 0;
// Create and style headers
XSSFRow row = sheet.createRow(index);
for (int i = 0; i < headers.size(); i++) {
sheet.setColumnWidth(i, 20 * 256);
XSSFCell cell = row.createCell(i);
cell.setCellStyle(headersStyle);
XSSFRichTextString text = new XSSFRichTextString(headers.get(i));
cell.setCellValue(text);
}
// Export body data and set its style
for (Map<String, Object> data : dataList) {
index++;
row = sheet.createRow(index);
int column = 0;
for (String key : data.keySet()) {
XSSFCell cell = row.createCell(column++);
Object value = data.get(key);
if (value == null) {
continue;
}
String dataType = value.getClass().getName();
if (dataType.endsWith("BigDecimal") || dataType.endsWith("Double") || dataType.endsWith("Float")) {
cell.setCellStyle(floatStyle); // Number format with decimal point
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (dataType.endsWith("Integer") || dataType.endsWith("Long")) {
cell.setCellStyle(integerStyle); // Integer numeric format
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (dataType.endsWith("Date")) {
cell.setCellStyle(textStyle); //Date to string
cell.setCellValue(date2Str((Date) value, "yyyy-MM-dd HH:mm:ss"));
} else {
cell.setCellStyle(textStyle); // Text format
cell.setCellValue(value.toString());
}
}
}
} catch (Exception e) {
log.error("writeDataToWorkbook error, ", e);
}
return workbook;
}
/**
* Test class
*/
public static void main(String[] args) {
String filename = date2Str(new Date(), "yyyyMMddHHmmss");
List<String> headers = Arrays.asList("Customer number", "cell-phone number", "full name", "Creation time", "Account balance", "Account balance 1", "Account balance 2", "Age");
List<Map<String, Object>> datas = new ArrayList<>();
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", 123);
map.put("mobile", "0833344545");
map.put("name", "jfdkdb ft#E@");
map.put("time", new Date());
map.put("amount", new BigDecimal("34276.8601"));
map.put("amount1", 34276.8);
map.put("amount2", 323455.10);
map.put("days", 24);
datas.add(map);
XSSFWorkbook wb = writeDataToWorkbook(filename, headers, datas);
try {
File file = new File("/Users/test/" + filename + EXCEL_SUFFIX);
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
System.err.println(e.getMessage());
}
System.out.println("export excel succeed!");
}
public static String date2Str(Date date, String pattern) {
if (null == date) return "";
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
return sdf.format(date);
}
}
The results are as follows:
I'll wipe it. It's really good???!
it turns out to be the case that
XSSFCellStyle integerStyle = workbook.createCellStyle();
The created style can only be assigned once. The subsequent assignment is invalid.
Although I've been tortured by this small problem for nearly a day and my mentality has collapsed several times, it's good that I finally found the reason. It's great to solve the problem. My broken mentality has been reunited and stronger than before...