java poi 使用写

之前项目写过一个导出Execl表格的util，现在想再用发现脑子不知道哪抽了，找不到了，所以开始用博客来记录

现在想来，还是应该早点用起来

jar包：poi-3.14版本

poi-3.14.jar
poi-examples-3.14.jar
poi-excelant-3.14.jar
poi-ooxml-3.14.jar
poi-ooxml-schemas-3.14.jar
poi-scratchpad-3.14.jar

实际代码：

读取，检验文件

public class ExcelUtils {
    
    private static final String EXCEL_XLS="xls";
    
    private static final String EXCEL_XLSX="xlsx";
    
    /** 
     * 判断Excel的版本,获取Workbook 
     * @param file 
     * @return Workbook
     * @throws IOException 
     */ 
    public Workbook getWorkbook(File file) throws IOException {
        
        checkExcelVaild(file);
        
        Workbook wb=null;
        FileInputStream fis = new FileInputStream(file);
        if(file.getName().endsWith(EXCEL_XLS)) {
            wb=new HSSFWorkbook(fis);
        }else if(file.getName().endsWith(EXCEL_XLSX)) {
            wb=new XSSFWorkbook(fis);
        }
        return wb;
    }
    
    /** 
     * 判断Excel的版本,获取Workbook 
     * @param url
     * @return Workbook
     * @throws IOException 
     */ 
    public Workbook getWorkbook(String url) throws IOException {
        File file = new File(url);
        
        checkExcelVaild(file);
        
        Workbook wb=null;
        FileInputStream fis = new FileInputStream(file);
        if(url.endsWith(EXCEL_XLS)) {
            wb=new HSSFWorkbook(fis);
        }else if(url.endsWith(EXCEL_XLSX)) {
            wb=new XSSFWorkbook(fis);
        }
        return wb;
    }
    
    /**
     * 
     * @Title: checkExcelVaild
     * @Description: 检查文件有效性
     *  @param file
     *  @throws IllegalArgumentException    非法参数
     *  void    
     *  2019年4月9日 下午2:04:11
     * @throws
     */
    public void checkExcelVaild(File file) throws IllegalArgumentException {
        if(!file.exists()) {
            throw new IllegalArgumentException("文件不存在");
        }
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new IllegalArgumentException("文件不是Excel");
        }
    }
    
}

此处有坑，

缺少 jar包：commons-collections4-4.1.jar

缺少 jar包：xmlbeans-2.6.0.jar

缺少 jar包：commons-compress.jar

写入Excel表格：

  1 public class ExcelWriteUtil extends ExcelUtils {
  2     
  3     public <T> Workbook writeDataToExcel(T t,Workbook wb,Map<String,String> map) throws Exception {
  4         if(map == null) {
  5             throw new IllegalArgumentException("请指定 写入字段");
  6         }
  7         
  8         Sheet sheet = creatOrGetSheet(wb, getSheetName(t, map));
  9         
 10         if(sheet.getLastRowNum() == 0) {
 11             Row row = createLastRow(sheet,true);
 12             writeCell(row, null, map, true);
 13         }
 14         
 15         Row title = sheet.getRow(0);
 16         Row row = createLastRow(sheet,false);
 17         Map<String, Object> matchData = matchData(objToMap(t), map);
 18         writeCell(title, row, matchData, false);
 19         
 20         return wb;
 21     }
 22     
 23     public <T> Workbook writeDataToExcel(T t,String url,Map<String,String> map) throws IOException {
 24         Workbook wb = getWorkbook(url);
 25         if(map == null) {
 26             throw new IllegalArgumentException("请指定 写入字段");
 27         }
 28         
 29         Sheet sheet = creatOrGetSheet(wb, getSheetName(t, map));
 30         
 31         if(sheet.getLastRowNum() == 0) {
 32             Row row = createLastRow(sheet,true);
 33             writeCell(row, null, map, true);
 34         }
 35         
 36         Row title = sheet.getRow(0);
 37         Row row = createLastRow(sheet,false);
 38         Map<String, Object> matchData = matchData(objToMap(t), map);
 39         writeCell(title, row, matchData, false);
 40         
 41         return wb;
 42     }
 43     
 44     public <T> Workbook writeDataToExcel(T t,File file,Map<String,String> map) throws IOException {
 45         Workbook wb = getWorkbook(file);
 46         if(map == null) {
 47             throw new IllegalArgumentException("请指定 写入字段");
 48         }
 49         
 50         Sheet sheet = creatOrGetSheet(wb, getSheetName(t, map));
 51         
 52         if(sheet.getLastRowNum() == 0) {
 53             Row row = createLastRow(sheet,true);
 54             writeCell(row, null, map, true);
 55         }
 56         
 57         Row title = sheet.getRow(0);
 58         Row row = createLastRow(sheet,false);
 59         Map<String, Object> matchData = matchData(objToMap(t), map);
 60         writeCell(title, row, matchData, false);
 61         
 62         return wb;
 63     }
 64     
 65     public <T> Workbook writeDataToExcel(List<T> list,Workbook wb,Map<String,String> map) {
 66         if(map == null) {
 67             throw new IllegalArgumentException("请指定 写入字段");
 68         }
 69         if(list == null || list.isEmpty()) {
 70             throw new NullPointerException("list is empty");
 71         }
 72         Sheet sheet = creatOrGetSheet(wb, getSheetName(list.get(0), map));
 73         
 74         if(sheet.getLastRowNum() == 0) {
 75             Row row = createLastRow(sheet,true);
 76             writeCell(row, null, map, true);
 77         }
 78         for (T t : list) {
 79             Row title = sheet.getRow(0);
 80             Row row = createLastRow(sheet,false);
 81             Map<String, Object> matchData = matchData(objToMap(t), map);
 82             writeCell(title, row, matchData, false);
 83         }
 84         
 85         return wb;
 86     }
 87     
 88     public <T> Workbook writeDataToExcel(List<T> list,String url,Map<String,String> map) throws IOException {
 89         Workbook wb = getWorkbook(url);
 90         if(map == null) {
 91             throw new IllegalArgumentException("请指定 写入字段");
 92         }
 93         if(list == null || list.isEmpty()) {
 94             throw new NullPointerException("list is empty");
 95         }
 96         Sheet sheet = creatOrGetSheet(wb, getSheetName(list.get(0), map));
 97         
 98         if(sheet.getLastRowNum() == 0) {
 99             Row row = createLastRow(sheet,true);
100             writeCell(row, null, map, true);
101         }
102         for (T t : list) {
103             Row title = sheet.getRow(0);
104             Row row = createLastRow(sheet,false);
105             Map<String, Object> matchData = matchData(objToMap(t), map);
106             writeCell(title, row, matchData, false);
107         }
108         
109         return wb;
110     }
111     
112     public <T> Workbook writeDataToExcel(List<T> list,File file,Map<String,String> map) throws IOException {
113         Workbook wb = getWorkbook(file);
114         if(map == null) {
115             throw new IllegalArgumentException("请指定 写入字段");
116         }
117         if(list == null || list.isEmpty()) {
118             throw new NullPointerException("list is empty");
119         }
120         Sheet sheet = creatOrGetSheet(wb, getSheetName(list.get(0), map));
121         
122         if(sheet.getLastRowNum() == 0) {
123             Row row = createLastRow(sheet,true);
124             writeCell(row, null, map, true);
125         }
126         for (T t : list) {
127             Row title = sheet.getRow(0);
128             Row row = createLastRow(sheet,false);
129             Map<String, Object> matchData = matchData(objToMap(t), map);
130             writeCell(title, row, matchData, false);
131         }
132         
133         return wb;
134     }
135     
136     public <T> Workbook writeDataToExcel(Set<T> set,Workbook wb,Map<String,String> map) {
137         if(map == null) {
138             throw new IllegalArgumentException("请指定 写入字段");
139         }
140         if(set == null || set.isEmpty()) {
141             throw new NullPointerException("data is empty");
142         }
143         List<T> list = new ArrayList<>(set);
144         Sheet sheet = creatOrGetSheet(wb, getSheetName(list.get(0), map));
145         
146         for (T t : list) {
147             if(sheet.getLastRowNum() == 0) {
148                 Row row = createLastRow(sheet,true);
149                 writeCell(row, null, map, true);
150             }
151             
152             Row title = sheet.getRow(0);
153             Row row = createLastRow(sheet,false);
154             Map<String, Object> matchData = matchData(objToMap(t), map);
155             writeCell(title, row, matchData, false);
156         }
157         
158         return wb;
159     }
160     
161     public <T> Workbook writeDataToExcel(Set<T> set,String url,Map<String,String> map) throws IOException {
162         Workbook wb = getWorkbook(url);
163         if(map == null) {
164             throw new IllegalArgumentException("请指定 写入字段");
165         }
166         if(set == null || set.isEmpty()) {
167             throw new NullPointerException("data is empty");
168         }
169         List<T> list = new ArrayList<>(set);
170         Sheet sheet = creatOrGetSheet(wb, getSheetName(list.get(0), map));
171         
172         for (T t : list) {
173             if(sheet.getLastRowNum() == 0) {
174                 Row row = createLastRow(sheet,true);
175                 writeCell(row, null, map, true);
176             }
177             
178             Row title = sheet.getRow(0);
179             Row row = createLastRow(sheet,false);
180             Map<String, Object> matchData = matchData(objToMap(t), map);
181             writeCell(title, row, matchData, false);
182         }
183         
184         return wb;
185     }
186     
187     
188     public <T> Workbook writeDataToExcel(Set<T> set,File file,Map<String,String> map) throws IOException {
189         Workbook wb = getWorkbook(file);
190         if(map == null) {
191             throw new IllegalArgumentException("请指定 写入字段");
192         }
193         if(set == null || set.isEmpty()) {
194             throw new NullPointerException("data is empty");
195         }
196         List<T> list = new ArrayList<>(set);
197         Sheet sheet = creatOrGetSheet(wb, getSheetName(list.get(0), map));
198         
199         for (T t : list) {
200             if(sheet.getLastRowNum() == 0) {
201                 Row row = createLastRow(sheet,true);
202                 writeCell(row, null, map, true);
203             }
204             
205             Row title = sheet.getRow(0);
206             Row row = createLastRow(sheet,false);
207             Map<String, Object> matchData = matchData(objToMap(t), map);
208             writeCell(title, row, matchData, false);
209         }
210         
211         return wb;
212     }
213     
214     /**
215      * 
216      * @Title: creatOrGetSheet
217      * @Description: 创建 sheet工作页 如果有工作页了 获取sheet工作页
218      *  @param wb
219      *  @param sheetName
220      *  @return    设定文件
221      *  Sheet    返回类型
222      *  2019年4月9日 下午11:01:26
223      * @throws
224      */
225     public Sheet creatOrGetSheet(Workbook wb,String sheetName) {
226         try {
227             return wb.createSheet(sheetName);//创建工作页
228         } catch (Exception e) {
229             return wb.getSheet(sheetName);
230         }
231     }
232     
233     /**
234      * 
235      * @Title: createRow
236      * @Description: 跟着最后一行 创建行
237      *  @param sheet
238      *  @return    设定文件
239      *  Row    返回类型
240      *  2019年4月9日 下午11:39:35
241      * @throws
242      */
243     public Row createLastRow(Sheet sheet,boolean isTitle) {
244         if (isTitle) {
245             return sheet.createRow(0);
246         }
247         int lastRowNum = sheet.getLastRowNum();//最后一行行标  从0开始
248         return sheet.createRow(lastRowNum+1);
249     }
250     
251     /**
252      * 
253      * @Title: getSheetName
254      * @Description: TODO(这里用一句话描述这个方法的作用)
255      *  @param t
256      *  @param map
257      *  @return    设定文件
258      *  String    返回类型
259      *  2019年4月10日 上午12:19:49
260      * @throws
261      */
262     public <T> String getSheetName(T t,Map<String,String> map) {
263         String sheetName = map.get(t.getClass().getSimpleName());
264         if(sheetName != null) {
265             map.remove(t.getClass().getSimpleName());
266             return sheetName;
267         }
268         return t.getClass().getSimpleName();
269     }
270     
271     /**
272      * 
273      * @Title: matchData
274      * @Description:准备数据
275      *  @param data
276      *  @param titles
277      *  @return    设定文件
278      *  Map<String,Object>    返回类型
279      *  2019年4月9日 下午10:57:03
280      * @throws
281      */
282     public Map<String,Object> matchData(Map<String,Object> data,Map<String,String> titles){
283         if(data == null) {
284             throw new NullPointerException("data is empty");
285         }
286         if(titles == null) {
287             return data;
288         }
289         Map<String,Object> result = new HashMap<>();
290         
291         for (Entry<String, Object> kv : data.entrySet()) {
292             String title = titles.get(kv.getKey());//获取 标题
293             if(title == null) {
294                 continue;
295             }
296             result.put(title, kv.getValue());//{"title":"value"}
297         }
298         return result;
299     }
300     
301     
302     /**
303      * 
304      * @Title: writeCell
305      * @Description: 将数据添加到对应标题下
306      *  @param title  标题行
307      *  @param row          数据行
308      *  @param map    准备添加的数据
309      *  void   
310      *  2019年4月9日 下午5:28:21
311      * @throws
312      */
313     public <T> void writeCell(Row title,Row row,Map<String,T> map,boolean isTitle) {
314         if(isTitle) {
315             List<String> key = new ArrayList<>(map.keySet());
316             for (int i = 0; i < key.size(); i++) {
317                 Cell cell = title.createCell(i);
318                 cell.setCellValue(map.get(key.get(i)).toString());
319             }
320             return ;
321         }
322         for (int i = 0; i < title.getLastCellNum(); i++) {
323             Object value = map.get(title.getCell(i).getStringCellValue());//数据与标题匹配
324              
325             if(value==null) {
326                 continue;
327             }
328             
329             Cell cell = row.createCell(i);
330             cell.setCellValue(value+"");
331         }
332     }    
333 
334 }

好了，下次再写读取Excel内容

java poi 使用 写

java poi 使用写