apache poi 读取xlsx并导出为json（没考虑xls）-白红宇

apache poi 读取xlsx并导出为json（没考虑xls）

阅读量：6148 次

发布时间：2019-06-21

本文共 7898 字，大约阅读时间需要 26 分钟。

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
     
       titles = null; 57         Map
      
        oneCellData = null; 58         List
       
        
         > 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
         
          (); 74                 AllDataList = new ArrayList
          
           
            >(); 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
            
             ();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.out126 // .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
             
              
               > 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 // @Override212 // 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 }