Java tutorial
package com.bawan.vims.common.util; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.bawan.vims.common.IDGenerator; /** * POI * <p> Description : Excel ? </p> * <p> Copyright : Copyright (c) 2015 by FITT</p> * <p> Author : ZCC </p> * <p> Create : 2015320 ?11:29:27 </p> * <p> version : 1.0 </p> */ public class ExcelHelper { private static Logger logger = LoggerFactory.getLogger(ExcelHelper.class); /** * ?excel */ public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) { Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>(); InputStream in = null; Workbook wb = null; try { File excelFile = new File(excelFilePath); if (excelFile == null || !excelFile.exists()) { logger.error("ExcelHelper[parserExcel] excel file don't exist!"); return null; } in = new FileInputStream(excelFile); String suffix = excelFilePath.substring(excelFilePath.lastIndexOf(".")); if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) { logger.error("ExcelHelper[parserExcel] file suffix do'not match[*.xls, *.xlsx]! "); return null; } /*else if ("xls".equals(suffix)){ wb = new HSSFWorkbook(in); } else if("xlsx".equals(suffix)) { wb = new XSSFWorkbook(in); }*/ wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx? int sheetSize = 0; while (true) { Sheet sheet = wb.getSheetAt(sheetSize); if (sheet == null) { break; } String sheetName = sheet.getSheetName(); List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>(); for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) { Row row = sheet.getRow(rowNum); Map<String, Object> rowMap = new HashMap<String, Object>(); StringBuffer rowContent = new StringBuffer( "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values("); rowContent.append("'").append(IDGenerator.getID(32)).append("',"); for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) { Cell cell = row.getCell(cellIndex); // if (cell == null) { // rowMap.put(rowNum + "_" + cellIndex, null); // } else { // rowMap.put(rowNum + "_" + cellIndex, cell.toString()); // } if (cellIndex == 2) { if (cell == null) { rowContent.append(0).append(","); } else if ("mpv".equalsIgnoreCase(cell.toString())) { rowContent.append(1).append(","); } else if ("suv".equalsIgnoreCase(cell.toString())) { rowContent.append(2).append(","); } else if ("".equalsIgnoreCase(cell.toString())) { rowContent.append(3).append(","); } else if ("".equalsIgnoreCase(cell.toString())) { rowContent.append(4).append(","); } else if ("?".equalsIgnoreCase(cell.toString())) { rowContent.append(5).append(","); } else if ("".equalsIgnoreCase(cell.toString())) { rowContent.append(6).append(","); } continue; } if (cell == null || cell.toString().trim().length() == 0) { if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3 || cellIndex == 6) { rowContent.append("default").append(","); } else { rowContent.append("0").append(","); } } else { if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3 || cellIndex == 6) { rowContent.append("'").append(cell.toString()).append("',"); } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8 || cellIndex == 9) { String value = cell.toString().substring(0, cell.toString().indexOf(".")); rowContent.append(Integer.valueOf(value)).append(","); } else { rowContent.append(cell.toString()).append(","); } } } String sql = rowContent.toString(); sql = sql.substring(0, sql.length() - 1); sql += ");"; System.out.println(sql); sheetContent.add(rowMap); } result.put(sheetName, sheetContent); sheetSize++; } } catch (Exception e) { e.printStackTrace(); logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e); } finally { try { if (wb != null) { wb.close(); wb = null; } } catch (IOException e1) { } try { if (in != null) { in.close(); in = null; } } catch (IOException e) { } } return result; } /** * excel?? */ public static void exportExcel() { } public static void main(String[] args) { String excelFilePath = "E:\\lean-soft-suntek\\database-bak\\\\201502\\\\20141216-v2.xlsx"; Map<String, List<Map<String, Object>>> excel = ExcelHelper.parserExcel(excelFilePath); for (Iterator ite = excel.keySet().iterator(); ite.hasNext();) { String sheetName = (String) ite.next(); List<Map<String, Object>> sheetContent = excel.get(sheetName); StringBuffer rowContent = new StringBuffer( "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values("); for (Map<String, Object> row : sheetContent) { for (Iterator it = row.keySet().iterator(); it.hasNext();) { String key = (String) it.next(); Object value = row.get(key); if (value == null || value.toString().trim().length() == 0) { rowContent.append("default").append(","); } else { rowContent.append(value.toString()).append(","); } String sql = rowContent.toString(); sql = sql.substring(0, sql.length()); sql += ") \n go \n"; System.out.println(sql); } } } } }