注意的导包:poi的几个包,dom4j,xmlbeans这个包(因为这个包没导入QB调试了6个多小时才找到错误,感觉亏了一个亿)
SpringMvc配置增加:
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <!-- set the max upload size100MB --> <property name="maxUploadSize"> <value>104857600</value> </property> <property name="maxInMemorySize"> <value>4096</value> </property> <property name="defaultEncoding"> <value>utf-8</value> </property> </bean>
工具类:
创建一个ExcelBean实现数据的封装
ExcelBean.java
- package cn.gsp.common.utils.excel;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- /**
- * @Description: 创建一个ExcelBean实现数据的封装
- * @author QB
- * @date 2017年10月17日 上午9:19:00
- */
- public class ExcelBean implements java.io.Serializable {
- /**
- * @Fields serialVersionUID : TODO(用一句话描述这个变量表示什么)
- */
- private static final long serialVersionUID = 1L;
- private String headTextName;// 列头(标题)名
- private String propertyName;// 对应字段名
- private Integer cols;// 合并单元格数
- private XSSFCellStyle cellStyle;
- public ExcelBean() {
- }
- public ExcelBean(String headTextName, String propertyName) {
- this.headTextName = headTextName;
- this.propertyName = propertyName;
- }
- public ExcelBean(String headTextName, String propertyName, Integer cols) {
- super();
- this.headTextName = headTextName;
- this.propertyName = propertyName;
- this.cols = cols;
- }
- public String getHeadTextName() {
- return headTextName;
- }
- public void setHeadTextName(String headTextName) {
- this.headTextName = headTextName;
- }
- public String getPropertyName() {
- return propertyName;
- }
- public void setPropertyName(String propertyName) {
- this.propertyName = propertyName;
- }
- public Integer getCols() {
- return cols;
- }
- public void setCols(Integer cols) {
- this.cols = cols;
- }
- public XSSFCellStyle getCellStyle() {
- return cellStyle;
- }
- public void setCellStyle(XSSFCellStyle cellStyle) {
- this.cellStyle = cellStyle;
- }
- }
excel的导入工具类(这里QB只采用了原博主的导入工具类,同时也进行了一些符合自己现状的修改)
ExcelUtil.java
- package cn.gsp.common.utils.excel;
- import java.beans.IntrospectionException;
- import java.beans.PropertyDescriptor;
- import java.io.InputStream;
- import java.lang.reflect.InvocationTargetException;
- import java.lang.reflect.Method;
- import java.text.DecimalFormat;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Map;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- 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.util.CellRangeAddress;
- import org.apache.poi.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- import org.apache.poi.xssf.usermodel.XSSFFont;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException;
- public class ExcelUtil {
- private final static String excel2003L = ".xls"; // 2003- 版本的excel
- private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
- /**
- * Excel导入
- */
- public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
- List<List<Object>> list = null;
- // 创建Excel工作薄
- Workbook work = getWorkbook(in,fileName);
- if ( null==work) {
- throw new Exception("创建Excel工作薄为空!");
- }
- Sheet sheet = null;
- Row row = null;
- Cell cell = null;
- list = new ArrayList<List<Object>>();
- // 遍历Excel中所有的sheet
- for (int i = 0; i < work.getNumberOfSheets(); i++) {
- sheet = work.getSheetAt(i);
- if (sheet == null) {
- continue;
- }
- // 遍历当前sheet中的所有行
- // 包涵头部,所以要小于等于最后一列数,这里也可以在初始值加上头部行数,以便跳过头部
- for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
- // 读取一行
- row = sheet.getRow(j);
- // 去掉空行和表头
- if (row == null || row.getFirstCellNum() == j) {
- continue;
- }
- // 遍历所有的列
- List<Object> li = new ArrayList<Object>();
- for (int y = 0; y <3; y++) { //由于个人原因被明确规定了导入数据只有三列,
- cell = row.getCell(y); //且 考虑到导入的excel单元格内可以不写东西得情况
- if(cell==null) //所以直接用 明确的数值来确定遍历的次数
- {li.add(null);} //这里添加了单元格中空白的判断,如果空白默认赋值为null,避免后面mybatis里面出现错误
- else
- li.add(getCellValue(cell));
- }
- list.add(li);
- }
- }
- return list;
- }
- /**
- * 描述:根据文件后缀,自适应上传文件的版本
- */
- public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
- Workbook wb = null;
- String fileType = fileName.substring(fileName.lastIndexOf("."));
- if (excel2003L.equals(fileType)) {
- wb = new HSSFWorkbook(inStr); // 2003-
- } else if (excel2007U.equals(fileType)) {
- wb = new XSSFWorkbook(inStr); // 2007+
- } else {
- throw new Exception("解析的文件格式有误!");
- }
- return wb;
- }
- /**
- * 描述:对表格中数值进行格式化
- */
- public static Object getCellValue(Cell cell) {
- Object value = null;
- DecimalFormat df = new DecimalFormat("0"); // 格式化字符类型的数字
- SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
- DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_STRING:
- value = cell.getRichStringCellValue().getString();
- break;
- case Cell.CELL_TYPE_NUMERIC:
- if ("General".equals(cell.getCellStyle().getDataFormatString())) {
- value = df.format(cell.getNumericCellValue());
- } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
- value = sdf.format(cell.getDateCellValue());
- } else {
- value = df2.format(cell.getNumericCellValue());
- }
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- value = cell.getBooleanCellValue();
- break;
- case Cell.CELL_TYPE_BLANK:
- value = "";
- break;
- default:
- break;
- }
- return value;
- }
ViewExcel.java
- package cn.gsp.common.utils;
- import java.io.OutputStream;
- import java.text.SimpleDateFormat;
- 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.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.springframework.web.servlet.view.document.AbstractExcelView;
- public class ViewExcel extends AbstractExcelView {
- private String[] titles;
- // 传入指定的标题头
- public ViewExcel(String[] titles) {
- this.titles = titles;
- }
- @Override
- protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
- HttpServletResponse response) throws Exception {
- // 获取数据
- List<Map<String, String>> list = (List<Map<String, String>>) model.get("excelList");
- // 在workbook添加一个sheet
- HSSFSheet sheet = workbook.createSheet();
- sheet.setDefaultColumnWidth(15);
- HSSFCell cell = null;
- // 遍历标题
- for (int i = 0; i < titles.length; i++) {
- // 获取位置
- cell = getCell(sheet, 0, i);
- setText(cell, titles[i]);
- }
- // 数据写出
- for (int i = 0; i < list.size(); i++) {
- // 获取每一个map
- Map<String, String> map = list.get(i);
- // 一个map一行数据
- HSSFRow row = sheet.createRow(i + 1);
- for (int j = 0; j < titles.length; j++) {
- // 遍历标题,把key与标题匹配
- String title = titles[j];
- // 判断该内容存在mapzhong
- if (map.containsKey(title)) {
- row.createCell(j).setCellValue(map.get(title));
- }
- }
- }
- // 设置下载时客户端Excel的名称
- String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xls";
- response.setContentType("application/vnd.ms-excel");
- response.setHeader("Content-disposition", "attachment;filename=" + filename);
- OutputStream ouputStream = response.getOutputStream();
- workbook.write(ouputStream);
- ouputStream.flush();
- ouputStream.close();
- }
- }
这里的导入指的是将excel文件上传,导入到数据库中,然后进行相应的规则应用(导入导出只是附带功能,但这里只记录导入导出的部分)
导出指的是将数据从数据库中导出,生成excel文件(目前正在想是否直接导出查询结果会更好?但感觉好麻烦。。。)
导入jsp部分
- <form method="post" enctype="multipart/form-data"
- action="${pageContext.request.contextPath }/seorder/import.action">
- <table>
- <tr>
- <td>上传文件:</td>
- <td><input id="upfile" type="file" name="upfile"
- class="btn btn-blue"></td>
- </tr>
- <tr>
- <td><input class="btn btn-blue" type="submit" value="提交"
- onclick="return checkData()"></td>
- </tr>
- </table>
- </form>
controller部分
- /**
- * excel表的导入
- */
- @RequestMapping("/import")
- public String impotr(HttpServletRequest request, Model model) throws Exception {
- // 获取上传的文件
- MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;
- MultipartFile file = multipart.getFile("upfile");
- if (file.isEmpty()) {
- JOptionPane.showMessageDialog(null, "上传文件为空!", "错误", JOptionPane.ERROR_MESSAGE);
- return "redirect:/seorder/list.action";
- } // 对上传文件是否为空做了一个判断,如果excel不存在弹出错误提示框页面重定向,存在导入excel
- InputStream in = file.getInputStream();
- // 数据导入
- seorderService.importExcelInfo(in, file);
- return "redirect:/seorder/pipei.action";
- }
- /**
- * 导入excel
- */
- public void importExcelInfo(InputStream in, MultipartFile file) throws Exception {
- List<List<Object>> listob = ExcelUtil.getBankListByExcel(in, file.getOriginalFilename());
- List<SeorderK> seorderList = new ArrayList<SeorderK>();
- // 遍历listob数据,把数据放到List中
- for (int i = 0; i < listob.size(); i++) {
- List<Object> ob = listob.get(i);
- SeorderK seorderK = new SeorderK();
- // 通过遍历实现把每一列封装成一个model中,再把所有的model用List集合装载
- seorderK.setFBillNo(String.valueOf(ob.get(0)));
- seorderK.setFMapNumber(String.valueOf(ob.get(1)));
- seorderK.setFEntrySelfS0164(String.valueOf(ob.get(2)));
- // seorderK.setFNumber(String.valueOf(ob.get(3))); 考虑到实际的情况让用户只用导入三个字段中的两个就可以了
- // seorderK.setFShortNumber(String.valueOf(ob.get(4)));
- // seorderK.setFName(String.valueOf(ob.get(5)));
- seorderList.add(seorderK);
- }
- // 批量插入
- seorderkDao.insertInfoBatch(seorderList);
- }
dao部分
- /**
- * 导入数据
- * @param seorderkList
- */
- public void insertInfoBatch(List<SeorderK> seorderkList);
dao的实现类xml
- <!-- 导入数据 -->
- <insert id="insertInfoBatch" parameterType="java.util.List">
- insert into gsp_seorderk_t (FBillNo, FMapNumber,FEntrySelfS0164)
- values
- <foreach collection="list" item="item" index="index"
- separator="," >
- (#{item.FBillNo}, #{item.FMapNumber}, #{item.FEntrySelfS0164})
- </foreach>
- </insert>
导入部分到此为止,接下来是导出的部分
导出jsp部分
- <form
- action="${pageContext.request.contextPath }/seorder/export.action">
- <button id="btnExport" type="submit" class="btn btn-blue">导出</button>
- </form>
controller部分
- /**
- * 导出excel2
- *
- * @param map
- * @return
- * @throws Exception
- */
- @RequestMapping("/export")
- public ModelAndView export(ModelMap map) throws Exception {
- List<Map<String, String>> list = seorderService.exportExcelInfo();
- String[] titles = { "订单编号", "客户代码", "新编码", "GSP长代码", "GSP短代码", "GSP名称" };
- ViewExcel excel = new ViewExcel(titles);
- map.put("excelList", list);
- return new ModelAndView(excel, map);
- }
- /**
- * 导出excel2
- *
- * @throws Exception
- */
- public List<Map<String, String>> exportExcelInfo() throws Exception {
- List<SeorderK> list = seorderkDao.pipei(null);
- List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
- for (SeorderK sk : list) {
- Map<String, String> map = new HashMap<String, String>();
- map.put("订单编号", sk.getFBillNo());
- map.put("客户代码", sk.getFMapNumber());
- map.put("新编码", sk.getFEntrySelfS0164());
- map.put("GSP长代码", sk.getFNumber());
- map.put("GSP短代码", sk.getFShortNumber());
- map.put("GSP名称", sk.getFName());
- mapList.add(map);
- }
- return mapList;
- }
dao部分
- /**
- * 导出excel
- * @param seorderkDate
- * @return
- */
- public List<SeorderK> selectApartInfo(String seorderkDate);
依据个人情况来写