maven 依赖
<dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> <version>2.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.dataformat</groupId> <artifactId>jackson-dataformat-xml</artifactId> <version>2.0.0</version> </dependency>
package excel;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Created by xiaominzh on 2016/11/14.
*/
public class ExcelExportNew {
private static String getCellValue(XSSFCell cell,String columnType)throws Exception{
if("i".equals(columnType)){
return String.valueOf(cell.getNumericCellValue());
}
if("d".equals(columnType)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(cell.getDateCellValue());
}
cell.setCellType(CellType.STRING);
return cell.getRichStringCellValue().toString();
}
private static List<String> loadColumnNames(XSSFRow row){
List<String> array = new ArrayList<String>();
int maxCellNum = row.getLastCellNum();
for(int i=0;i<maxCellNum;i++){
XSSFCell cell = row.getCell(i);
String name = cell.getStringCellValue();
array.add(name);
}
return array;
}
private static List<String> loadColumnTypes(XSSFRow row){
List<String> array = new ArrayList<String>();
int maxCellNum = row.getLastCellNum();
for(int i=0;i<maxCellNum;i++){
XSSFCell cell = row.getCell(i);
String name = cell.getStringCellValue();
array.add(name);
}
return array;
}
private static void convertExcelToJSON(String fileName,String sheetName)throws Exception {
InputStream is = ExcelExportNew.class.getResourceAsStream(fileName);
XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
XSSFSheet sheet = hssfWorkbook.getSheet(sheetName);
int rows = sheet.getLastRowNum();
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
List<String> columnTypes = loadColumnTypes(sheet.getRow(0));
List<String> columnNames = loadColumnNames(sheet.getRow(1));
for (int i = 3; i <= rows; i++) {
XSSFRow row = sheet.getRow(i);
int maxCellNum = row.getLastCellNum();
Map<String, Object> item = new HashMap<String, Object>();
for (int cellIndex = 0; cellIndex < maxCellNum; cellIndex++) {
XSSFCell cell = row.getCell(cellIndex);
String columnName = columnNames.get(cellIndex);
String columnType = columnTypes.get(cellIndex);
String value = null;
try {
// cell.setCellType(CellType.STRING);
value = getCellValue(cell,columnType);
} catch (Exception e) {
System.err.println("row:"+i+",column:"+cellIndex);
System.err.println(e.getMessage());
break;
}
item.put(columnName, value);
}
result.add(item);
}
System.out.println(JSONUtil.getJSONString(result));
}
public static void main(String[] args) throws Exception {
convertExcelToJSON("shop_sale_type_list.xlsx","shop_sale_type_list");
}
}
测试excel文件
输出
转载于:https://blog.51cto.com/antlove/1874907