-
2021-07-27 10:06:15
系统单元格格式预设值:
S/No.
Format
Vlaue S/No Format
Value 更多相关内容 -
POI操作Excel
2020-04-29 20:15:29// 这个格式字符串可以看BuiltinFormats类的字符串数组_formats style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); ...POI 操作Excel
pom依赖如下:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.0</version> </dependency>
1. 创建,读取,输出表格
// 创建xls后缀文件表格 Workbook wb = new HSSFWorkbook();
// 创建xlsx后缀文件表格 Workbook wb = new XSSFWorkbook();
// 读取表格 try (InputStream inp = new FileInputStream("workbook.xlsx")) { Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); }
// 输出表格 try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { wb.write(fileOut); } wb.close();
2. 创建sheet
sheet名字不能超过31个字符,而且不同包含一些特殊字符
Sheet sheet = wb.createSheet("new sheet");
3. 创建单元格
Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(1);
4. 单元格显示时间
//设置单元格时间显示格式 CreationHelper createHelper = wb.getCreationHelper(); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm")); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); // 不设置格式, 最终在excel显示的是1900年0时0分0秒到现在的天数,是一个小数 cell = row.createCell(3); cell.setCellValue(new Date());
5. 不同类型的单元格
POI没有日期类型单元格,由CellType这个枚举类可以看出,只有数字,字符串,公式,空白,布尔,error六种
row = sheet.createRow(1); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue("a string"); row.createCell(2).setCellFormula("A1+A1"); Cell cellBlank = row.createCell(3); cellBlank.setCellValue("1"); cellBlank.setBlank(); row.createCell(4).setCellValue(true); row.createCell(5).setCellErrorValue(FormulaError.NULL.getCode());
6. 遍历单元格
int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) { Row r = sheet.getRow(rowNum); if (r == null) { // 整行是空的 continue; } int lastColumn = r.getLastCellNum(); for (int cn = 0; cn < lastColumn; cn++) { Cell c = r.getCell(cn); if (c == null) { // 单元格是空的 System.out.println("empty"); } else { // 单元格不是空的 switch (c.getCellType()) { case STRING: System.out.println("string:" + c.getStringCellValue());break; case NUMERIC:System.out.println("num:" + c.getNumericCellValue());break; case BOOLEAN:System.out.println("boolean:" + c.getBooleanCellValue());break; case FORMULA: System.out.println("formula:" + c.getCellFormula() );break; case BLANK:System.out.println("blank:" + c );break; case ERROR:System.out.println("error:" + c.getErrorCellValue());break; default:System.out.println("other"); } } } }
7. 另一种获取单元格内容方式
int rowStart = sheet.getFirstRowNum(); int rowEnd = sheet.getLastRowNum(); DataFormatter formatter = new DataFormatter(); for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) { Row r = sheet.getRow(rowNum); if (r == null) { // 整行是空的 continue; } int lastColumn = r.getLastCellNum(); for (int cn = 0; cn < lastColumn; cn++) { Cell c = r.getCell(cn); CellReference cellRef = new CellReference(r.getRowNum(), c.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); String text = formatter.formatCellValue(c); System.out.println(text); } }
8. 提取文本, 只支持xls格式excel
ExcelExtractor extractor = new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); System.out.println(extractor.getText());
9. 单元格对齐
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(2); row.setHeightInPoints(30); // HorizontalAlignment水平对齐 VerticalAlignment垂直对齐 createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM); // CENTER_SELECTION 选择范围内中央对齐 createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM); // FILL 会将内容复制多份填充单元格 createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER); // GENERAL 标准,就是初始的单元格 createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER); // JUSTIFY 调节对齐,单元格内容自动换行 createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY); createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP); createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) { Cell cell = row.createCell(column); cell.setCellValue("Align It"); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); }
10. 单元格边框
Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue(4); CellStyle style = wb.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderTop(BorderStyle.MEDIUM_DASHED); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); cell.setCellStyle(style);
11. 填充单元格
CellStyle style = wb.createCellStyle(); // 设置填充背景颜色 style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); // 设置填充模式 style.setFillPattern(FillPatternType.BIG_SPOTS); Cell cell = row.createCell(1); cell.setCellValue("X"); cell.setCellStyle(style); style = wb.createCellStyle(); // 设置填充前景颜色 style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell = row.createCell(2); cell.setCellValue("X"); cell.setCellStyle(style);
12. 合并单元格
Cell cell = row.createCell(1); cell.setCellValue("This is a test of merging"); sheet.addMergedRegion(new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 2 //last column (0-based) ));
CellRangeAddress region = CellRangeAddress.valueOf("B2:E5"); sheet.addMergedRegion( region );
13. 设置字体
Font font = wb.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); CellStyle style = wb.createCellStyle(); style.setFont(font); Cell cell = row.createCell(1); cell.setCellValue("测试字体,This is a test of fonts"); cell.setCellStyle(style);
14. 单元格内换行
Cell cell = row.createCell(2); cell.setCellValue("Use \n with word wrap on to create a new line"); // 要启用换行符,需要使用wrap = true设置单元格样式 CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs); // 增加行高以容纳两行文本 row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); // 调整列宽以适合内容 sheet.autoSizeColumn(2);
15. 单元格数据格式化
Row row; CellStyle style; Cell cell; DataFormat format = wb.createDataFormat(); int rowNum = 0; int colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); // 这个格式字符串可以看BuiltinFormats类的字符串数组_formats style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style);
16. sheet移动行
// 第5到第10(0为基准,实际就是第6到第11行), 往下移动5行, 负数就是往上移动 sheet.shiftRows(5, 10, 5);
17. 将sheet设为选中
sheet.setSelected(true);
18. 设置sheet的缩放放大倍数
sheet.setZoom(75);
19. 创建拆分和冻结窗格
// 冻结窗格 sheet.createFreezePane( 0, 1, 0, 1 ); // 拆分 sheet.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );
20. 插入图片
InputStream is = new FileInputStream("image1.jpg"); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); is.close(); CreationHelper helper = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(0); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(1, 1);
21. 单元格评论
CreationHelper factory = wb.getCreationHelper(); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(""); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex()+1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum()+3); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString("你好"); comment.setString(str); comment.setAuthor("someone"); cell.setCellComment(comment);
22. 设置下拉框
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Data Validation"); DataValidationHelper helper = new XSSFDataValidationHelper(sheet); CellRangeAddressList addressList = new CellRangeAddressList(0, 1, 0, 0); //设置下拉框数据 String [] list = {"张三", "李四"}; DataValidationConstraint constraint = helper.createExplicitListConstraint(list); DataValidation dataValidation = helper.createValidation(constraint, addressList); //处理Excel兼容性问题 if (dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else { dataValidation.setSuppressDropDownArrow(false); } sheet.addValidationData(dataValidation);
23. 开启自动筛选功能
sheet.setAutoFilter(CellRangeAddress.valueOf("A1:F20"));
24. 设置单元格属性
Map<String, Object> properties = new HashMap<>(); properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM); properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM); properties.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM); properties.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM); properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex()); properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex()); properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex()); properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex()); Row row = sheet.createRow(1); Cell cell = row.createCell(1); CellUtil.setCellStyleProperties(cell, properties); for (int ix = 3; ix <= 5; ix++) { row = sheet.createRow(ix); for (int iy = 3; iy <= 5; iy++) { cell = row.createCell(iy); CellUtil.setCellStyleProperties(cell, properties); } }
25. 绘制边框
PropertyTemplate pt = new PropertyTemplate(); pt.drawBorders(new CellRangeAddress(1, 3, 1, 3), BorderStyle.MEDIUM, BorderExtent.ALL); pt.drawBorders(new CellRangeAddress(5, 7, 1, 3), BorderStyle.MEDIUM, BorderExtent.OUTSIDE); pt.drawBorders(new CellRangeAddress(5, 7, 1, 3), BorderStyle.THIN, BorderExtent.INSIDE); pt.drawBorders(new CellRangeAddress(9, 11, 1, 3), BorderStyle.MEDIUM, IndexedColors.RED.getIndex(), BorderExtent.OUTSIDE); pt.drawBorders(new CellRangeAddress(9, 11, 1, 3), BorderStyle.MEDIUM, IndexedColors.BLUE.getIndex(), BorderExtent.INSIDE_VERTICAL); pt.drawBorders(new CellRangeAddress(9, 11, 1, 3), BorderStyle.MEDIUM, IndexedColors.GREEN.getIndex(), BorderExtent.INSIDE_HORIZONTAL); pt.drawBorders(new CellRangeAddress(10, 10, 2, 2), BorderStyle.NONE, BorderExtent.ALL); pt.applyBorders(sheet);
参考
https://poi.apache.org/components/spreadsheet/quick-guide.html
-
excel导出文本格式设置为数值(easypoi)
2020-06-03 18:06:29见BuiltinFormats源码 (强调 - 自定义格式一定要在_formats 数组内,否则无效): public static int getBuiltinFormat(String pFmt) { String fmt = "TEXT".equalsIgnoreCase(pFmt) ? "@" : pFmt; int i = -1; ...文章目录
场景
导出的数值单元格格式是文本。 客户每次都要手动转为数值,往往一个表格就是好多万数据,转换起来等半天。
解决方案
听说@Excel 设置type=10即可。 实测无效。
设置type=10对不对
这样设置是对的。 但是为什么没有效果呢?
仔细跟代码,发现type=10,会设置单元格的type为 Cell.CELL_TYPE_NUMERIC 。 单元格的值属于CellValue ,和CelType都是属于Cell 。
但是单元格属性 属于 CellStyle。
所以虽然值的输出符合规范了,但是单元格显示还是为文本。导出用到的主要元素和依赖关系
使用easypoi 导出excel只用简单的一个方法。
ExcelExportUtil.exportExcel(params, StatisticEntity.class, list);
主要元素列表如下:名称 描述 ExportParams 定义导出的文件名,中文名,文件类型,导出样式等。 ExcelExportStatisticStyler 继承自ExcelExportStylerDefaultImpl类,定义样式。
通过params.setStyle(ExcelExportStatisticStyler.class); 设置到ExportParams中。
如果不指定,默认使用ExcelExportStylerDefaultImplStatisticEntity 导出单元格的实体,这里可以设置数据规则 List<StatisticEntity> list = new ArrayList<StatisticEntity>(); // TODO list中添加数据根据情况自己写 ExportParams params = new ExportParams("2412312", "测试", ExcelType.XSSF); params.setStyle(ExcelExportStatisticStyler.class); Workbook workbook = ExcelExportUtil.exportExcel(params, StatisticEntity.class, list);
打造属于自己的styler
好的,既然ExportParams 可以setStyle,那么我们写个类,继承ExcelExportStylerDefaultImpl 重写 getStyles方法不就成了么。
错误的写法
思路,依照最小改动原则,先获取已有的style,然后添加type=10的处理逻辑。代码:
@Override public CellStyle getStyles2(boolean noneStyler, ExcelExportEntity entity) { CellStyle styles = super.getStyles(noneStyler, entity); // 获取style // type=10 的处理 if (entity != null && 10==entity.getType()) { styles.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00")); return styles; } return styles; }
测了下发现报错, 是因为子类重写getStyles()方法,super.getStyles()调的就是子类的getStyles()方法。 这不无限循环了么。
重新写逻辑
代码:
@Override public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) { if (entity != null && 10==entity.getType()) { return numberCellStyle; } return super.getStyles(noneStyler, entity); }
实测成功。
导出的excel单元格格式为 自定义 0.00 。这还不是客户要求的数值格式。但是只能做到这了。
至少我目前没找到如何设置为数值格式。ExcelExportStatisticStyler 类完整的代码
public class ExcelExportStatisticStyler extends ExcelExportStylerDefaultImpl { private CellStyle numberCellStyle; public ExcelExportStatisticStyler(Workbook workbook) { super(workbook); createNumberCellStyler(); } private void createNumberCellStyler() { numberCellStyle = workbook.createCellStyle(); numberCellStyle.setAlignment(HorizontalAlignment.CENTER); numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); numberCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00")); numberCellStyle.setWrapText(true); } @Override public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) { if (entity != null && 10==entity.getType()) { return numberCellStyle; } return super.getStyles(noneStyler, entity); } }
setDataFormat有2种方式入参形式
1、字符串格式(推荐,可读性更强)
2、_formats 数组的下标两种方式,本质上没区别, 传入string也是根据字面值,找_formats对应的下标。
见BuiltinFormats源码 (强调 - 自定义格式一定要在_formats 数组内,否则无效):public static int getBuiltinFormat(String pFmt) { String fmt = "TEXT".equalsIgnoreCase(pFmt) ? "@" : pFmt; int i = -1; for (String f : _formats) { i++; if (f.equals(fmt)) { return i; } } return -1; }
两种设置的例子:
// 使用字符串定义格式 cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00")); // BuiltinFormats._formats 数组中的下标 cellStyle.setDataFormat((short) 1);
解决总结
步骤:
@Excel 中添加 type=10
写个类继承ExcelExportStylerDefaultImpl ,重写getStyles()方法
导出前ExportParams。既然重写了getStyles(),那么其实type=10并不是唯一的办法。
官网就是根据name中是否包含int,double等类型来判断的,当然@Excel中的name要记得配合。(感觉type=10比改name更方便,更解耦)setAlignment(HorizontalAlignment.CENTER) 报错
版本的问题。 报错代码如下:
numberCellStyle.setAlignment(HorizontalAlignment.CENTER); numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
调整为如下代码即可:
numberCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); numberCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
debug跟代码一行一行看
ExcelExportBase类中的createCells方法中,如果类型为double,则进入如下逻辑:
else if (entity.getType() == BaseEntityTypeConstants.DoubleType) { }
ExcelExportBase类中的createDoubleCell方法(type=10时设置了cellType):
public void createDoubleCell(Row row, int index, String text, CellStyle style,ExcelExportEntity entity) { Cell cell = row.createCell(index); if (text != null && text.length() > 0) { cell.setCellValue(Double.parseDouble(text)); } else { cell.setCellValue(-1); } cell.setCellType(Cell.CELL_TYPE_NUMERIC); // 这里是设置了celltype为0,表示数字格式 if (style != null) { // 这里设置style cell.setCellStyle(style); } addStatisticsData(index, text, entity); }
是不是type不影响页面excel的文本格式,而是由style控制的呢。
easypoi支持的自定义格式列表
BuiltinFormats类的_formats列表里的自定义格式才有效,否则就会使用文本格式。
private final static String[] _formats = { "General", "0", "0.00", "#,##0", "#,##0.00", "\"$\"#,##0_);(\"$\"#,##0)", "\"$\"#,##0_);[Red](\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)", "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)", "0%", "0.00%", "0.00E+00", "# ?/?", "# ??/??", "m/d/yy", "d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM", "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm", // 0x17 - 0x24 reserved for international and undocumented // TODO - one junit relies on these values which seems incorrect "reserved-0x17", "reserved-0x18", "reserved-0x19", "reserved-0x1A", "reserved-0x1B", "reserved-0x1C", "reserved-0x1D", "reserved-0x1E", "reserved-0x1F", "reserved-0x20", "reserved-0x21", "reserved-0x22", "reserved-0x23", "reserved-0x24", "#,##0_);(#,##0)", "#,##0_);[Red](#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);[Red](#,##0.00)", "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)", "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)", "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", "mm:ss", "[h]:mm:ss", "mm:ss.0", "##0.0E+0", "@" };
-
Apache POI导出格式设置为文本格式
2021-09-02 14:54:58参照org.apache.poi.ss.usermodel.BuiltinFormats的说明 Open Declaration org.apache.poi.ss.usermodel.BuiltinFormats Utility to identify built-in formats. The following is a list of the formats as ...知识点
参照org.apache.poi.ss.usermodel.BuiltinFormats的说明
Open Declaration org.apache.poi.ss.usermodel.BuiltinFormats
Utility to identify built-in formats. The following is a list of the formats as returned by this class.
0, "General" 1, "0" 2, "0.00" 3, "#,##0" 4, "#,##0.00" 5, "$#,##0_);($#,##0)" 6, "$#,##0_);[Red]($#,##0)" 7, "$#,##0.00);($#,##0.00)" 8, "$#,##0.00_);[Red]($#,##0.00)" 9, "0%" 0xa, "0.00%" 0xb, "0.00E+00" 0xc, "# ?/?" 0xd, "# ??/??" 0xe, "m/d/yy" 0xf, "d-mmm-yy" 0x10, "d-mmm" 0x11, "mmm-yy" 0x12, "h:mm AM/PM" 0x13, "h:mm:ss AM/PM" 0x14, "h:mm" 0x15, "h:mm:ss" 0x16, "m/d/yy h:mm" // 0x17 - 0x24 reserved for international and undocumented 0x25, "#,##0_);(#,##0)" 0x26, "#,##0_);[Red](#,##0)" 0x27, "#,##0.00_);(#,##0.00)" 0x28, "#,##0.00_);[Red](#,##0.00)" 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)" 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)" 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)" 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)" 0x2d, "mm:ss" 0x2e, "[h]:mm:ss" 0x2f, "mm:ss.0" 0x30, "##0.0E+0" 0x31, "@" - This is text format. 0x31 "text" - Alias for "@"
实现
//设置单元格格式为文本格式 CellStyle textStyle = sxssfWorkbook.createCellStyle(); DataFormat format = sxssfWorkbook.createDataFormat(); textStyle.setDataFormat(format.getFormat("@")); cell.setCellStyle(textStyle);
-
easyExcel自定义样式
2020-12-21 14:47:31} easyExcel设置单元格格式:传一个short类型的下标,从内建格式中选择需要设置的格式 防止长数字字符串自动科学计数法 下面是poi内建格式:BuiltinFormats中定义的静态数组,初始化了50个格式 private static ... -
Excel 单元格样式讲解
2016-12-10 21:38:461.在org.apache.poi.ss.usermodel包中类BuiltinFormats 中定义了excel的内置单元格样式 -
解决EasyPoi导出Excel金额数值类型
2020-12-20 08:03:29numberCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#,##0.00")); numberCellStyle.setWrapText(true); } @Override public CellStyle getStyles(boolean noneStyler, ... -
【EXCEL终极总结分享】基于NPOI扩展封装的简易操作工具类库(简单灵活易用,支持导出、导入、上传等常见...
2019-06-06 23:40:00对于EXCEL的导入、导出,我之前已分享过多次,比如: 第一种方案:《我写的一个ExcelHelper通用类,可用于读取或生成数据》这个主要是利用把EXCEL当成一个DB来进行获取数据,...推荐指数:♥♥♥ 第二种方案:《MV... -
NPOI导出数值格式设置(我是保留四位小数,不足补0)
2019-01-22 10:22:38看了网上好多帖子,都是保留两位小数的,写法是: HSSFDataFormat.GetBuiltinFormat("0.00"); 于是想四位小数,就是多加两个00,变成: HSSFDataFormat.GetBuiltinFormat("... -
NPOI设置Excel单元格千分位格式
2019-11-26 09:36:04BuiltinFormats (POI API Documentation) http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html 根据上面的链接,设置Excel单元格千分位格式,以下写法都行: HSSFCellStyle ... -
5.Apache POI使用详解
2018-03-27 22:45:00一.POI结构与常用类 1.POI介绍 Apache POI是Apache软件基金会的开源项目,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office... -
EasyExcel使用
2020-10-19 21:34:24contentWriteCellStyle.setDataFormat((short)BuiltinFormats.getBuiltinFormat("@")); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 HorizontalCellStyleStrategy ... -
POI读写超大数据量Excel,解决超过几万行而导致内存溢出的问题(附源码)
2021-03-21 00:15:48import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.... -
POI 兼容导入Excel
2013-11-05 14:45:38dataCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy")); dataCell.setCellStyle(dataCellStyle); }else if(chs[k].getHeaderType().equals(HeaderType.DOUBLE)){ ... -
POI 单元格格式化 - 数据格式化
2013-12-19 00:01:49HSSFCellStyle cellPercentStyle = workbook.createCellStyle(); cellPercentStyle.setDataFormat((short)10);//百分数 一、第一种方式 //POI 自定义保留一位小数格式 ...HSSFCellStyle cell -
POI设置Excel单元格格式 (数…
2014-10-21 22:30:48转自:...HSSFWorkbook demoWorkBook = new HSSFWorkbook(); HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises"); HSSFCell -
【POI框架实战】——POI导出Excel时设置单元格类型为数值类型
2015-11-17 19:04:12最近做的一个ITFIN的项目中,导出的数据中有文本格式,也有货币格式,所以为了方便在将来导出的表格中做计算,存放货币先用正则表达式判断数据是否为数值型,如果为数值型,则设置单元格格式为整数或者小数;... -
阿里开源的这个库,让 Excel 导出不再复杂(既要能写,还要写的好看)
2021-10-07 16:10:04*Style:分为HeadStyle和ContentStyle,分别定义表头和表体样式 dataFormat:表头格式化,short 格式,是org.apache.poi.ss.usermodel.BuiltinFormats类中已定义格式的小标 border*:分别是 borderLeft、... -
POI dataFormat种类
2013-10-14 23:46:29环境:POI3.9 自定义DataFormat Utility to identify builtin formats. Now can handle user defined data formats also. The following is a list of the formats as returned by this class. ... -
这套 Excel 大批量导入导出解决方案,太牛逼了
2021-08-18 00:17:01点击关注公众号,回复“1024”获取2TB学习资源!概要Java对Excel的操作一般都是用POI,但是数据量大的话可能会导致频繁的FGC或OOM,这篇文章跟大家说下如果避免踩POI的坑,... -
Excel大批量数据的导入和导出,如何做优化?
2021-03-15 00:26:12作者:Albenhttps://albenw.github.io/posts/d093ca4e概要 Java对Excel的操作一般都是用POI,但是数据量大的话可能会导致频繁的FGC或... -
POI对EXCEL的操作【重点:如何设置CELL格式为文本格式】
2017-04-17 18:21:00实际开发过程中通常用到的就是从数据库导出EXCEL表格了,JXL可以这样做,其实POI也可以(关于JXL与POI的异同可访问我之前总结的文章),之前写过POI对七种文档(当然也包括EXCEL)的内容读取操作的文章,这次要写的就... -
POI对Excel自定义日期格式的读取
2015-07-01 15:33:00帖子来源:http://yl-fighting.iteye.com/blog/1726285 用POI读取Excel数据:(版本号:POI3.7) 1、读取Excel Java代码 private List<String[]> rosolveFile... int star... -
EasyExcel导出保留2位小数
2021-12-08 18:25:49这个注解的dataFormat就是设置格式的,他的值是BuiltinFormats类中BUILTIN_FORMATS_XX数组中对应格式的下标,源码如下: public class BuiltinFormats { private static final String[] BUILTIN_FORMATS_... -
Java XSSFCellStyle.getDataFormatString方法代碼示例
2021-02-26 09:03:14if (formatString == null) { formatString = BuiltinFormats.getBuiltinFormat(formatIndex); } if (formatString != null) { formattedValues.add(formatter.formatRawCellContents(Double.parseDouble(number), ... -
SAX方式实现Excel导入
2021-09-20 09:52:42SAX解析Excel1. 读取Excel公共方法2. 校验Excel文件格式3. 解析Excel的委托接口4. 2003版SAX解析5. 2007版SAX解析6. 测试SAX解析Excel6.1 测试代码6.2 测试结果7....上篇讲到了DOM解析Excel , 这篇记录工作中用到的SAX... -
POI Sax 事件驱动解析Excel
2019-09-12 16:03:28POI Sax 事件驱动解析Excel事件情景1.pom.xml引入依赖2.ExcelReaderUtil3.IExcelRowReader4.ExcelRowReader5.ExcelXlsReader6.ExcelXlsxReader 事件情景 找了不少资料,本文记录一下成果吧,主要使用POI Sax 事件... -
Java实现excel大数据量导入
2019-04-15 11:26:10import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.... -
Java 中如何解决 POI 读写 excel 几万行数据时内存溢出的问题?(附源码)
2021-03-17 00:55:38import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model....