今天因为工作需要，需要写一个demo将xlsx转换成json数据。将store绑定在category里面。
数据如图：
然后就找到POI和fastjson的jar包。
demo:
public static void main(String[] args) throws IOException {
Xlsx xlsxMain = new Xlsx();
List list = new ArrayList();
HashMap map = xlsxMain.readXlsx();//读取xlsx
Set setIds = map.keySet();
Iterator i = setIds.iterator();
while(i.hasNext()){
int id = i.next();
Category category = map.get(id);
list.add(category);
}
Collections.sort(list);//排序
String json = JSON.toJSONString(list);//将list转换成json
File file = new File("D:\\json.txt");
FileOutputStream out = new FileOutputStream(file);//写入文件
byte[] bytes = json.getBytes();
out.write(bytes);
out.flush();
out.close();
}
private HashMap readXlsx() throws IOException {
String fileName = "D:\\m.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
HashMap map = new HashMap();
//读取sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
//读取行
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
if (rowNum == 0) {
continue;
}
Store store = new Store();
Category category = null;
//读取列
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if(xssfCell == null){
continue;
}
String xssCell = getValue(xssfCell);
if(cellNum == 0){
store.setId((int)Float.parseFloat(xssCell));
}else if(cellNum == 1){
store.setName(xssCell);
}else if(cellNum == 4){
int categoryId = Integer.parseInt(xssCell);
if(map.containsKey(categoryId)){
category = map.get(categoryId);
category.setCategoryId(categoryId);
category.addStore(store);
}else{
category = new Category();
category.setCategoryId(categoryId);
category.addStore(store);
map.put(categoryId, category);
}
}else if(cellNum == 5){
int p = xssCell.indexOf("/");
String b = p > 0? xssCell.substring(0, p) : xssCell;
b = b.toUpperCase();
category.setCategoryName(b);
}else if(cellNum ==6){
store.setBanner(xssCell);
}
}
}
}
return map;
}
//得到值
private String getValue(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
}输出文本：
[{"categoryId":1,"categoryName":"DEPARTMENT STORES","stores":[{"banner":"http://upload.cheaperseeker.com/2014/02/5746e15389.png","id":2612,"name":"Kohls"},{"banner":"http://upload.cheaperseeker.com/2012/04/Macys.jpg","id":2845,"name":"Macy's"},{"banner":"http://upload.cheaperseeker.com/2012/08/logo_bbb.gif","id":557,"name":"Bed Bath & Beyond"},...}]
4723
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
