文件上传下载与Excel、数据表数据之间的转换（续）（Struts2操作jxl方式与Spring MVC操作poi方式）
原文请参考上一篇（仅struts2操作jxl方式）（无详细解析）
文章目录
Java操作jxl包使数据库数据导出到Excel、Excel数据导入到数据库：
java对于Excel的操作借助于jxl包，如下方法偏底层实现，内部细节复杂，SQL拼接等极易出错，比较适合写好之后作为工具类使用。但是方法参数过多，调用起来非常麻烦，在调用过程中也会存在SQL拼接等容易出错，每次不同表单需要不同编写方案，不具有泛型能力。
此处只是针对了简单的查询和插入不具备修改删除能力，当我们想要利用Excel修改数据库内容时以下方法便不再合适，或者重复上传Excel并试图插入数据库数据时（由于主键原因不允许重复插入）则会报错。
同时，文件上传下载不具备断点续传能力，不允许中断。
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.edu.db_util.JdbcPoolUtils;
public class DbToExcel {
/** * 该方法实现将数据库中的某数据表数据形成电子表Excel * * 一步步按SQL语法将SQL拼接起来，查询到数据后依次赋值到电子表格的每一个单元格内 * * @param table :数据表名字 * @param fieldList ：数据表字段名，采用字符串数组依次存放 * @param titles ：所形成的电子表表头字段信息，采用字符串数组存放 * @param condition：查询数据库的条件，将满足该条件的记录存放到电子表中 * @param order ：排序字段与排序方式（asc\desc） * @param file ：电子表名字 * @throws Exception */
public static void dBToExcel(String table, String[] fieldList,
String[] titles, String condition,
String order, String file)
throws Exception {
Connection conn = null;
PreparedStatement ps = null;
WritableWorkbook wwb = null;
WritableSheet ws = null;
conn = JdbcPoolUtils.getConnection();
String flist = "";
int fl = fieldList.length;
for (int i = 0; i < fl - 1; i++) {
flist += fieldList[i] + ",";
}
flist += fieldList[fieldList.length - 1];
String sql = "select " + flist + " from " + table + " where 1=1 ";
if (condition != null && !condition.equals("")) {
sql = sql + " and " + condition;
}
if (order != null && !order.equals("") ) {
sql = sql + " order by " + order;
}
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
//Excel的创建过程：首先创建一个数据表单对象，然后依次创建Sheet对象、Cell对象（将Excel对象化）（单元格或者行数等属性均从0开始计数）
//具体使用方式可以参考jxl包
wwb = Workbook.createWorkbook(new File(file));
ws = wwb.createSheet("sheet1", 0);
for (int i = 0; i < fl; i++) {
ws.addCell(new Label(i, 0, titles[i]));
}
int count = 1;
while (rs.next()) {
for (int j = 0; j < fl; j++) {
//同上New Lable操作，j表示改行所在单元格位置（从0开始计数），count表示所在行数（从0开始计数，但上已有标题行，故此处从1开始），rs中可作游标处理，每次从1开始计数）
ws.addCell(new Label(j, count, rs.getString(j + 1)));
}
//游标自动更新至下一位，Excel行数也要更新以存储下一个对象
count++;
}
wwb.write();
//最后Excel写入完毕后要及时关闭Workbook，使程序与Excel对象断开连接，与此同时，查询结果已经保存在Excel中，故关闭数据库连接
if (wwb != null) wwb.close();
JdbcPoolUtils.close(null, ps, conn);
}
/** * 该方法实现将电子表中的数据导入对应的数据库内 * * @param excelpath:电子表路径 * @param table ：数据库数据表名 * @param fieldList：数据库字段名串，在插入数据库中，各字段信息，且用逗号间隔， * @param columnCount ：要添加的字段的个数 * @throws Exception */
public static void excelToDb(String excelpath, String table,
String fieldList, int columnCount) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
Workbook workbook = null;
Sheet sheet = null;
conn = JdbcPoolUtils.getConnection();
String sql ="insert into " + table + " " + fieldList + " values (";
for (int i = 1; i < columnCount; i++) {
sql += "?,";
}
sql += "?) ";
System.out.println(sql);
ps = conn.prepareStatement(sql);
workbook = Workbook.getWorkbook(new File(excelpath));
sheet = workbook.getSheet(0);
int r = sheet.getRows();
for (int i = 1; i < r; i++) {
for (int j = 0; j < columnCount; j++)
//获取第i行第j列的数据（此处i从1开始表示跳过标题行）（第0行为标题行）
//void setString(int parameterIndex, String x) 将指定的参数设置为给定的Java String值，即为刚才的ps = conn.prepareStatement(sql);中的占位符参数赋值（注意下标从1开始）
ps.setString(j + 1, sheet.getCell(j, i).getContents());
//采用批处理方式进行数据插入
//void addBatch() throws SQLException向这个 PreparedStatement对象的一批命令添加一组参数。
//在Statement中：void addBatch(String sql) throws SQLException将给定的SQL命令添加到此Statement对象的当前命令列表中。 该列表中的命令可以通过调用方法executeBatch作为批处理执行。 注意：此方法无法在PreparedStatement或CallableStatement
ps.addBatch();
}
//int[] executeBatch() throws SQLException将一批命令提交到数据库以执行，并且所有命令都执行成功，返回一个更新计数的数组。 返回的数组的int元素被排序以对应于批次中的命令，这些命令根据它们添加到批处理的顺序进行排序。
ps.executeBatch();
workbook.close();
JdbcPoolUtils.close(null, ps, conn);
}
}
文件下载：
（Struts2：必须指定好相应的参数 ），先将数据库中的数据导出到Excel，然后再下载Excel文件：
package com.edu.action.card;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import com.edu.db_util.DbToExcel;
import com.opensymphony.xwork2.ActionSupport;
import com.opensymphony.xwork2.inject.Scope;
import com.opensymphony.xwork2.inject.Scoped;
@Namespace("/card")
@Scoped(Scope.REQUEST)
@ParentPackage("struts-default") //表示继承的父包
public class DownLoadAction extends ActionSupport {
private static final long serialVersionUID = 1L;
//以下4个属性必须提供getter方法
private String contentType;//指定下载文件的类型,默认值为 text/plain
private long contentLength;//被下载的文件的大小，以字节为单位
// 属性：contentDisposition：指定文件下载的处理方式，当为attachment（附件方式）会弹出文件保存对话框，是默认方式，
// 其格式是attachment;filename="${fileName}
private String contentDisposition;//指定下载文件的下载方式，并指定保存文件的默认文件名
private InputStream inputStream;//Action 中提供的文件的输入流。默认值为 inputStream
private String fileName="名片.xls"; //指定下载的文件名
public String getContentType() {
return contentType;
}
public long getContentLength() {
return contentLength;
}
public String getContentDisposition() {
return contentDisposition;
}
public InputStream getInputStream() {
return inputStream;
}
@Action( //表示请求的Action及处理方法
value="download",
results={
@Result(name="success",type="stream")}
)
public String executeDownLoad() throws Exception {
String[] fieldList={
"id","name","sex","department","mobile","phone","email","address","flag"};
String[] titles={
"序号","姓名","性别","单位","手机","电话","电子邮箱","地址","备注"};
String file="名片.xls";
HttpSession session=ServletActionContext.getRequest().getSession();
String condition=(String)session.getAttribute("condition");
String order=(String)session.getAttribute("order");
String sql="";
if (condition != null && !condition.equals("")) {
sql = sql + " (name like '%" + condition + "%'";
sql = sql + " or sex like '%" + condition + "%'";
sql = sql + " or department like '%" + condition + "%'";
sql = sql + " or mobile like '%" + condition + "%'";
sql = sql + " or phone like '%" + condition + "%'";
sql = sql + " or email like '%" + condition + "%'";
sql = sql + " or address like '%" + condition + "%')";
}
//确定各个成员变量的值，注意，这些值也可以在配置文件中配置，但一般在这里配置
contentType = "application/octet-stream";//指定为任意类型的文件
//指定下载后要保存的默认文件名,并通过编码转化，使之支持汉字文件名
String name=java.net.URLEncoder.encode(fileName, "UTF-8");
contentDisposition = "attachment;filename="+name;
ServletContext servletContext =ServletActionContext.getServletContext();
String fileName2 = servletContext.getRealPath("/download/"+file);
File downloadfile=new File(fileName2);
if(!downloadfile.exists()){
System.out.println("不存在");
downloadfile.getParentFile().mkdirs();
}
DbToExcel.dBToExcel("card", fieldList, titles, sql, order, fileName2);
/** * 通过inputstream传递给客户端（Action 中提供的文件的输入流，具体实现方式由Struts2内部实现，我们只负责配置好相应的下载参数等） */
//System.out.println(fis.available());//可以获取与之关联的文件的字节数
//inputStream.available();返回从该输入流中可以读取（或跳过）的字节数的估计值，而不会被下一次调用此输入流的方法阻塞。 下一个调用可能是同一个线程或另一个线程。 这个多个字节的单个读取或跳过将不会被阻塞，但可以读取或跳过较少的字节。
//注意，尽管一些实现InputStream将在流中返回的字节总数，但也有很多实现不会这样做。 使用此方法的返回值分配用于保存此流中的所有数据的缓冲区是绝对不正确的。
//参考链接：https://www.iteye.com/blog/hold-on-1017449
//https://www.cnblogs.com/fanhq/p/5233718.html
//文件下载中使用inputStream流获取文件大小: https://blog.csdn.net/qq_21104515/article/details/84061109
inputStream = new FileInputStream(fileName2);
contentLength = inputStream.available();
return SUCCESS;
}
}
上传文件
同时将上传的Excel文件中的数据解析插入到数据库中：
package com.edu.action.card;
import java.io.File;
import javax.servlet.ServletContext;
import org.apache.commons.io.FileUtils;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import com.edu.db_util.DbToExcel;
import com.opensymphony.xwork2.ActionSupport;
import com.opensymphony.xwork2.inject.Scope;
import com.opensymphony.xwork2.inject.Scoped;
@Namespace("/card")
@Scoped(Scope.REQUEST)
@ParentPackage("struts-default") //表示继承的父包
public class UploadAction extends ActionSupport {
private static final long serialVersionUID = 1L;
/** * 上***个文件方式，必须设置下面的3个属性，File属性名与提交页面的属性名一致， * 其他两项必须用该属性名开始并且名称 */
private File file;//上传文件对象（File属性名与提交页面的属性名一致）
private String fileContentType;//上传文件内容类型
private String fileFileName;//上传文件名
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
public String getFileContentType() {
return fileContentType;
}
public void setFileContentType(String fileContentType) {
this.fileContentType = fileContentType;
}
public String getFileFileName() {
return fileFileName;
}
public void setFileFileName(String fileFileName) {
this.fileFileName = fileFileName;
}
@Action( //表示请求的Action及处理方法
value="upload",
results={
@Result(name="success",location="/find",type="redirectAction")
}
)
public String upload() throws Exception {
//获取服务器容器对象
ServletContext servletContext = ServletActionContext.getServletContext();
//获取实际存放上传文件的文件夹（项目根目录下的文件夹upload），若不存在，自动创建该文件夹
String dir = servletContext.getRealPath("/upload");
System.out.println(dir);
File saveFile=new File(dir,fileFileName);
FileUtils.copyFile(file,saveFile);
DbToExcel.excelToDb(dir+"/"+fileFileName, "card",
"(name,sex,department,mobile,phone,email,address)", 7);
return "success";
}
}
上传文件视图：
<s:form action="/card/upload" method="post"
enctype="multipart/form-data">
<s:file name="file" label="提交文件"></s:file>
<s:submit value="提交"></s:submit>
</s:form>
Java操作poi包使List列表数据导出到Excel：
（可先查询数据库将数据保存到List列表，然后再将该列表数据导出到Excel实现数据库数据->Excel）
封装成模板化，解放双手，不必再去修改代码文件，直接修改Excel配置文件的对应关系即可，更加直观，不易出错；当然，我们要想精确实现某一个SQL功能仍然需要我们自己手动去实现，借鉴java操作jxl实现Excel数据导入数据库方法中的SQL实现即可。
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.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.cglib.beans.BeanMap;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public abstract class EXCElController extends BaseController {
public static int EXCEL_MAX = 65536;
//关于Excel有xls和xlsx版本
public static String _2003 = "xls";
public static String _2007 = "xlsx";
public String defFmt = "yyyy/MM/dd HH:mm:ss";
//@SuppressWarnings，表示警告抑制，告诉编译器不用提示相关的警告信息。"rawtypes"，是说传参时也要传递带泛型的参数,那么警告抑制后是告诉编译器不用提示使用基本类型参数时相关的警告信息。
//可参考：https://blog.csdn.net/xiehuanhuan1991/article/details/79615960
//参数表示：模板文件名.xls，要转换成Excel数据的数据列表，要保存的Excel文件名，HttpServletResponse
@SuppressWarnings("rawtypes")
protected void downExportExcel(String tplUrl, List list, String excelName, HttpServletResponse resp) {
downloadExcel(exportExcel(tplUrl, list), excelName, resp);
}
protected void downloadExcel(Workbook wb, String excelName, HttpServletResponse resp) {
try {
//将UTF-8编码变成iso-8859-1类型编码
//public byte[] getBytes(String charsetName) throws UnsupportedEncodingException使用命名的字符集将此String编码为字节序列，将结果存储到新的字节数组中。
//String(byte[] bytes, String charsetName) 构造一个新的String由指定用指定的字节的数组解码charset 。
//可参考：https://blog.csdn.net/pseudonym_/article/details/71170684
excelName = new String(excelName.getBytes("UTF-8"), "iso-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
resp.setContentType("application/octet-stream"); //指定为任意类型的文件
resp.setHeader("Content-Type", "application/vnd.ms-excel");
resp.addHeader("Content-Disposition", "attachment;fileName=" + excelName);
resp.setCharacterEncoding("UTF-8");
OutputStream os = null;
try {
//将导出后的Excel表格对象直接通过outputstream输出到客户端，即满足客户端下载
os = resp.getOutputStream();
wb.write(os);
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
//Java泛型写法：https://www.runoob.com/java/java-generics.html
/* 所有泛型方法声明都有一个类型参数声明部分（由尖括号分隔），该类型参数声明部分在方法返回类型之前（在下面例子中的 <E>）。 每一个类型参数声明部分包含一个或多个类型参数，参数间用逗号隔开。一个泛型参数，也被称为一个类型变量，是用于指定一个泛型类型名称的标识符。 类型参数能被用来声明返回值类型，并且能作为泛型方法得到的实际参数类型的占位符。 泛型方法体的声明和其他方法一样。注意类型参数只能代表引用型类型，不能是原始类型（像 int、double、char 等）。 java 中泛型标记符： E - Element (在集合中使用，因为集合中存放的是元素) T - Type（Java 类） K - Key（键） V - Value（值） N - Number（数值类型） ？ - 表示不确定的 java 类型 */
public static <T> Map<String, Object> obj2Map(T bean) {
Map<String, Object> map = new HashMap<String, Object>();
if (bean != null) {
//此处采用了BeanMap，出自Spring5，将JavaBean对象映射为BeanMap对象
//官方介绍： A -based view of a JavaBean. The default set of keys is the union of all property names (getters or setters). An attempt to set a read-only property will be ignored, and write-only properties will be returned as . Removal of objects is not a supported (the key set is fixed).
BeanMap beanMap = BeanMap.create(bean);
for (Object key : beanMap.keySet()) {
map.put(key + "", beanMap.get(key));
}
}
return map;
}
//通过该文件名找到该文件，然后将其解析成一个Workbook对象
public static Workbook getTplWorkbook(String tplUrl) {
//通过该类的类加载器找到指定资源（conf文件夹下）并返回该指定资源的输入流
//ClassLoader getClassLoader() 返回该类或接口的类加载器。
//InputStream getResourceAsStream(String name) 返回用于读取指定资源的输入流。
InputStream is = EXCElController.class.getClassLoader().getResourceAsStream(tplUrl);
Workbook wb = null;
try {
if (is == null) {
is = new FileInputStream(tplUrl);
}
wb = new HSSFWorkbook(is);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return wb;
}
//获取模板中对应数据库数据表参数名开始的行数
//即['id'] ['age'] ['username'] ['money'] ['ctime'] ['sex'] ['idcard'] ['name']所在行数
private int getClnRow(Sheet sheet) {
int clnRow = 0;
for (int i = 0; i < 10; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
Cell clnCell = row.getCell(0);
if (clnCell != null) {
String cv = clnCell.getStringCellValue();
if (cv != null && cv.contains("['")) {
clnRow = i;
break;
}
}
}
return clnRow;
}
//将数据库数据表参数名封装成一个List列表
private List<String> getTplClnList(Sheet sheet, int clnRow) {
Row row1 = sheet.getRow(clnRow);
if (row1 == null) {
return null;
}
List<String> r1cells = new ArrayList<String>();
for (Cell cell : row1) {
String cv = cell.getStringCellValue();
if (cv == null) {
cv = "";
} else {
//去除特殊符号等（按模板规范写法不会出现此类状况）
if (cv.contains("{'")) {
int idx = cv.indexOf("{'");
String temp = cv;
//String substring(int beginIndex, int endIndex) 返回一个字符串，该字符串是此字符串的子字符串。 子串开始于指定beginIndex并延伸到字符索引endIndex - 1 。 因此，子串的长度为endIndex-beginIndex （简单来讲即左闭右开区间）
cv = temp.substring(0, idx);
defFmt = temp.substring(idx + 2, temp.length() - 2);
}
cv = cv.substring(2, cv.length() - 2);
}
r1cells.add(cv);
}
return r1cells;
}
@SuppressWarnings("rawtypes")
public Workbook exportExcel(String tplUrl, List list) {
Workbook wb = null;
try {
//首先尝试解析模板得到一个Workbook对象
wb = getTplWorkbook(tplUrl);
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
//获取模板中对应数据库数据表参数名开始的行数
int clnRow = getClnRow(sheet);
//将数据库数据表参数名封装成一个List列表
List<String> r1cells = getTplClnList(sheet, clnRow);
//去除数据库数据表参数名所在模板行（保留标题行）
sheet.removeRow(sheet.getRow(clnRow));
int len = Math.min(EXCEL_MAX, list.size());
for (int idx = 0; idx < len; idx++) {
//将List列表中的每个对象中的属性分别解析出来
Map<String, Object> map = obj2Map(list.get(idx));
HSSFRow row = sheet.createRow(idx + clnRow);
//将list列表内每个对象的每个属性值且Excel模板中也存在的属性添加到新的Excel表中
for (String key : map.keySet()) {
if (r1cells.contains(key)) {
HSSFCell cell = row.createCell(r1cells.indexOf(key));
Object value = map.get(key);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof Date) {
//判别属性类型，并赋予不同定义与格式
CellStyle style = wb.createCellStyle();
style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(defFmt));
cell.setCellValue(new Date()); //此处仅仅为Excel导出时间，并非源数据库内挂号时间（小bug）
//SimpleDateFormat可以将String转为Date,也可以Date转为String
//Date转为String： SimpleDateFormat: public final String format(Date date)将日期格式化成日期/时间字符串。参数 date - 要格式化为时间字符串的时间值。
//bean.setDatetime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(bean.getCtime())); //setDatetime为字符串类型的时间表示 bean.getCtime()返回为Date类型的时间
//String转为Date： DateFormat： Date parse(String source) 从给定字符串的开始解析文本以生成日期。（调用SimpleDateFormat中的parse(source, pos);方法）
//SimpleDateFormat extends DateFormat 实现了public Date parse(String text, ParsePosition pos)
//参考手动实现格式调整并调用JDK具体实现：https://blog.csdn.net/PiaoMiaoXiaodao/article/details/84568938
cell.setCellStyle(style);
} else if (value instanceof Integer) {
cell.setCellValue((int) value);
} else if (value instanceof Float) {
cell.setCellValue((float) value);
} else if (value instanceof Double) {
cell.setCellValue((double) value);
} else if (value instanceof BigDecimal) {
//double doubleValue() 将此 BigDecimal转换为 double 。
cell.setCellValue(((BigDecimal) value).doubleValue());
} else {
//将true、false转化为“是、否”
String val = value.toString();
if (Boolean.TRUE.toString().equals(val.toLowerCase())) {
cell.setCellValue("是");
} else if (Boolean.FALSE.toString().equals(val.toLowerCase())) {
cell.setCellValue("否");
} else {
cell.setCellValue(val);
}
}
}
}
}
} catch (Exception e) {
wb = null;
}
return wb;
}
}
方法调用方式：
@RequestMapping("/out")
public void out(int pid,HttpServletResponse resp) {
List<PnumBean> retList=pnumMapper.list(pid);
PatientBean patient=patientMapper.single(pid);
for(PnumBean bean:retList) {
//foreach循环
bean.setName(patient.getName());
bean.setIdcard(patient.getIdcard());
bean.setSex(patient.getSex());
bean.setDatetime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(bean.getCtime()));
}
//参数分别为：模板.xls，待转换List表单，转换后Excel名称，HttpServletResponse
//其中HttpServletResponse的必要性（获取outputstream，方便直接下载）：
/* os = resp.getOutputStream(); * wb.write(os); * os.flush(); */
downExportExcel("helloworld.xls", retList, patient.getName()+"的历史病例.xls", resp);
}
京公网安备 11010502036488号