需求
身份证号超过长度，自动变成科学计数法了，需要把单元格设置成文本
遇到的问题
找了两个解决方式但是都不生效
一个是使用Apache POI 来设置单元格为文本
DataFormat fmt = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
worksheet.setDefaultColumnStyle(0, textStyle);
另一个是 使用easyExcel 设置type 为String
WriteCellData<T> writeCellData = new WriteCellData<>(text);
writeCellData.setType(CellDataTypeEnum.STRING);
解决方式
把cellStyle 设置到cell上才生效，设置到workSheet上不生效
DataFormat fmt = wb.createDataFormat();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("@"));
cell.setCellStyle(cellStyle);
完整的代码如下
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import java.util.List;
import org.apache.commons.collections4.CollectionUtils;
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.Workbook;
/**
* 单元格设置文本格式
*/
public class RowFormatSetTextHandler implements CellWriteHandler {
/**
*需要设置为文本的单元格的ColumnIndex, ColumnIndex 从0开始计数
*/
private List<String> columnIndexList;
public RowFormatSetTextHandler(List<String> columnIndexList) {
this.columnIndexList = columnIndexList;
}
public RowFormatSetTextHandler() {
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
WriteWorkbookHolder writeWorkbookHolder = context.getWriteWorkbookHolder();
Workbook workbook = writeWorkbookHolder.getWorkbook();
Cell cell = context.getCell();
DataFormat fmt = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
int columnIndex = cell.getColumnIndex();
String columnIndexString = String.valueOf(columnIndex);
if (CollectionUtils.isNotEmpty(columnIndexList) && columnIndexList.contains(columnIndexString)) {
cell.setCellStyle(textStyle);
}
}
}
生成 excel
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new RowFormatSetTextHandler(excelExportVo.getTextColumnIndexList()))
.sheet("导出").doWrite(excelExportVo.getDataList());
