1、jar包:poi-3.14-20160307.jar、poi-ooxml-3.14-20160307.jar
2、导入(本例实现了解析excel生成List):
@Override
public Map<String, Object> parseExcel(String fileName) {
// 1.准备返回的变量
Map<String, Object> resultMap = new HashMap<String, Object>();
String message = "success";
List<Stone> stones = new ArrayList<Stone>();
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、导出
/*
* 调用写入Excel的方法
*/
public static void doExpotToExcel(List<Map> params) throws IOException {
//System.out.println("params===:"+params);
writeXlsx("D:/测试写入XLSX.xls",params);
}
public static void main(String[] args) throws IOException {
writeXlsx("D:/测试写入XLSX.xls", null);
}
/*
* 将数据写入Excel
*/
private static void writeXlsx(String fileName, List<Map> params) throws IOException {
// 创建
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
// 创建单元格样式
HSSFCellStyle titleCellStyle = wb.createCellStyle();
// 指定单元格居中对齐
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleCellStyle.setWrapText(true);//设置自动换行
HSSFRow headerRow = sheet.createRow(0);
HSSFCell headerCell = null;
HSSFFont titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 14);
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleCellStyle.setFont(titleFont);
String[] titles = { "序号", "名称","型号规格","单位","数量","单价","金额","备注"};
for (int c = 0; c < titles.length; c++) {
headerCell = headerRow.createCell(c);
headerCell.setCellStyle(titleCellStyle);
headerCell.setCellValue(titles[c]);
if(c==0){
sheet.setColumnWidth(c, (13 * 160));
}else if(c==1){
sheet.setColumnWidth(c, (50 * 160));
}else if(c==2){
sheet.setColumnWidth(c, (50 * 160));
}else if(c==7){
sheet.setColumnWidth(c, (40 * 160));
}else{
sheet.setColumnWidth(c, (13 * 160));
}
}
HSSFCell bodyCell = null;
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle(); 一、设置背景色:
cellStyle.setFillForegroundColor((short) 13);// 设置背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
具体颜色可以参照:http://blog.csdn.NET/qq_27937043/article/details/72779442
二、设置边框:
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
三、设置居中:
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//垂直底部
cellStyle.setAlignment(HSSFCellStyle.VERTICAL_TOP);//垂直顶部
四、设置字体:
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
cellStyle.setFont(font);//选择创建的字体格式
五、设置列宽:
sheet.setColumnWidth(0, 3766);
//第一个参数代表列id(从0开始),第2个参数代表宽度值 参考 :"2017-06-01"的宽度为2500
六、设置自动换行:
cellStyle.setWrapText(true);//设置自动换行
七、合并单元格:
方法1. 此方法在POI3.8中已经被废弃,建议使用方法2
Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
方法2
CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);
//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
但应注意两个构造方法的参数不是一样的,具体使用哪个取决于POI的不同版本。
sheet.addMergedRegion(region1);
// 指定单元格居中对齐,边框为细
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
cellStyle.setWrapText(true);//设置自动换行
HSSFFont bodyFont = wb.createFont();
bodyFont.setFontHeightInPoints((short) 11);
cellStyle.setFont(bodyFont);
//额外的列样式
HSSFCellStyle cellStyleTemp = wb.createCellStyle();
cellStyleTemp.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平居中
cellStyleTemp.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
HSSFFont tempFont = wb.createFont();
tempFont.setFontHeightInPoints((short) 13);
tempFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyleTemp.setFont(tempFont);
for (int r = 0; r < params.size(); r++) {
HSSFRow row = sheet.createRow(r + 1);
int c = 0;
bodyCell = row.createCell(1);
bodyCell.setCellStyle(cellStyleTemp);
bodyCell.setCellValue(params.get(r).get("product_name").toString());
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("count").toString());
if(params.get(r).get("product_type").toString().equals("")){
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyleTemp);
bodyCell.setCellValue(params.get(r).get("product_name").toString());
}else{
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_name").toString());
}
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_type").toString());
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_unit").toString());
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_count").toString());
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_unit_price").toString());
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_price").toString());
bodyCell = row.createCell(c++);
bodyCell.setCellStyle(cellStyle);
bodyCell.setCellValue(params.get(r).get("product_remark").toString());
}
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
System.out.println("Done");
}
}