java 的 Excel 导入导出 （参照：慕课网 关于多版本问题）
（一）普通 Java 程序 (无多版本控制)
1-1 Excel 写入
package com.ssm.test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class PoiEcpExcel {
public static void main(String[] args) {
// 设置第一行名
String[] title = { "id", "name", "sex" };
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
· // 写入第一行
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 循环写入5 条
for (int i = 1; i < 5; i++) {
HSSFRow nextrow = sheet.createRow(i);
HSSFCell cell2 = nextrow.createCell(0);
cell2.setCellValue("a" + i);
cell2 = nextrow.createCell(1);
cell2.setCellValue("user" + i);
cell2 = nextrow.createCell(2);
cell2.setCellValue("男");
}
File file = new File("f:/poi_test.xls");
try {
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
1-2 Excel 读取
public static void main(String[] args) throws IOException {
// 需要解析的 Excel 文件
// File file = new File("f:/test.xlsx");
File file = new File("f:/test.xlsx");
Workbook workbook = null;
String fileName = file.getName();
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
} else if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
}
if (workbook != null) {
Sheet sheet = workbook.getSheetAt(0);
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
// 循环除了第一行的所有行
for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
// 获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
// 循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum + firstCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
String value = (String) getCellValue(cell);
System.out.print(value + "\t");
}
System.out.println();
}
}
}
（二） JavaWeb 程序 (多版本控制)
2.0 pom 文件依赖
<!-- excel导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.7</version>
</dependency>
2-1.文件上传
<form class="modifyall" id="excelForm">
<span class="bigtext">批量导入学生名单</span><br>
<input type="file" name="file" id="file" style="display: inline-block" class="po">
<input id="excelButton" type="button" class="layui-btn layui-btn-sm po" value="导入">
</form>
/*
* 导入名单
*/
function excelUpload() {
var data = new FormData($("#excelForm")[0]);
var URL = root + "/teacher/excelUpload";
$.ajax({
url : URL,
type : "post",
data : data,
async : false,
cache : false,
processData : false,
contentType : false,
success : function(data) {
if (data == "true") {
location.reload();
}
if (data == "false") {
alert("请检查文件格式");
}
},
error : function(data) {
alert("操作失败");
}
});
}
2-2 后台逻辑处理
/*
* excel 导入名单
*/
@ResponseBody
@RequestMapping("/excelUpload")
public Object excelUpload(@RequestParam MultipartFile file, HttpServletRequest request, HttpServletRequest reponse)
throws IOException {
PageData pd = this.getPageData();
//System.out.println(pd);
String check = (String) this.teacherFacade.readExcel(file, pd);
return check;
}
/*
* excel 导入名单
*/
public Object readExcel(MultipartFile file, PageData pd) throws IOException {
// TODO Auto-generated method stub
String fileName = file.getOriginalFilename();
Workbook workbook = null;
String check = "";
InputStream in = file.getInputStream();
System.out.println(fileName);
// 判断后缀名，确定版本
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(in);
} else if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(in);
}
String[] title = { "学号", "姓名", "班级" };
if (workbook != null) {
Sheet sheet = workbook.getSheetAt(0);
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
Row row = null;
int firstCellNum = 0;
int lastCellNum = 0;
Cell cell = null;
String value = "";
row = sheet.getRow(firstRowNum);
// 获得当前行的开始列
firstCellNum = row.getFirstCellNum();
// 获得当前行的列数
lastCellNum = row.getPhysicalNumberOfCells();
// 循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum + firstCellNum; cellNum++) {
cell = row.getCell(cellNum);
value = (String) GetCellValues.getCellValue(cell);
if (title[cellNum].equals(value)) {
check = "true";
} else {
check = "false";
break;
}
}
System.out.println(check);
if (check.equals("true")) {
// 循环除了第一行的所有行
for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 获得当前行的开始列
firstCellNum = row.getFirstCellNum();
// 获得当前行的列数
lastCellNum = row.getPhysicalNumberOfCells();
// 循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum + firstCellNum; cellNum++) {
cell = row.getCell(cellNum);
value = (String) GetCellValues.getCellValue(cell);
if (pd != null) {
switch (cellNum) {
case 0:
pd.put("stuid", value);
break;
case 1:
pd.put("stuname", value);
break;
case 2:
pd.put("stuclass", value);
break;
default:
break;
}
}
}
boolean b = this.teacherService.insertStudent(pd);
}
}
}
return check;
// TODO: handle exception
}
2-3 获取 Excel Cell 值的工具类
package com.ssm.sample.util;
import org.apache.poi.ss.usermodel.Cell;
public class GetCellValues {
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
// 判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
2-4 从数据库导出 Excel
<a class="test_after_op btn btn-default" id="exportrecords_info" href="excelExport?testid=${test.get('testid')}" title="导出提交记录"><img
src="<%=path%>/assets/imgs/export_record.png" class="test_afterlogos" alt=""></a>
2-5 后台 /*
* excel 导出提交信息
*/
@ResponseBody
@RequestMapping("/excelExport")
public void excelExport(HttpServletRequest request, HttpServletRequest reponse) throws IOException {
PageData pd = this.getPageData();
System.out.println(pd);
String testid = pd.getString("testid");
List<PageData> test = this.teacherFacade.selectTestById(testid);
List<PageData> list = this.teacherFacade.selectUploadById(testid);
System.out.println(list);
String[] title = { "学号", "提交时间", "文件名", "文件大小" };
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
// 设置 Excel 第一行内容
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 从数据库获取数据加入 Excel 文件
for (int i = 1; i < list.size(); i++) {
HSSFRow nextrow = sheet.createRow(i);
HSSFCell cell2 = nextrow.createCell(0);
cell2.setCellValue(list.get(i).getString("stuid"));
cell2 = nextrow.createCell(1);
cell2.setCellValue(list.get(i).getString("fileuptime"));
cell2 = nextrow.createCell(2);
cell2.setCellValue(list.get(i).getString("filename"));
cell2 = nextrow.createCell(3);
cell2.setCellValue(list.get(i).getString("filesize"));
}
String fileName = test.get(0).getString("testname") + ".xls";
System.out.println(fileName);
String path = request.getSession().getServletContext().getRealPath("/") + "ExamSystem/" + fileName;
File file = new File(path);
try {
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 导出
response.addHeader("content-disposition", "attachment;filename=" + fileName);
FileUtils.copyFile(file, response.getOutputStream());
}
