使用apache的poi实现导入导出excel
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了使用apache的poi实现导入导出excel，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10386字，纯文字阅读大概需要15分钟。
内容图文
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的数量
// 遍历每个Sheetfor (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;
}
publicvoid setCountDate(String countDate) {
this.countDate = countDate;
}
public String getChannelId() {
return channelId;
}
publicvoid setChannelId(String channelId) {
this.channelId = channelId;
}
};
publicclass TestExport {
publicstaticvoid 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<DataInfo> 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");
}
}
爱的反复
原文：http://www.cnblogs.com/mabaishui/p/5826864.html
内容总结
以上是互联网集市为您收集整理的使用apache的poi实现导入导出excel全部内容，希望文章能够帮你解决使用apache的poi实现导入导出excel所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】