apache poi 读取xlsx并导出为json(没考虑xls)

时间:2022-09-17 20:25:16

1、用到的jar包:fastjson-1.2.9、poi(poi-3.15、poi-ooxml-3.15、poi-ooxml-schemas-3.15、xmlbeans-2.6.0、commons-collections4-4.1)

很简单,直接上代码:

2、导出类,两个WrapAll类字符串数组都是excel文件名,如item.xlsx,写死的读取sheet 第 0 页

ParseJson方法导出为json,list是行,Map key-value:字段名-值

  1 package com.ojcgame.warp;
  2 
  3 import java.io.FileInputStream;
  4 import java.io.FileWriter;
  5 import java.io.IOException;
  6 import java.io.InputStream;
  7 import java.lang.reflect.InvocationTargetException;
  8 import java.util.ArrayList;
  9 import java.util.HashMap;
 10 import java.util.List;
 11 import java.util.Map;
 12 
 13 import org.apache.poi.hssf.usermodel.HSSFCell;
 14 import org.apache.poi.xssf.usermodel.XSSFCell;
 15 import org.apache.poi.xssf.usermodel.XSSFRow;
 16 import org.apache.poi.xssf.usermodel.XSSFSheet;
 17 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 18 import org.eclipse.core.runtime.IProgressMonitor;
 19 import org.eclipse.jface.dialogs.ProgressMonitorDialog;
 20 import org.eclipse.jface.operation.IRunnableWithProgress;
 21 
 22 import com.alibaba.fastjson.JSON;
 23 import com.ojcgame.common.EnvironmentManager;
 24 import com.ojcgame.common.OJCUtils;
 25 
 26 public class WarpDataManager {
 27     String[] filesArr;
 28 
 29     public void WarpAll(String[] files) {
 30         filesArr = files;
 31         ProgressMonitorDialog progress = new ProgressMonitorDialog(null);
 32         IRunnableWithProgress progressTask = new IRunnableWithProgress() {
 33             @Override
 34             public void run(IProgressMonitor monitor)
 35                     throws InvocationTargetException, InterruptedException {
 36                 monitor.beginTask("正在导出数据", IProgressMonitor.UNKNOWN);
 37                 WarpAll(filesArr, monitor);
 38             }
 39         };
 40 
 41         try {
 42             progress.run(true, false, progressTask);
 43         } catch (InvocationTargetException e) {
 44             e.printStackTrace();
 45         } catch (InterruptedException e) {
 46             e.printStackTrace();
 47         } finally {
 48             filesArr = null;
 49         }
 50     }
 51 
 52     @SuppressWarnings("deprecation")
 53     private void WarpAll(String[] files, IProgressMonitor monitor) {
 54         InputStream is = null;
 55         XSSFWorkbook xssfWorkbook = null;
 56         List<String> titles = null;
 57         Map<String, Object> oneCellData = null;
 58         List<Map<String, Object>> AllDataList = null;
 59         int fileIndex = 0;
 60         try {
 61             for (int f = 0, fLength = files.length; f < fLength; ++f) {
 62                 fileIndex = f;
 63                 // System.out.println("正在尝试导出:" + files[f]);
 64                 monitor.subTask("尝试导出:" + files[f]);
 65                 is = new FileInputStream(EnvironmentManager.getInstance()
 66                         .getDataSourcesFloderPath() + "\\" + files[f]);
 67                 xssfWorkbook = new XSSFWorkbook(is);
 68                 // 读取sheet1
 69                 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
 70                 if (xssfSheet == null)
 71                     continue;
 72 
 73                 titles = new ArrayList<String>();
 74                 AllDataList = new ArrayList<Map<String, Object>>();
 75                 // 先读取字段
 76                 XSSFRow titleRow = xssfSheet.getRow(0);
 77                 for (int rowIndex = 0, mLength = titleRow.getLastCellNum() + 1; rowIndex < mLength; ++rowIndex) {
 78                     if (null == titleRow.getCell(rowIndex)
 79                             || titleRow.getCell(rowIndex).getCellType() == HSSFCell.CELL_TYPE_BLANK) {
 80                         break;
 81                     } else {
 82                         try {
 83                             // System.out.println(titles.get(cellNum) + "---"
 84                             // + xssfCell.getStringCellValue());
 85                             titles.add(titleRow.getCell(rowIndex)
 86                                     .getStringCellValue());
 87                         } catch (IllegalStateException e) {
 88                             // System.out.println("rowIndex number:" + rowIndex
 89                             // + " ---- " + files[f]);
 90                             // System.out.println(titles.get(cellNum) + "---"
 91                             // + xssfCell.getNumericCellValue());
 92                             titles.add(titleRow.getCell(rowIndex)
 93                                     .getNumericCellValue() + "");
 94                         }
 95                     }
 96                 }
 97                 // System.out.println(xssfSheet
 98                 // .getLastRowNum() + 1);
 99                 // 读取行
100                 for (int rowNum = 2, rLength = xssfSheet.getLastRowNum() + 1; rowNum < rLength; ++rowNum) {
101                     XSSFRow xssfRow = xssfSheet.getRow(rowNum);
102                     if (xssfRow == null) {
103                         continue;
104                     }
105                     oneCellData = new HashMap<String, Object>();
106                     // 读取列
107                     for (int cellNum = 0; cellNum < titles.size(); ++cellNum) {
108                         XSSFCell xssfCell = xssfRow.getCell(cellNum);
109                         if (null == xssfCell)
110                             continue;
111 
112                         if (xssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
113                             // System.out.println(titles.get(cellNum) + "---"
114                             // + xssfCell.getNumericCellValue());
115                             oneCellData.put(titles.get(cellNum),
116                                     xssfCell.getNumericCellValue());
117                         } else if (xssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
118                             // System.out.println(titles.get(cellNum) + "---"
119                             // + xssfCell.getStringCellValue());
120                             oneCellData.put(titles.get(cellNum),
121                                     xssfCell.getStringCellValue());
122                         } else if (xssfCell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
123                             // System.out.println(cellNum + "--- kong=======" +
124                             // rowNum);
125                             // System.out
126                             // .println(titles.get(cellNum) + "--- kong");
127                             oneCellData.put(titles.get(cellNum), "");
128                         } else if (xssfCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
129                             try {
130                                 // System.out.println(titles.get(cellNum) +
131                                 // "---"
132                                 // + xssfCell.getStringCellValue());
133                                 oneCellData.put(titles.get(cellNum),
134                                         xssfCell.getStringCellValue());
135                             } catch (IllegalStateException e) {
136                                 // System.out.println(titles.get(cellNum) +
137                                 // "---"
138                                 // + xssfCell.getNumericCellValue());
139                                 oneCellData.put(titles.get(cellNum),
140                                         xssfCell.getNumericCellValue());
141                             }
142                         }
143                     }
144 
145                     AllDataList.add(oneCellData);
146                 }
147 
148                 if (null != xssfWorkbook)
149                     xssfWorkbook.close();
150                 if (null != is)
151                     is.close();
152 
153                 ParseJson(AllDataList, OJCUtils.GetFileName(files[f], ".xlsx"));
154 
155                 monitor.worked(f + 1);
156             }
157 
158         } catch (Exception e) {
159             e.printStackTrace();
160             OJCUtils.ShowDialog("导出失败:" + files[fileIndex]);
161         } finally {
162             monitor.done();
163             try {
164                 if (null != xssfWorkbook)
165                     xssfWorkbook.close();
166             } catch (IOException e) {
167                 e.printStackTrace();
168                 OJCUtils.ShowDialog("导出失败:" + files[fileIndex]);
169             } finally {
170                 try {
171                     if (null != is)
172                         is.close();
173                 } catch (Exception e) {
174                     e.printStackTrace();
175                     OJCUtils.ShowDialog("导出失败:" + files[fileIndex]);
176                 }
177             }
178         }
179     }
180 
181     private void ParseJson(List<Map<String, Object>> pContents, String pFileName) {
182         String jsonStr = JSON.toJSONString(pContents, true);
183         if (null == jsonStr || jsonStr.isEmpty()) {
184             return;
185         }
186         FileWriter writer = null;
187         try {
188             writer = new FileWriter(EnvironmentManager.getInstance()
189                     .getDataTargetFloderPath() + "\\" + pFileName + ".json");
190             writer.write(jsonStr);
191             writer.flush();
192         } catch (Exception e) {
193             e.printStackTrace();
194             OJCUtils.ShowDialog("导出JSON失败:" + pFileName);
195         } finally {
196             try {
197                 if (null != writer) {
198                     writer.flush();
199                     writer.close();
200                 }
201             } catch (Exception e) {
202                 e.printStackTrace();
203                 OJCUtils.ShowDialog("导出JSON失败:" + pFileName);
204             }
205         }
206     }
207 
208 //    public static void main(String[] args) {
209 //        ProgressMonitorDialog progress = new ProgressMonitorDialog(null);
210 //        IRunnableWithProgress progressTask = new IRunnableWithProgress() {
211 //            @Override
212 //            public void run(IProgressMonitor monitor)
213 //                    throws InvocationTargetException, InterruptedException {
214 //                monitor.beginTask("正在导出数据", IProgressMonitor.UNKNOWN);
215 //                WarpDataManager wdMgr = new WarpDataManager();
216 //                wdMgr.WarpAll(new String[] { "skill.xlsx" }, monitor);
217 //                monitor.done();
218 //            }
219 //        };
220 //
221 //        try {
222 //            progress.run(true, false, progressTask);
223 //        } catch (InvocationTargetException e) {
224 //            e.printStackTrace();
225 //        } catch (InterruptedException e) {
226 //            e.printStackTrace();
227 //        }
228 //    }
229 }