十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
这篇文章主要介绍“Java怎么对Excel进行操作”,在日常操作中,相信很多人在Java怎么对Excel进行操作问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”Java怎么对Excel进行操作”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
创新互联是专业的万荣网站建设公司,万荣接单;提供成都网站建设、成都做网站,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行万荣网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
想必大家在平常的项目开发过程中经常会涉及到Excel文件的导出功能,一般都会选择Apache poi组件来完成,但是通常需要写大量代码,无疑增加了复杂度,且存在一个严重的问题就是非常的耗内存,容易导致OOM,幸运的是阿里开源了easyexcel,它是基于Apache poi而开发的一个组件,easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别,并且再大的excel不会出现内存溢出,但在实际使用过程中发现easyexcel操作具体每个cell还不是很便捷,所以我重写了一个组件easyexcel-util,本项目基于阿里easyexcel,在此基础上做了更进一步的封装,使其写入数据更加便捷,通过抽离出的ExcelDataHandler接口更容易处理每个cell的字体与样式,下面将介绍如何使用easyexcel-util。
com.github.aifeinik easyexcel-util 1.0
public class ExcelTest { CampaignModel m1 = new CampaignModel("2019-01-01", "10000000", "campaign1", "12.21", "100", "0.11"); CampaignModel m2 = new CampaignModel("2019-01-02", "12000010", "campaign2", "13", "99", "0.91"); CampaignModel m3 = new CampaignModel("2019-01-03", "12001010", "campaign3", "10", "210", "1.13"); CampaignModel m4 = new CampaignModel("2019-01-04", "15005010", "campaign4", "21.9", "150", "0.15"); ArrayListdata1 = Lists.newArrayList(m1, m2); ArrayList data2 = Lists.newArrayList(m3, m4); @Test public void writeExcelWithOneSheet() throws Exception { ExcelUtil.writeExcelWithOneSheet(new File("G:/tmp/campaign.xlsx"), "campaign", data1); } }
写入效果如下:
@Test public void writeExcelWithOneSheet2() throws Exception { ExcelUtil.writeExcelWithOneSheet(new File("G:/tmp/campaign.xlsx"), "campaign", data1, new CampaignDataHandler()); }
写入效果如下:
@Test public void writeExcelWithMultiSheet() throws Exception { Map> map = new HashMap<>(); map.put("sheet1", data1); map.put("sheet2", data2); ExcelUtil.writeExcelWithMultiSheet(new File("G:/tmp/campaign.xlsx"), map); }
写入效果如下:
@Test public void writeExcelWithMultiSheet2() throws Exception { Map> map = new HashMap<>(); map.put("sheet1", data1); map.put("sheet2", data2); ExcelUtil.writeExcelWithMultiSheet(new File("G:/tmp/campaign.xlsx"), map, new CampaignDataHandler()); }
写入效果如下:
@Test public void writeOneSheetWithWrapWriter() { ExcelWrapWriter wrapWriter = null; try { OutputStream os = new FileOutputStream("G:/tmp/campaign.xlsx"); //默认样式 //wrapWriter = new ExcelWrapWriter(os, ExcelTypeEnum.XLSX); //自定义excel样式 wrapWriter = new ExcelWrapWriter(os, ExcelTypeEnum.XLSX, new CampaignDataHandler()); Listmodels1 = Lists.newArrayList(m1, m2); List models2 = Lists.newArrayList(m3, m4); //第一批次写入设置包含head头 ExcelUtil.writeExcelWithOneSheet(wrapWriter, "sheet1", true, models1); //第二批次开始不需要在写入head头 ExcelUtil.writeExcelWithOneSheet(wrapWriter, "sheet1", false, models2); } catch (Exception e) { e.printStackTrace(); } finally { //close IO if (wrapWriter != null) { wrapWriter.finish(); } } }
数据分批写入excel文件,可通过该方式写入超大数据,而不至于一次写入大数据量导致OOM问题
2.2.6 大数据量分批写入多个sheet
@Test public void writeMultiSheetWithWrapWriter() { ExcelWrapWriter wrapWriter = null; try { //os流不需要单独close,可通过wrapWriter.finish()来关闭 OutputStream os = new FileOutputStream("G:/tmp/campaign.xlsx"); //默认样式 //wrapWriter = new ExcelWrapWriter(os, ExcelTypeEnum.XLSX); //自定义excel样式 wrapWriter = new ExcelWrapWriter(os, ExcelTypeEnum.XLSX, new CampaignDataHandler()); Map> batch2 = new HashMap<>(); List models1 = Lists.newArrayList(m1, m2); List models2 = Lists.newArrayList(m3, m4); batch2.put("sheet1", models1); batch2.put("sheet2", models2); Map > batch3 = new HashMap<>(); List models3 = Lists.newArrayList(m4, m2); List models4 = Lists.newArrayList(m3, m1); batch3.put("sheet1", models3); batch3.put("sheet2", models4); //第一批次写入设置包含head头 ExcelUtil.writeExcelWithMultiSheet(wrapWriter, true, batch2); //第二批次开始不需要在写入head头 ExcelUtil.writeExcelWithMultiSheet(wrapWriter, false, batch3); } catch (Exception e) { e.printStackTrace(); } finally { //close IO if (wrapWriter != null) { wrapWriter.finish(); } } }
通过该注解更加方便的处理每个数据的具体格式, 内部采用MessageFormat.format进行数据格式化,如下代码,其中cost花费字段注解了@ExcelValueFormat(format = "{0}$") 那么如果cost = 100 则写入Excel后内容为100$
@Data public class CampaignModel extends BaseRowModel implements Serializable { @ExcelProperty(value = "日期", index = 0) private String day; @ExcelProperty(value = "广告系列 ID", index = 1) private String campaignId; @ExcelProperty(value = "广告系列", index = 2) private String campaignName; @ExcelProperty(value = "费用", index = 3) @ExcelValueFormat(format = "{0}$") private String cost; @ExcelProperty(value = "点击次数", index = 4) private String clicks; @ExcelProperty(value = "点击率", index = 5) @ExcelValueFormat(format = "{0}%") private String ctr; }
public interface ExcelDataHandler { /** * Excel head头部字体设置 * @param font * @param cellIndex 列索引 */ void headFont(Font font, int cellIndex); /** * Excel head头部样式设置 * @param style * @param cellIndex 列索引 */ void headCellStyle(CellStyle style, int cellIndex); /** * Excel 除head外的内容字体设置 * @param font * @param cellIndex 列索引 */ void contentFont(Font font, int cellIndex, Object data); /** * Excel 除head外的内容样式设置 * @param style * @param cellIndex 列索引 */ void contentCellStyle(CellStyle style, int cellIndex); /** * Excel sheet * @param sheetIndex sheet索引 * @param sheet */ void sheet(int sheetIndex, Sheet sheet); }
列如实现类如下:
public class CampaignDataHandler implements ExcelDataHandler { @Override public void headCellStyle(CellStyle style, int cellIndex) { style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); } @Override public void headFont(Font font, int cellIndex) { font.setColor(IndexedColors.WHITE.getIndex()); } @Override public void contentCellStyle(CellStyle style, int cellIndex) { style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); } @Override public void contentFont(Font font, int cellIndex, Object data) { CampaignModel campaign = (CampaignModel) data; switch (cellIndex) { case 4: //这里的值为Model对象中ExcelProperty注解里的index值 if (Long.valueOf(campaign.getClicks()) > 100) { //表示将点击次数大于100的第4列也就是点击次数列的cell字体标记为红色 font.setColor(IndexedColors.RED.getIndex()); font.setFontName("宋体"); font.setItalic(true); font.setBold(true); } break; } } @Override public void sheet(int sheetIndex, Sheet sheet) { System.out.println("sheetIndex = [" + sheetIndex + "]"); } }
本文主要介绍了easyexcel-util组件不同场景的使用方式、通过ExcelValueFormat注解可以方便的处理数据的具体格式,以及通过ExcelDataHandler 接口来灵活设置具体每个cell的样式与字体。
到此,关于“Java怎么对Excel进行操作”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!