1、jar包：poi-3.14-20160307.jar、poi-ooxml-3.14-20160307.jar

2、导入（本例实现了解析excel生成List）：

@Override    public Map
     
       parseExcel(String fileName) {        // 1.准备返回的变量        Map
      
        resultMap = new HashMap
       
        ();        String message = "success";        List
        
          stones = new ArrayList
         
          ();        boolean isE2007 = false; // 判断是否是excel2007格式        if (fileName.endsWith("xlsx")) {            isE2007 = true;        }        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");        // 2.准备workbook        // 同时支持Excel 2003、2007        File excelFile = new File(fileName); // 创建文件对象        Workbook workbook = null;        // 根据文件格式(2003或者2007)来初始化        try {            FileInputStream is = new FileInputStream(excelFile); // 文件流            if (isE2007) {                workbook = new XSSFWorkbook(is);            } else {                workbook = new HSSFWorkbook(is);            }        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }        // 3.遍历集合，组装结果        int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量        // 遍历每个Sheet        for (int s = 0; s < sheetCount; s++) {            Sheet sheet = workbook.getSheetAt(s);            int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数            // 遍历每一行            for (int r = 1; r < rowCount; r++) {                Stone stone = new Stone();                Row row = sheet.getRow(r);                int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数                // 遍历每一列                for (int c = 0; c < cellCount; c++) {                    Cell cell = row.getCell(c);                    int cellType = cell.getCellType();                    String cellStringValue = null;                    switch (cellType) {                    case Cell.CELL_TYPE_STRING: // 文本                        cellStringValue = cell.getStringCellValue();                        break;                    case Cell.CELL_TYPE_NUMERIC: // 数字、日期                        if (DateUtil.isCellDateFormatted(cell)) {                            cellStringValue = fmt.format(cell.getDateCellValue()); // 日期型                        } else {                            cellStringValue = String.valueOf(cell.getNumericCellValue()); // 数字                            if (cellStringValue.contains("E")) {                                cellStringValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字                            }                        }                        break;                    case Cell.CELL_TYPE_BOOLEAN: // 布尔型                        cellStringValue = String.valueOf(cell.getBooleanCellValue());                        break;                    case Cell.CELL_TYPE_BLANK: // 空白                        cellStringValue = cell.getStringCellValue();                        break;                    case Cell.CELL_TYPE_ERROR: // 错误                        cellStringValue = "错误";                        break;                    case Cell.CELL_TYPE_FORMULA: // 公式                        cellStringValue = "错误";                        break;                    default:                        cellStringValue = "错误";                    }                    if (cellStringValue.equals("错误")) {                        message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第[" + (c + 1)                                + "]列解析错误";                        resultMap.put("message", message);                        return resultMap;                    }                    cellStringValue = cellStringValue.trim();                    switch (c) {                    case ConstantsUtil.STONE_EXCEL_COLUMN_STONEID:                        try {                            new Long(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setStoneId(new Long(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_SHAPE:                        stone.setShape(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_CARAT:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setCarat(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_COLOUR:                        stone.setColour(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_CLARITY:                        stone.setClarity(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_CUT:                        stone.setCut(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_POLISH:                        stone.setPolish(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_SYM:                        stone.setSym(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_FLUOR:                        stone.setFluor(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_DIAMETER:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setDiameter(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_PLENGTH:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setpLength(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_PWEIGHT:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setpWeight(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_PDEPTH:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setpDepth(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_PDEPTHPER:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setpDepthPer(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_PTABLE:                        try {                            new Double(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setpTable(new Double(cellStringValue));                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_LAB:                        stone.setLab(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_CERTIID:                        stone.setCertiId(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_STOCKIN:                        Integer stockIn = 0;                        if ("是".equals(cellStringValue)) {                            stockIn = 1;                        }                        stone.setStockIn(stockIn);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_STOCKCITY:                        stone.setStockCity(cellStringValue);                        break;                    case ConstantsUtil.STONE_EXCEL_COLUMN_SINGLEPRICE:                        try {                            new BigDecimal(cellStringValue);                        } catch (NumberFormatException e) {                            message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["                                    + (c + 1) + "]列值类型转换异常";                            resultMap.put("message", message);                            return resultMap;                        }                        stone.setSinglePrice(new BigDecimal(cellStringValue));                        break;                    default:                        message = "解析Excel时发生错误，第[" + (row.getRowNum() + 1) + "]行，第[" + (c + 1) + "]列不应该有值";                        resultMap.put("message", message);                        return resultMap;                    }                }                stone.setIsOnSale(false);                stone.setIsDelete(false);                stones.add(stone);            }        }        resultMap.put("message", message);        resultMap.put("stones", stones);        return resultMap;    }
         
        
       
      
     

3、导出

import java.io.FileOutputStream;import java.io.IOException;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import com.alibaba.fastjson.JSON;class DataInfo {      private String countDate; // 统计日期      private String channelId; // 渠道号        public String getCountDate() {          return countDate;      }        public void setCountDate(String countDate) {          this.countDate = countDate;      }        public String getChannelId() {          return channelId;      }        public void setChannelId(String channelId) {          this.channelId = channelId;      }  };public class TestExport {    public static void main(String[] args) throws IOException {          // 创建          HSSFWorkbook wb = new HSSFWorkbook();          HSSFSheet sheet = wb.createSheet();          // 创建单元格样式          HSSFCellStyle titleCellStyle = wb.createCellStyle();          // 指定单元格居中对齐，边框为细          titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);          titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);          titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);          titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);          titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);          titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);          // 设置填充色          titleCellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);          titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);          // 指定当单元格内容显示不下时自动换行          titleCellStyle.setWrapText(true);          // 设置单元格字体          HSSFFont titleFont = wb.createFont();          titleFont.setFontHeightInPoints((short) 12);          titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);          titleCellStyle.setFont(titleFont);          HSSFRow headerRow = sheet.createRow(0);          HSSFCell headerCell = null;          String[] titles = { "统计日期", "渠道号" };          for (int c = 0; c < titles.length; c++) {              headerCell = headerRow.createCell(c);              headerCell.setCellStyle(titleCellStyle);              headerCell.setCellValue(titles[c]);              sheet.setColumnWidth(c, (30 * 160));          }          // ------------------------------------------------------------------          // 创建单元格样式          HSSFCellStyle cellStyle = wb.createCellStyle();          // 指定单元格居中对齐，边框为细          cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);          cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);          cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);          cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);          // 设置单元格字体          HSSFFont font = wb.createFont();          titleFont.setFontHeightInPoints((short) 11);          font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);          cellStyle.setFont(font);          String infoStr = "[{\"channelId\":\"bodao\",\"countDate\":\"2014-06-11\"},"                  + "{\"channelId\":\"dingzhi\",\"countDate\":\"2014-06-12\"},"                  + "{\"channelId\":\"ruiwei\",\"countDate\":\"2014-06-13\"}]";          List
     
       list = JSON.parseArray(infoStr, DataInfo.class);          for (int r = 0; r < list.size(); r++) {              DataInfo item = list.get(r);              HSSFRow row = sheet.createRow(r + 1);              HSSFCell cell = null;              int c = 0;              cell = row.createCell(c++);              cell.setCellStyle(cellStyle);              cell.setCellValue(item.getCountDate());              cell = row.createCell(c++);              cell.setCellStyle(cellStyle);              cell.setCellValue(item.getChannelId());          }            FileOutputStream fileOut = new FileOutputStream("E:/test/test.xls");          wb.write(fileOut);          fileOut.close();          System.out.println("Done");      }}
     

 

爱的反复