Java通过Poi的开发Excel导入导出和下载功能
Java通过Poi的开发Excel导入导出和下载功能
sunonzj
发布于 2022-06-21 14:05:33
发布于 2022-06-21 14:05:33
最近有用到Excel的下载、导入、导出功能。提供一个Excel模板给用户下载，用户根据规范填写模板然后再导入Excel数据，保存到数据库，也可导出类表数据为Excel。因为有时候页面添加功太麻烦，就做成这样的Excel批量导入。
154466968004240052054.png
154466968512245091885.png
Excel的下载
这项目用的是spring+Struts2+mybatis。
需要的jar包
<!-- POI-EXCEL -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- POI-EXCEL 这个包使用下面的最新ExcelUtil-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>有两种方式
第一种、把Excel模板放在项目目录，提供下载。（这种比较方便）
Excel的路径：webapp/common/excelModule/downloadModel.xlsx。
前台代码：
<form action="" name="Form" id="myForm" method="" enctype="multipart/form-data">
<div id="zhongxin">
<table style="width:95%;" >
<tr>
<td style="padding-top: 20px;"><input type="file" id="excel"/></td>
</tr>
<tr>
<td style="text-align: center;padding-top: 10px;">
<a class="btn btn-mini btn-primary" id="submitBtn">导入</a>
<a class="btn btn-mini btn-success" id="downLoadExcel">下载模版</a>
</td>
</tr>
</table>
</div>
<div id="zhongxin2"class="center" style="display:none"><br/><img src="images/jzx.gif"/>
<br/><h4 class="lighter block green"></h4></div>
</form>js代码：
$('#downLoadExcel').click(function(){
location.href ='downLoadModel.action';
});action代码：
/**
* 下载文件的文件名和流
*/
private String fileName;
private InputStream is;
略：get set方法。
/**
* 下载模板
*
* @throws Exception
*/
public String downLoadModel() throws Exception {
// 为InputStream的is流参数赋值
is = ServletActionContext.getServletContext()
.getResourceAsStream("/common/excelModule/downloadModel.xlsx");
fileName = new String("题目导入模板.xlsx".getBytes(), "ISO8859-1");
return "success";
}Struts2配置文件：
<action name="*Question" class="com.bayan.keke.action.QuestionAction"
method="{1}">
<result name="toList">/WEB-INF/jsp/question/questionList.jsp</result>
<result name="success" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="contentDisposition">attachment;fileName=${fileName}</param>
<param name="inputName">is</param>
<param name ="bufferSize">4096</param>
</result>
</action>好了，一个简单的下载功能就完成了。
注意：Struts的配置文件中的文件名和流要和action的对应。
第二种、自定义生成Excel模板，提供下载。（ExcelUtil工具类代码在底下）
/**
* 下载模板
*
* @throws Exception
*/
public void downExcel() throws Exception {
try {
String[] titles =
new String[] {"比赛名称", "报名者姓名", "报名者手机号",
"报名者身份证号", "性别", "邮箱", "住址", "职业", "分数"};
String[] cols =
new String[] {"partyName", "userName", "userPhone",
"userIdCard", "sex", "email",
"address", "career", "score"};
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
ExcelUtil.outPutExcelByMap(list, titles, cols, "题目信息模板",
getRequest(), getResponse());
renderNull();
} catch (Exception e) {
e.printStackTrace();
}
toList();
}Excel的导入
js代码：通过ajax，formdata格式提交文件
//导入Excel
$("#importExcel").click(function(){
$('#importQuestion').modal('show');
});
$("#submitBtn").click(function(){
var fileName = $("#excel").val();
var suffix = (fileName.substr(fileName.lastIndexOf("."))).toUpperCase();
//验证文件后缀名
if(".XLS" != suffix && ".XLSX" != suffix ){
alert("文件格式只能是.XLS或者.XLSL");
return false;
}
var formData = new FormData();
var file = $("#excel")[0].files[0];
if (file != "") {
formData.append("excel", file);
}
$.ajax({
type : "POST",
url : "importExcel.action",
data : formData,
cache : false,
processData : false,
contentType : false,
dataType:'json',
success : function(data) {
if (data.result == "success") {
alert("导入题目成功");
$('#importQuestion').modal('hide');
getQuestionList();
}else if(data.result == "fileNull"){
alert("导入的文件为空");
}else if(data.result == "excelNull"){
alert("导入的Excel表格内容为空");
}else if(data.result == "maxLength"){
alert("导入的Excel表格内容超过了最大1000");
} else if(data.result == "fail"){
alert("导入数据库失败");
}else if(data.result=="error"){
alert(data.codeError);
}else{
alert("导入失败");
}
}
});
//$("#myForm").submit();
});action代码：
/**
* 导入Excel
*
* @throws Exception
*/
@SuppressWarnings("unused")
public void importExcel() throws Exception {
printStartLog("方法开始update", LOGGER);
// 将客户端的文件上传到服务端
String desPath = ServletActionContext.getServletContext()
.getRealPath("/upload/questionModel");
File destFile = new File(excelFileName);
// 上传到本地
FileUtils.copyFile(excel, destFile);
JSONObject json = new JSONObject();
if (destFile == null) {
printDebugLog("导入的文件为空", LOGGER);
json.element("result", "fileNull");
print(json);
return;
}
// 解析excel
List<String[]> excelList = ExcelUtil.getExcelData(excel, 1);
if (excelList.size() > 1000) {
printDebugLog("导入的Excel表格内容超过了最大1000", LOGGER);
json.element("result", "maxLength");
print(json);
return;
}
String lengthMsg = "", formatMsg = "", emptyMsg = "";
int n = 2;
// 格式校验
for (String[] data : excelList) {
if (data.length > 17 || Tools.isEmpty(data)) {
formatMsg += "第" + n + "行数据为空或者超出列数、";
}
if (data[0].length() > 20) {
lengthMsg += "第" + n + "行题目编号、";
}
if (Tools.isEmpty(data[1])) {
emptyMsg += "第" + n + "行题目标题、";
} else if (data[1].length() > 1000) {
lengthMsg += "第" + n + "行题目标题、";
}
if (Tools.isEmpty(data[3])) {
emptyMsg += "第" + n + "行有无题目附件、";
} else if (!"0".equals(data[3]) && !"1".equals(data[3])) {
formatMsg += "第" + n + "行有无题目附件只能填0或1、";
} else if ("1".equals(data[3]) && Tools.isEmpty(data[4])) {
formatMsg += "第" + n + "题目附件名必填、";
} else if ("0".equals(data[3]) && Tools.isNotEmpty(data[4])) {
formatMsg += "第" + n + "题目附件名不必填、";
}
if (Tools.isNotEmpty(data[4]) && data[4].length() > 255) {
lengthMsg += "第" + n + "行题目附件名、";
}
if (Tools.isNotEmpty(data[5]) && data[5].length() > 200) {
lengthMsg += "第" + n + "行题目标签、";
}
if (Tools.isEmpty(data[6])) {
emptyMsg += "第" + n + "行所属学科、";
} else if (!getCodeList(KeConstant.QUESTION_SUBJECT_TYPE_OWNID)
.contains(data[6])) {
formatMsg += "第" + n + "行所属学科值不在范围内、";
}
if (Tools.isEmpty(data[7])) {
emptyMsg += "第" + n + "行题型类别、";
} else if (!getCodeList(KeConstant.QUESTION_TYPE_OWNID).contains(data[7])) {
formatMsg += "第" + n + "行题型类别值不在范围内、";
}
System.out.println(data[0] + "---" + data[1] + "---" + data[2]+
"---" + data[3] + "---" + data[4] + "---"
+ data[5] + "---" + data[6] + "---" + data[7]);
n++;
}
if (!"".equals(lengthMsg) || !"".equals(formatMsg) || !"".equals(emptyMsg)) {
json.element("result", "error");
json.element("codeError", assembleErrorMsg(formatMsg,emptyMsg,lengthMsg));
print(json);
return;
}
// 存入数据库操作======================================//
// 新增列表
boolean result = save(excelList);
if (result) {
json.element("result", "success");
print(json);
} else {
json.element("result", "fail");
print(json);
}
// 请求结束log
printEndLog("更新学生结束返回值:", json.toString(), LOGGER);
}
private Boolean save(List<String[]> excelList) throws Exception {
List<KeQuestion> saveQuestionList = new ArrayList<KeQuestion>();
Date now = new Date();
for (String[] data : excelList) {
KeQuestion saveQuestion = new KeQuestion();
saveQuestion.setId(Tools.getUniqueId());
saveQuestion.setTitle(data[1]);
saveQuestion.setIndex(data[0]);
saveQuestion.setContent(data[2]);
saveQuestion.setHasAnnex(Integer.parseInt(data[3]) == 0?false:true);
saveQuestion.setResourceUrl(data[4]);
saveQuestion.setSpan(data[5]);
......
}
boolean result = true;
result = questionService.insertQuestion(saveQuestionList);
return result;
}
private String assembleErrorMsg(String formatMsg,String emptyMsg,String length) {
String tabf = !"".equals(formatMsg)||!"".equals(emptyMsg)?"---": "";
String promptMsg="长度超过规定范围。" + tabf : "";
String tabs = !"".equals(emptyMsg) ? "---" : "";
promptMsg += !"".equals(formatMsg) ? formatMsg.substring(0, formatMsg.length()
- 1) + "格式错误。" + tabs : "";
promptMsg += !"".equals(emptyMsg) ? emptyMsg.substring(0, emptyMsg.length()
- 1) + "为空，无法导入。" : "";
return promptMsg;
}注意：Excel的验证根据自己的需求来判断，验证无误的在通过对象保存到数据库中。Excel单元格通通为文本格式，不然有问题
遇到过的问题：当Excel最后一列为空时，比如一空有10列，但最后一列为空时拿到了ExcelList长度为9，，折腾了半天无果，就改为了通过第一行标题来获取列长度，这样就没问题了。注意数据还是从第二行开始获取。还有一个问题就是，当填的值为0等数字时，取到则为0.0，设置了Excel的单元格为文本格式还是没用，需要设置为强文本格式（选择单元格点导航栏数据中的分列，然后下一步，下一步，选择文本，完成即可）。
然后还有就是一定要效验好数据，不能漏了各种会出现的情况，不然保存到数据库出问题就大条了。
Excel的导出
java代码：
/**
* 导出表格信息为Excel
*
* @throws Exception
*/
public void excelExport() throws Exception {
try {
String[] titles = new String[] {"比赛名称", "报名人姓名",
"报名人手机号", "身份证号", "报名时间", "分数"};
String[] cols =
new String[] {"partyName", "userName", "userPhone",
"userIdCard", "creationTime","score"};
// 查询比赛信息
ExaminationPartyInfo keywords = new ExaminationPartyInfo();
keywords.setIsDeleted(Const.ISDELETED_FALSE).setType(Const.PARTYTYPE_BS);
if (Tools.isNotEmpty(Jurisdiction.getMerchanismId())) {
keywords.setMerchanismId(Jurisdiction.getMerchanismId());
}
List<ExaminationPartyInfo>partyList =examinationPartyInfoService.findList(keywords);
// 查询报名信息
ExaminationRegisterInfo condition = new ExaminationRegisterInfo();
condition.setPartyIdArray(Tools.array2Long(StringUtil
.listToString(partyList, "partyId")));
List<ExaminationRegisterInfo> registerList =
examinationRegisterInfoService.findList(condition);
Map<String, Object> partyMap = Tools.list2Map(partyList, "partyId", "name");
Map<String, Object> userNameMap = new HashMap<String, Object>(16);
Map<String, Object> userPhoneMap = new HashMap<String, Object>(16);
Map<String, Object> userIdCardMap = new HashMap<String, Object>(16);
if (registerList.size() > 0) {
List<ExaminationUserInfo> userInfoList =
examinationUserInfoService.findByIdAsGroup(Tools.array2Long(StringUtil
.listToString(registerList, "userId")));
if (userInfoList.size() > 0) {
userInfoList.stream().forEach(e -> {
userNameMap.put(String.valueOf(e.getUserId()), e.getName());
userPhoneMap.put(String.valueOf(e.getUserId()), e.getPhone());
userIdCardMap.put(String.valueOf(e.getUserId()), e.getIdCard());
});
}
registerList.stream().forEach(e -> {
e.put("partyName", partyMap.get(e.getPartyId()));
e.put("userName", userNameMap.get(e.getUserId()));
e.put("userPhone", userPhoneMap.get(e.getUserId()));
e.put("userIdCard", userIdCardMap.get(e.getUserId()));
});
ExaminationGradeInfo examinationGradeInfo =
getBean(ExaminationGradeInfo.class, "", true);
examinationGradeInfo.setRegisterIdArray(Tools.array2Long(StringUtil.listToString(
registerList, "registerId")));
examinationGradeInfo.setIsDeleted(Const.ISDELETED_FALSE);
Page<ExaminationGradeInfo> examinationGradeInfoPage =
examinationGradeInfoService.findListInPageWithKeywords(
getParaToInt("pageNumber"), getParaToInt("pageSize"), examinationGradeInfo,
getPara("sortName"), getPara("sortOrder"));
Map<String, Object> registerInfoMap = Tools.list2Map(registerList, "registerId");
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
examinationGradeInfoPage.getList().forEach(
e -> {
Map<String, Object> map = new HashMap<String, Object>(16);
ExaminationRegisterInfo registerInfo =
(ExaminationRegisterInfo)registerInfoMap.get(e.getRegisterId());
if (registerInfo != null) {
map.put("userName", registerInfo.get("userName"));
map.put("userPhone", registerInfo.get("userPhone"));
map.put("userIdCard", registerInfo.get("userIdCard"));
map.put("partyName", registerInfo.get("partyName"));
map.put("creationTime", registerInfo.getCreationTime());
}
map.put("score", e.getScore());
list.add(map);
});
ExcelUtil.outPutExcelByMap(list, titles, cols, "报名信息" + Tools.getUniqueId(),
getRequest(), getResponse());
}
renderNull();
} catch (Exception e) {
e.printStackTrace();
}
}ExcelUtil 工具类：
package com.egaosoft.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;
/**
* 使用poi报表导出工具类 把poi的一个调用接口抽出来，便于导出功能的管理
*/
public class ExcelUtil {
/**
* 导出list中map做载体的数据到excel 参数说明： list:存放了Map数据的集合 hdNames:表头列名 hds:对应表头的数据KEY xlsName:导出文件名
*/
public static <T> boolean outPutExcelByMap(List<Map<String, Object>> list, String[] hdNames, String[] hds,
String xlsName, HttpServletRequest request, HttpServletResponse response) throws Exception {
Workbook wb = new HSSFWorkbook(); // 创建工作薄
Sheet sheet = wb.createSheet(); // 创建工作表
sheet.autoSizeColumn((short)0); // 自适应宽度
// 写入表头---Excel的第一行数据
Row nRow = sheet.createRow(0); // 创建行
for (int i = 0; i < hdNames.length; i++) {
Cell nCell = nRow.createCell(i); // 创建单元格
nCell.setCellValue(hdNames[i]);
}
// 写入每一行数据---一条记录就是一行数据
for (int i = 0; i < list.size(); i++) {
for (int j = 0; j < hds.length; j++) {
Object o = list.get(i).get(hds[j]); // 得到列的值
data2Excel(sheet, o, i + 1, j); // 将值写入Excel
}
}
setSizeColumn(sheet, hdNames.length);
return downloadExcel(wb, xlsName, request, response);
}
/**
* 传递一个Wookbook，给定文件名，以及request和response下载Excel文档
*
* @throws IOException
*/
@SuppressWarnings("all")
private static boolean downloadExcel(Workbook wb, String xlsName, HttpServletRequest request,
HttpServletResponse response) throws IOException {
if (request.getHeader("user-agent").indexOf("MSIE") != -1) {
xlsName = java.net.URLEncoder.encode(xlsName, "utf-8") + ".xls";
} else {
xlsName = new String(xlsName.getBytes("utf-8"), "iso-8859-1") + ".xls";
}
OutputStream os = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + xlsName);
wb.write(os);
return true;
}
/**
* 将数据写到Excel中
*/
private static void data2Excel(Sheet sheet, Object o, Integer r, Integer c) {
// 通过获得sheet中的某一列，有得到，没有创建
Row nRow = sheet.getRow(r);
if (nRow == null) {
nRow = sheet.createRow(r);
}
// nRow.setColumnWidth(r, arg1);
Cell nCell = nRow.createCell(c);
// 根据不同类型进行转化，如有其它类型没有考虑周全的，使用发现的时候添加
char type = 'x';
if (o instanceof Integer) {
type = 1;
} else if (o instanceof Double) {
type = 2;
} else if (o instanceof Float) {
type = 3;
} else if (o instanceof String) {
type = 4;
} else if (o instanceof Date) {
type = 5;
} else if (o instanceof Calendar) {
type = 6;
} else if (o instanceof Boolean) {
type = 7;
} else if (o == null) {
type = 8;
}
switch (type) {
case 1:
nCell.setCellValue((Integer)o);
break;
case 2:
nCell.setCellValue((Double)o);
break;
case 3:
nCell.setCellValue((Float)o);
break;
case 4:
nCell.setCellValue((String)o);
break;
case 5:
nCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(o));
break;
case 6:
nCell.setCellValue((Calendar)o);
break;
case 7:
nCell.setCellValue((Boolean)o);
break;
case 8:
nCell.setCellValue("");
break;
default:
nCell.setCellValue(o + "");
break;
}
}
public static List<String[]> getExcelData(File file) {
return getData(file, 0).get(0);// 选择sheet1
}
public static List<String[]> getExcelData(File file, int rowStart) {
return getData(file, rowStart).get(0);// 选择sheet1
}
@SuppressWarnings("deprecation")
public static List<List<String[]>> getData(File file, int rowStart) {
Workbook workbook;
List<List<String[]>> data = new ArrayList<List<String[]>>();
try {
workbook = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = null;
// 循环sheet
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheet = workbook.getSheetAt(i);
List<String[]> rows = new ArrayList<String[]>();
int colsnum = 0;
// 循环每一行
for (int j = rowStart; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
if (null != row) {
// 列数以excel标题为准
colsnum = sheet.getRow(0).getLastCellNum();
String[] cols = new String[colsnum];
// 循环每一个单元格，以一行为单位，组成一个数组
for (int k = 0; k < colsnum; k++) {
// 判断单元格是否为null，若为null，则置空
if (null != row.getCell(k)) {
int type = row.getCell(k).getCellType();
// 判断单元格数据是否为数字
if (type == HSSFCell.CELL_TYPE_NUMERIC) {
// 判断该数字的计数方法是否为科学计数法，若是，则转化为普通计数法
if (String.valueOf(row.getCell(k).getNumericCellValue()).matches(".*[E|e].*")) {
DecimalFormat df = new DecimalFormat("#.#");
// 指定最长的小数点位为10
df.setMaximumFractionDigits(10);
cols[k] = df.format(row.getCell(k).getNumericCellValue());
// 判断该数字是否是日期,若是则转成字符串
} else if (HSSFDateUtil.isCellDateFormatted(row.getCell(k))) {
Date d = row.getCell(k).getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cols[k] = formater.format(d);
} else {
BigDecimal number =
BigDecimal.valueOf(Double.valueOf(row.getCell(k).toString()));
if (number.compareTo(number.setScale(0)) == 0) {
cols[k] = number.setScale(0).toString();
continue;
}
cols[k] = (row.getCell(k) + "").trim();
}
} else {
cols[k] = (row.getCell(k) + "").trim();
}
} else {
cols[k] = "";
}
}
// 去除全是空值得行
int num = 0;
for (String col : cols) {
if (Tools.isEmpty(col)) {
num++;
}
}
if (num != cols.length) {
// 以一行为单位，加入list
rows.add(cols);
}
}
}
// 返回所有数据，第一个list表示sheet，第二个list表示sheet内所有行数据，第三个string[]表示单元格数据
data.add(rows);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
return data;
}
@SuppressWarnings("deprecation")
private static void setSizeColumn(Sheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum++) {
sheet.autoSizeColumn((short)columnNum);
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
Row currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
Cell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, 256 * columnWidth + 184);
}
}
}SSM框架下导入导出
导出：
/**
* 导出题目
*/
@RequestMapping(value="excelExport")
@ResponseBody
public ModelAndView excelExport(){
logBefore(logger, Jurisdiction.getUsername() + "导出信息");
ModelAndView mv = this.getModelAndView();
PageData pd = this.getPageData();
String keywords = pd.getString("keywords"); // 检索条件 关键词
if (null != keywords && !"".equals(keywords)) {
pd.put("keywords", keywords.trim());
}
try {
PageData bPd = new PageData();
bPd.put("tableName", "aicp_question");
bPd.put("columnName", "type");
Map<String, String> basicMap = Tools.list2Map(basicVariableService.listAll(bPd), "value", "title");
Map<String, Object> dataMap = new HashMap<String, Object>();
List<String> titles = new ArrayList<String>();
titles.add("题目");
titles.add("类型");
titles.add("答案");
titles.add("解析");
titles.add("选项");
dataMap.put("titles", titles);
List<PageData> listData = aicpQuestionService.listAll(pd);
List<PageData> varList = new ArrayList<PageData>();
listData.stream().forEach(e ->{
PageData vpd = new PageData();
vpd.put("var1", e.getString("question"));
vpd.put("var2", basicMap.get(e.getString("type")));
vpd.put("var3", e.getString("answer"));
vpd.put("var4", e.getString("parsing"));
vpd.put("var5", e.getString("option"));
varList.add(vpd);
});
dataMap.put("varList", varList);
ObjectExcelView erv = new ObjectExcelView();
mv = new ModelAndView(erv, dataMap);
} catch (Exception e) {
logger.error(e.toString(), e);
}
return mv;
}导入和下载模板：
@RequestMapping(value = "/downExcel")
public void downExcel(HttpServletResponse response) throws Exception {
FileDownload.fileDownload(response, PathUtil.getClasspath() + Const.FILEPATHFILE + "2020102120125.xls",
"2020102120125.xls");
}
@RequestMapping(value = "/readExcel")
public ModelAndView readExcel(@RequestParam(value = "excel", required = false) MultipartFile file, @RequestParam(value = "warehouseId", required = false)String warehouseId)
throws Exception {
logBefore(logger, Jurisdiction.getUsername() + "：从EXCEL导入题目。");
ModelAndView mv = this.getModelAndView();
if (!this.hasAddQX(this.menuId)) {
logAfter(logger, Jurisdiction.getUsername() + "：无权从EXCEL导入题目");
} else {
Map<String, Object> map = new HashMap<String, Object>();
try {
if (null != file && !file.isEmpty()) {
String filePath = PathUtil.getClasspath() + Const.FILEPATHFILE;
String fileName = FileUpload.fileUp(file, filePath, "userexcel");
List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 2, 0, 0);
String date = Tools.date2Str(new Date());
List<PageData> saveList = new ArrayList<PageData>();
for (PageData pd : listPd) {
PageData cd = new PageData();
cd.put("id", getUniqueId());
cd.put("warehouseId", warehouseId);
cd.put("question", pd.get("var0"));
cd.put("option", pd.get("var4"));
cd.put("answer", pd.get("var2"));
cd.put("parsing", pd.get("var3"));
if(pd.getString("var1").equals("单选")){
cd.put("type", "0");
}else if(pd.getString("var1").equals("多选")){
cd.put("type", "1");
}else if(pd.getString("var1").equals("填空")){
cd.put("type", "2");
}else if(pd.getString("var1").equals("判断")){
cd.put("type", "3");
}
cd.put("createTime", new Date());
cd.put("modifiedTime", new Date());
cd.put("isDeleted", "0");
saveList.add(cd);
}
mv.addObject("data", JSONObject.fromObject(aicpQuestionService.insertMultiple(saveList)));
}
} catch (Exception e) {
e.printStackTrace();
mv.addObject("data",
JSONObject.fromObject(Tools.fillMap(map, false, Const.TYPE_ALERT, "导入失败！请及时联系管理员解决问题，以免发生后续错误。")));
}
}
mv.setViewName("save_result");
return mv;
}ObjectExcelRead工具类
package com.fh.util;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
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;
/**
* 从EXCEL导入到数据库
* 创建人：FH Q313596790
* 创建时间：2014年12月23日
* @version
*/
public class ObjectExcelRead {
/**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
@SuppressWarnings({ "deprecation", "resource" })
public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Object> varList = new ArrayList<Object>();
try {
File target = new File(filepath, filename);
FileInputStream fi = new FileInputStream(target);
HSSFWorkbook wb = new HSSFWorkbook(fi);
HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { //行循环开始
PageData varpd = new PageData();
HSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { //列循环开始
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var"+j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
}ObjectExcelView工具类
package com.fh.util;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
/**
* 导入到EXCEL
* 类名称：ObjectExcelView.java
* @author FH Q313596790
* @version 1.0
*/
@SuppressWarnings({"deprecation", "unchecked"})
public class ObjectExcelView extends AbstractExcelView{
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
PageData vpd = varList.get(i);
for(int j=0;j<len;j++){
String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,varstr);
}
}
}
}本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2018-12-11 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读