POI生成Excel文件:Excel,工具类,背景色,边框,居中,合并单元格

背景今天分配到任务,要导出很多表格,懒得一个个写导出代码,故准备写个工具类

工具类代码如下:

  1 package com.swyx.tools.utils.poi;
  2 
  3 import java.io.File;
  4 import java.io.FileOutputStream;
  5 import java.io.OutputStream;
  6 import java.util.List;
  7 import java.util.Map;
  8 
  9 import org.apache.poi.hssf.usermodel.HSSFCell;
 10 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 11 import org.apache.poi.hssf.usermodel.HSSFPalette;
 12 import org.apache.poi.hssf.usermodel.HSSFRow;
 13 import org.apache.poi.hssf.usermodel.HSSFSheet;
 14 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 15 import org.apache.poi.ss.usermodel.BorderStyle;
 16 import org.apache.poi.ss.usermodel.FillPatternType;
 17 import org.apache.poi.ss.usermodel.HorizontalAlignment;
 18 import org.apache.poi.ss.util.CellRangeAddress;
 19 import org.apache.poi.ss.util.RegionUtil;
 20 
 21 /**
 22  * 
 23  * @author wangbaojun1992@163.com
 24  * @version poi version : 4.1.0
 25  */
 26 public class ExcelWriteUtil {
 27     
 28     /**
 29      * 
 30      * @param titleMape 标题行,为第一行标题内容与样式,参数Map<String,String>结构,字段如下:
 31      *         {
 32      *             value:内容值
 33      *             backgroundColor:背景色,为RGB颜色,3个色值以","隔开,默认"189,215,238"
 34      *         }
 35      * @param titleList 表头行,为第二行表头内容与样式,参数List<Map<String,String>>结构,字段如下:
 36      *         [
 37      *             {
 38      *                 value:内容值
 39      *                 backgroundColor:背景色,为RGB颜色,3个色值以","隔开,默认"189,215,238"
 40      *             }
 41      *         ]
 42      * @param contentList 内容,所有取值均被转换位String类型,参数List<List<String>>结构。
 43      * @param contentStyle 内容样式,参数为Map<String,String>结构,字段如下:
 44      *         {
 45      *             isZebra:内容区是否使用斑马线,枚举值:0是,1否,默认0
 46      *             zebraColor:斑马线颜色,为RGB颜色,3个色值以","隔开,默认"230,230,230"
 47      * @param dirName 缓存文件的文件夹的绝对路径
 48      * @param fileName 文件名,不要带后缀
 49      * @return
 50      * @throws Exception 
 51      */
 52     @SuppressWarnings("deprecation")
 53     public static String exportXlsExcel(Map<String, String> titleMape,List<Map<String, String>> titleList,List<List<String>> contentList,Map<String, String> contentStyle,String dirName,String fileName) throws Exception {
 54         //1.验证文件和文件夹名并创建Excel文件
 55         if(fileName == null || fileName.trim().equals("")) {
 56             throw new Exception("生成Excel文件异常:传入的文件名fileName不可为null、空字符串");
 57         }
 58         File parentDir = null;
 59         if(dirName == null || dirName.trim().equals("")) {
 60             throw new Exception("生成Excel文件异常:传入的文件夹名dirName不可为null、空字符串");
 61         }
 62         try {
 63             parentDir = new File(dirName);
 64             if(!parentDir.exists()) {
 65                 parentDir.mkdirs();
 66             }
 67         } catch (Exception e) {
 68             throw new Exception("生成Excel文件异常:传入的文件夹名dirName有误,dirName="+dirName);
 69         }finally {
 70             if(parentDir == null) {
 71                 throw new Exception("生成Excel文件异常:创建文件夹出错,dirName="+dirName);
 72             }
 73         }
 74         File excelFile = null;
 75         try {
 76             excelFile = new File(parentDir, fileName+".xls");
 77             if(excelFile.exists()) {
 78                 excelFile.delete();
 79             }
 80             excelFile.createNewFile();
 81         } catch (Exception e) {
 82             throw new Exception("生成Excel文件异常:生成File文件出错,fileName="+fileName);
 83         }finally {
 84             if(excelFile == null) {
 85                 throw new Exception("生成Excel文件异常:生成File文件出错,fileName="+fileName); 
 86             }
 87         }
 88         
 89         //2创建工作簿
 90         HSSFWorkbook wb=new HSSFWorkbook();
 91         HSSFSheet sheet=wb.createSheet();
 92         
 93         //3编辑标题
 94         //3.1标题样式
 95         HSSFCellStyle titleStyle=wb.createCellStyle();
 96         //3.1.1标题背景色
 97         HSSFPalette palette0_0 = wb.getCustomPalette();
 98         String backgroundColorStr0_0 = titleMape.get("backgroundColor");
 99         if(backgroundColorStr0_0 == null || backgroundColorStr0_0.trim().equals("")) {
100             backgroundColorStr0_0 = "189,215,238";
101         }
102         String[] backGroundColorStr0_0Strs = backgroundColorStr0_0.split(",");
103         if(backGroundColorStr0_0Strs.length != 3) {
104             backGroundColorStr0_0Strs = "189,215,238".split(",");
105         }
106         palette0_0.setColorAtIndex((short)9 ,(byte)new Integer(backGroundColorStr0_0Strs[0]).intValue(),(byte)(new Integer(backGroundColorStr0_0Strs[1]).intValue()),(byte)(new Integer(backGroundColorStr0_0Strs[2]).intValue()));
107         titleStyle.setFillForegroundColor((short)9 );
108         titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
109         //3.1.2标题合并单元格
110         // Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号 
111         CellRangeAddress rg0_0 = new CellRangeAddress(0,0,(short)0,(short)titleList.size()-1);
112         sheet.addMergedRegion(rg0_0);
113         //3.1.3标题边框
114         //TODO 此处合并单元格的边框样式没有生效,网络上一大堆复制黏贴的东西,试了很多未成功,头痛,暂时遗留该问题
115         //使用RegionUtil类为合并后的单元格添加边框
116         RegionUtil.setBorderBottom(BorderStyle.THIN, rg0_0, sheet); // 下边框
117         RegionUtil.setBorderLeft(BorderStyle.THIN, rg0_0, sheet); // 左边框
118         RegionUtil.setBorderRight(BorderStyle.THIN, rg0_0, sheet); // 有边框
119         RegionUtil.setBorderTop(BorderStyle.THIN, rg0_0, sheet); // 上边框
120         //3.1.4对齐
121         titleStyle.setAlignment(HorizontalAlignment.CENTER); //居中   
122        
123         //3.2写入标题
124         HSSFRow row0 = sheet.createRow(0);
125         HSSFCell cell0_0 = row0.createCell(0);
126         cell0_0.setCellValue(titleMape.get("value"));
127         cell0_0.setCellStyle(titleStyle);
128         
129         //4编辑表头
130         short fi = 11;
131         HSSFRow row1 = sheet.createRow(1);
132         for(int i = 0;i < titleList.size();i ++,fi++) {
133             Map<String, String> oneTitle = titleList.get(i);
134             //4.1当前列表头样式
135             HSSFCellStyle titleStyleI = wb.createCellStyle();
136             //4.1.1当前列表头背景色
137             HSSFPalette palette1_I = wb.getCustomPalette();
138             String backGroundColor1_I = oneTitle.get("backgroundColor");
139             if(backGroundColor1_I == null || backGroundColor1_I.trim().equals("")) {
140                 backGroundColor1_I = "189,215,238";
141             }
142             String[] backGroundColor1_IStrs = backGroundColor1_I.split(",");
143             if(backGroundColor1_IStrs.length != 3) {
144                 backGroundColor1_IStrs = "189,215,238".split(",");
145             }
146             palette1_I.setColorAtIndex(fi,(byte)(new Integer(backGroundColor1_IStrs[0]).intValue()),(byte)(new Integer(backGroundColor1_IStrs[1]).intValue()),(byte)(new Integer(backGroundColor1_IStrs[2]).intValue()));
147             titleStyleI.setFillPattern(FillPatternType.SOLID_FOREGROUND);
148             titleStyleI.setFillForegroundColor(fi);
149             //4.1.2当前列表头边框
150             titleStyleI.setBorderBottom(BorderStyle.THIN); //下边框
151             titleStyleI.setBorderLeft(BorderStyle.THIN);//左边框
152             titleStyleI.setBorderTop(BorderStyle.THIN);//上边框
153             titleStyleI.setBorderRight(BorderStyle.THIN);//右边框
154             //4.1.3对齐
155             titleStyleI.setAlignment(HorizontalAlignment.CENTER); //居中   
156             //4.2写入当前列表头
157             HSSFCell cell1_I = row1.createCell(i);
158             cell1_I.setCellValue(oneTitle.get("value"));
159             cell1_I.setCellStyle(titleStyleI);
160         }
161         
162         //5编辑内容区
163         //5.1准备样式
164         String isZebraStr = contentStyle.get("isZebra");
165         boolean isZebra = true;
166         if(isZebraStr != null && isZebraStr.equals("1")) {
167             isZebra = false;
168         }
169         //5.1.1斑马线行样式
170         HSSFCellStyle style1 = wb.createCellStyle();
171         //背景色
172         HSSFPalette paletteC = wb.getCustomPalette();
173         String backGroundColorC = contentStyle.get("zebraColor");
174         if(backGroundColorC == null || backGroundColorC.trim().equals("")) {
175             backGroundColorC = "230,230,230";
176         }
177         String[] backGroundColorCStrs = backGroundColorC.split(",");
178         if(backGroundColorCStrs.length != 3) {
179             backGroundColorCStrs = "230,230,230".split(",");
180         }
181         paletteC.setColorAtIndex((short)10 ,  (byte)(new Integer(backGroundColorCStrs[0]).intValue()),(byte)(new Integer(backGroundColorCStrs[1]).intValue()),(byte)(new Integer(backGroundColorCStrs[2]).intValue()));
182         style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
183         style1.setFillForegroundColor((short)10);
184         //边框
185         style1.setBorderBottom(BorderStyle.THIN); //下边框
186         style1.setBorderLeft(BorderStyle.THIN);//左边框
187         style1.setBorderTop(BorderStyle.THIN);//上边框
188         style1.setBorderRight(BorderStyle.THIN);//右边框
189         //5.1.2非斑马线行样式
190         HSSFCellStyle style0 = wb.createCellStyle();
191         //背景色
192         style0.setFillPattern(FillPatternType.SOLID_FOREGROUND);
193         //边框
194         style0.setBorderBottom(BorderStyle.THIN); //下边框
195         style0.setBorderLeft(BorderStyle.THIN);//左边框
196         style0.setBorderTop(BorderStyle.THIN);//上边框
197         style0.setBorderRight(BorderStyle.THIN);//右边框
198         
199         //5.2写入内容
200         for(int i = 0;i < contentList.size();i ++) {
201             List<String> contents = contentList.get(i);
202             HSSFRow rowI = sheet.createRow(i+2);
203             for(int j = 0;j < contents.size();j ++) {
204                 HSSFCell cellJ = rowI.createCell(j);
205                 cellJ.setCellValue(contents.get(j));
206                 if(i % 2 == 1) {
207                     if(isZebra) {
208                         cellJ.setCellStyle(style1);
209                     }else {
210                         cellJ.setCellStyle(style0);
211                     }
212                 }else {
213                     cellJ.setCellStyle(style0);
214                 }
215             }
216         }
217 
218         //6将文件输出
219         OutputStream ouputStream = null;
220         try {
221              ouputStream = new FileOutputStream(excelFile);
222              wb.write(ouputStream);
223              ouputStream.flush();
224              wb.close();
225         } catch (Exception e) {
226             throw new Exception("生成Excel文件异常:写出Excel文件异常"); 
227         }finally {
228             try {
229                 if(ouputStream != null) {
230                     ouputStream.close();
231                 }
232             } catch (Exception e2) {
233             }
234         }
235         
236         return excelFile.getAbsolutePath();
237     }
238 }

工具类调用:

 1 package com.swyx.tools.utils.poi;
 2 
 3 import java.util.ArrayList;
 4 import java.util.HashMap;
 5 import java.util.List;
 6 import java.util.Map;
 7 
 8 public class ExcelWriteUtilTest {
 9     public static void main(String[] args) throws Exception {
10         exportXlsExcel_Test();
11     }
12 
13     private static void exportXlsExcel_Test() throws Exception {
14         Map<String, String> titleMape = new HashMap<String, String>();
15         titleMape.put("value", "生成Excel文件测试");
16         
17         List<Map<String, String>> titleList = new ArrayList<Map<String,String>>();
18         Map<String, String> tm1 = new HashMap<String, String>();
19         tm1.put("value", "第1列");
20         titleList.add(tm1);
21         Map<String, String> tm2 = new HashMap<String, String>();
22         tm2.put("value", "第2列");
23         titleList.add(tm2);
24         Map<String, String> tm3 = new HashMap<String, String>();
25         tm3.put("value", "第3列");
26         titleList.add(tm3);
27         Map<String, String> tm4 = new HashMap<String, String>();
28         tm4.put("value", "第4列");
29         titleList.add(tm4);
30         Map<String, String> tm5 = new HashMap<String, String>();
31         tm5.put("value", "第5列");
32         titleList.add(tm5);
33         Map<String, String> tm6 = new HashMap<String, String>();
34         tm6.put("value", "第6列");
35         titleList.add(tm6);
36         
37         List<List<String>> contentList = new ArrayList<List<String>>();
38         List<String> cl1 = new ArrayList<String>();
39         cl1.add("111111");cl1.add("111111");cl1.add("111111");cl1.add("111111");cl1.add("111111");cl1.add("111111");
40         contentList.add(cl1);
41         
42         List<String> cl2 = new ArrayList<String>();
43         cl2.add("222222");cl2.add("222222");cl2.add("222222");cl2.add("222222");cl2.add("222222");cl2.add("222222");
44         contentList.add(cl2);
45         
46         List<String> cl3 = new ArrayList<String>();
47         cl3.add("333333");cl3.add("333333");cl3.add("333333");cl3.add("333333");cl3.add("333333");cl3.add("333333");
48         contentList.add(cl3);
49         
50         List<String> cl4 = new ArrayList<String>();
51         cl4.add("444444");cl4.add("444444");cl4.add("444444");cl4.add("444444");cl4.add("444444");cl4.add("444444");
52         contentList.add(cl4);
53         
54         List<String> cl5 = new ArrayList<String>();
55         cl5.add("555555");cl5.add("555555");cl5.add("555555");cl5.add("555555");cl5.add("555555");cl5.add("555555");
56         contentList.add(cl5);
57         
58         List<String> cl6 = new ArrayList<String>();
59         cl6.add("666666");cl6.add("666666");cl6.add("666666");cl6.add("666666");cl6.add("666666");cl6.add("666666");
60         contentList.add(cl6);
61         
62         List<String> cl7 = new ArrayList<String>();
63         cl7.add("777777");cl7.add("777777");cl7.add("777777");cl7.add("777777");cl7.add("777777");cl7.add("777777");
64         contentList.add(cl7);
65         
66         List<String> cl8 = new ArrayList<String>();
67         cl8.add("888888");cl8.add("888888");cl8.add("888888");cl8.add("888888");cl8.add("888888");cl8.add("888888");
68         contentList.add(cl8);
69         
70         List<String> cl9 = new ArrayList<String>();
71         cl9.add("999999");cl9.add("999999");cl9.add("999999");cl9.add("999999");cl9.add("999999");cl9.add("999999");
72         contentList.add(cl9);
73         
74         Map<String, String> contentStyle = new HashMap<String, String>();
75         String dirName = "C:\\WorkSpaces\\dxc";
76         String fileName = "ExcelWriteUtil_exportXlsExcel_Test";
77         
78         String name = ExcelWriteUtil.exportXlsExcel(titleMape, titleList, contentList, contentStyle, dirName, fileName);
79         System.out.println(name);
80     }
81 }

生成Excel:

POI生成Excel文件:Excel,工具类,背景色,边框,居中,合并单元格

 

上一篇:「GNOME 3」- 修改 Topbar 字体(顶部栏字体)、调整默认主题 @20210211


下一篇:Llinux下安装salt-minion