把Excule导入，并把Excule中的数据解析出来，包装成对象的属性，保存在数据库中；
Excule中的数据：
1.web.xml的配置：
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <servlet> <servlet-name>Dispatcher</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:ApplicationContext.xml</param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>Dispatcher</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <!-- 文件上传 --> <!-- 1.在web.xml中添加listener 2.在web.xml中添加spring框架启动的加载的配置文件路径: --> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:ApplicationContext.xml</param-value> </context-param> </web-app>
2.ApplicationContext.xml中的配置
<!-- 支持上传文件 --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> </bean>
3.文件上传的JSP以及JS判断上传的文件是否为指定格式：
①：form表单
<form enctype="multipart/form-data" action="${paths}/upload.do" method="post" onchange="selectFile(this)"> <p style="font-size:16px;">请选择正确的excel文件上传</p> <input id="txt" class="input" type="text" disabled="disabled" value="文件域" name="txt"> <input type="button" onclick="file.click()" value="上传文件" onmousemove="file.style.pixelLeft=event.x-60;file.style.pixelTop=this.offsetTop;"> <input id="file1" class="files" type="file" hidefocus="" size="1" style="height:26px;" name="file" onchange="txt.value=this.value"> <p style="color:red;">支持的excel格式为：xls、xlsx、xlsb、xlsm、xlst！</p> <input class="btn btn_ok" type="submit" value="确认"> </form>
②：JS代码：
function selectFile(fnUpload){ var filename=document.getElementById("txt").value; var mime = filename.toLowerCase().substr(filename.lastIndexOf(".")); if(!(mime==".xls"||mime==".xlsx"||mime==".xlsb"||mime==".xlsm"||mime==".xlst")){ alert("请选择正确的格式上传"); } }
4.ExculeUtil类：
package com.bgiseq.Experiment_Center.controller; import java.io.File; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.CellFormat; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; public class ExculeUtils { /** * 下载Excule模板 * * @param output * @return * @throws IOException * @throws WriteException */ public static WritableWorkbook createTemplate(OutputStream output) throws IOException, WriteException { WritableWorkbook writableWorkbook = Workbook.createWorkbook(output); WritableSheet wsheet = writableWorkbook.createSheet("测试title", 0); CellFormat cf = writableWorkbook.getSheet(0).getCell(1, 0).getCellFormat(); WritableCellFormat wc = new WritableCellFormat(); // 设置居中 wc.setAlignment(Alignment.CENTRE); // 设置边框线 // wc.setBorder(Border.ALL, BorderLineStyle.THIN); wc.setBackground(jxl.format.Colour.GREEN);// 设置背景颜色 // Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, Label nc1 = new Label(0, 0, "药品编号", wc); Label nc0 = new Label(1, 0, "药品名称", wc); // 单元格的内容是z Label nc2 = new Label(2, 0, "药品厂家", wc); Label nc3 = new Label(3, 0, "备注", wc); Label nc4 = new Label(4, 0, "创建日期", wc); Label nc5 = new Label(5, 0, "修改日期", wc); Label nc6 = new Label(6, 0, "删除标志", wc); wsheet.addCell(nc0); wsheet.addCell(nc1); wsheet.addCell(nc2); wsheet.addCell(nc3); wsheet.addCell(nc4); wsheet.addCell(nc5); wsheet.addCell(nc6); return writableWorkbook; } /** * 解析Excule，并将解析出来的数据保存在list中
* JXL解析（要导入JXL包） * * @param file 传入一个Excule文件 * @return */ public static List<String[]> readEcxule(File file) { Sheet sheet; Workbook book; Cell cell1 = null; try { // file为要读取的excel文件名 book = Workbook.getWorkbook(file); // 获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....) sheet = book.getSheet(0); int columns = sheet.getColumns();//获取一共有多少列 int rows = sheet.getRows();//获取一共有多少行 List<String[]> list = new ArrayList<>(); // 获取左上角的单元格 // cell1 = sheet.getCell(0, 0); // System.out.println("标题："+cell1.getContents()); for (int i = 0; i < rows; i++) { String[] vals = new String[columns]; for (int j = 0; j < columns; j++) { // 获取每一行的单元格 cell1 = sheet.getCell(j, i);// （列，行） String contents = cell1.getContents(); //将每一行的数据添加的String[]中 vals[j] = contents; } list.add(vals); } book.close(); return list; } catch (Exception e) { e.printStackTrace(); } return null;
5.T_KitInfo对象类：
public class T_KitInfo { private String ID;//试剂盒编号 private String KitNO;// private String KitName;//试剂盒名称 private String Manufactor;//制造厂 private String Note;//票据 private Date CreateTime;//当前时间 private Date UpdateTime;//修改时间 private Character flag;//标记 //getter和setter方法 }
6.Controller类：
@RequestMapping(value = "/upload.do") public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) { String path = request.getSession().getServletContext().getRealPath("/WebContent/upload"); String fileName = file.getOriginalFilename(); try { File targetFile=null; if(fileName!=null||fileName!=""){ //用\.将文件名进行解析 String[] split = fileName.split("\."); String lastname = split[1]; //判断文件的后缀名是不是指定的格式 if("xls".equals(lastname)||"xlsx".equals(lastname)||"xlsb".equals(lastname)||"xlsm".equals(lastname)||"xlst".equals(lastname)){ targetFile = new File(path, fileName); if(!targetFile.exists()){ targetFile.mkdirs(); } //保存文件 file.transferTo(targetFile); //调用ExculeUtils类中的解析Excule的方法 List<String[]> list = ExculeUtils.readEcxule(targetFile); //遍历得到的list for (int i = 1; i < list.size(); i++) { //因为第一行数据为表头信息，所以不用获取到，i从1开始 String[] strings = list.get(i); //将获取到的信息设置为T_KitInfo的属性 T_KitInfo t=new T_KitInfo(); for (int j = 0; j < strings.length; j++) { String KitNO = strings[0];//药品编号 String KitName = strings[1];//药品名称 String Manufactor = strings[2];//药品厂家 String Note = strings[3];//备注 String createDate1 = strings[4];//创建日期 //T_KitInfo中的时间对象为Date对象，利用Date工具类对String进行解析，得到date对象 Date createDate = DateUtil.getDateFromString(createDate1); String updateDate1 = strings[5];//修改日期 Date updateDate = DateUtil.getDateFromString(updateDate1); t.setKitNO(KitNO); t.setKitName(KitName); t.setManufactor(Manufactor); t.setNote(Note); t.setCreateTime(createDate); t.setCreateTime(updateDate); } //调用service层的保存对象的方法 service.save(t); }
------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------
文件下载：
1.jsp信息：
<a class="import" href="${paths}/sjdcExcel">模板下载</a>
2.controller类：
@RequestMapping("sjdcExcel")
public void download(HttpServletRequest request, HttpServletResponse response) throws IOException, BiffException, WriteException {
String filename="试剂盒信息";
response.setHeader("Content-Disposition", "attachment; filename=SJHMessage.xls");
WritableWorkbook writableWorkbook = ExculeUtils.createTemplate(response.getOutputStream());
writableWorkbook.write();
writableWorkbook.close();
}