java后台导出Excel封装通用javaBean
需要下载poi依赖jar包，配合web端下载使用
jar包和示例代码demo下载地址
一、基础工具
工具代码
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil
{
public static List<Map<String, String>> ExcelToList(String filePath)
throws Exception
{
DecimalFormat df = new DecimalFormat("#.###");
List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
String filetype = filePath.substring(filePath.lastIndexOf(".") + 1);
FileInputStream fi = null;
try
{
fi = new FileInputStream(filePath);
}
catch (FileNotFoundException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (fi == null)
throw new Exception("文件不存在");
// xls文件
if ("xls".equals(filetype.toLowerCase()))
{
try
{
HSSFWorkbook wookbook = new HSSFWorkbook(fi);
HSSFSheet sheet = wookbook.getSheet("Sheet1");
if (sheet == null)
{
throw new RuntimeException("请将工作目录修改为：Sheet1");
}
int rows = sheet.getPhysicalNumberOfRows();
// 获取标题行
HSSFRow title = sheet.getRow(0);
int index = title.getFirstCellNum();
int rowcount = title.getLastCellNum();
for (int i = 1; i < rows; i++)
{
HSSFRow row = sheet.getRow(i);
if (isBlankRow(row, index, rowcount))
continue;
if (row != null)
{
Map<String, String> map = new TreeMap<String, String>();
int cells = title.getPhysicalNumberOfCells();
for (int j = 0; j < cells; j++)
{
String value = "";
HSSFCell cell = row.getCell(j);
if (cell != null)
{
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
Double oneD = cell.getNumericCellValue();
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// value += cell.getStringCellValue().trim();
value += df.format(oneD);
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue().trim();
break;
default:
value = "";
break;
}
}
if (title.getCell(j) != null && !"".equals(title.getCell(j)))
{
map.put(title.getCell(j).getStringCellValue().trim(), value);
}
}
mapList.add(map);
}
}
}
catch (FileNotFoundException e)
{
// TODO Auto-generated catch block
throw new RuntimeException(e.toString());
}
catch (IOException e)
{
// TODO Auto-generated catch block
throw new RuntimeException(e.toString());
}
catch (Exception e)
{
throw new RuntimeException(e.toString());
}
}
else if ("xlsx".equals(filetype.toLowerCase()))
{
// xlsx文件
try
{
XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
XSSFSheet sheet = wookbook.getSheet("Sheet1");
if (sheet == null)
{
throw new RuntimeException("请将工作目录修改为：Sheet1");
}
int rows = sheet.getPhysicalNumberOfRows();
// 获取标题行
XSSFRow title = sheet.getRow(0);
int index = title.getFirstCellNum();
int rowcount = title.getLastCellNum();
for (int i = 1; i < rows; i++)
{
XSSFRow row = sheet.getRow(i);
if (isBlankRow(row, index, rowcount))
continue;
if (row != null)
{
Map<String, String> map = new TreeMap<String, String>();
int cells = title.getPhysicalNumberOfCells();// 是获取不为空的列个数
for (int j = 0; j < cells; j++)
{
String value = "";
XSSFCell cell = row.getCell(j);
if (cell != null)
{
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA:// 表达式类型
break;
case HSSFCell.CELL_TYPE_NUMERIC:// 数值类型
Double oneD = cell.getNumericCellValue();
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// value += cell.getStringCellValue().trim();
value += df.format(oneD);
break;
case HSSFCell.CELL_TYPE_STRING:// 字符串类型
value += cell.getStringCellValue().trim();
break;
default:
value = "";
break;
}
}
if (title.getCell(j) != null && !"".equals(title.getCell(j)))
{
map.put(title.getCell(j).getStringCellValue().trim(), value);
}
}
mapList.add(map);
}
}
}
catch (FileNotFoundException e)
{
e.printStackTrace();
// TODO Auto-generated catch block
throw new RuntimeException(e.toString());
}
catch (IOException e)
{
e.printStackTrace();
// TODO Auto-generated catch block
throw new RuntimeException(e.toString());
}
catch (Exception e)
{
e.printStackTrace();
// TODO Auto-generated catch block
throw new RuntimeException(e.toString());
}
}
if (fi != null)
fi.close();
return mapList;
}
public static boolean isBlankRow(HSSFRow row, int index, int rowCount)
{
if (row == null)
return true;
for (int i = index; i < rowCount; i++)
{
if (row.getCell(i) == null)
{
continue;
}
row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
if (row.getCell(i) != null && !"".equals(row.getCell(i).getStringCellValue().trim()))
{
return false;
}
}
return true;
}
public static boolean isBlankRow(XSSFRow row, int index, int rowCount)
{
if (row == null)
return true;
for (int i = index; i < rowCount; i++)
{
if (row.getCell(i) == null)
{
continue;
}
row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
if (row.getCell(i) != null && !"".equals(row.getCell(i).getStringCellValue().trim()))
{
return false;
}
}
return true;
}
}
二、简单封装代码
封装代码示例
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
/**
* 利用开源组件POI3.0.2动态导出EXCEL文档
*
* @version v1.0
* @param <T>
* 应用泛型，代表任意一个符合javabean风格的类
* 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
* byte[]表jpg格式的图片数据
*/
public class ExportExcel<T>
{
public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
public void exportExcel(String title, Collection<T> dataset, OutputStream out)
{
exportExcel(title, null, dataset, out, "yyyy-MM-dd");
}
public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out)
{
exportExcel(title, headers, dataset, out, "yyyy-MM-dd");
}
public void exportExcel(String[] headers, Collection<T> dataset, OutputStream out, String pattern)
{
exportExcel("EXCEL文档", headers, dataset, out, pattern);
}
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param title
* 表格标题名
* @param headers
* 表格属性列名数组
* @param dataset
* 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out
* 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern
* 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern)
{
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("Sheet1");
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short)15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short)12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
//生成字体
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
// 把字体应用到当前的样式
style2.setFont(font2);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext())
{
index++;
row = sheet.createRow(index);
T t = (T)it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try
{
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value == null)
{
textValue = "";
}
else if (value instanceof Boolean)
{
boolean bValue = (Boolean)value;
textValue = "男";
if (!bValue)
{
textValue = "女";
}
}
else if (value instanceof Date)
{
Date date = (Date)value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
}
else if (value instanceof byte[])
{
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short)(35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[])value;
HSSFClientAnchor anchor =
new HSSFClientAnchor(0, 0, 1023, 255, (short)6, index, (short)6, index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
else
{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null)
{
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches())
{
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
}
else
{
HSSFRichTextString richString = new HSSFRichTextString(textValue);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}
catch (SecurityException e)
{
e.printStackTrace();
}
catch (NoSuchMethodException e)
{
e.printStackTrace();
}
catch (IllegalArgumentException e)
{
e.printStackTrace();
}
catch (IllegalAccessException e)
{
e.printStackTrace();
}
catch (InvocationTargetException e)
{
e.printStackTrace();
}
finally
{
// 清理资源
}
}
}
try
{
workbook.write(out);
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
三、进行二次封装，增加代码可读性，接口调用方便
工具代码示例
package com.hms.common.util;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
public class GenerateToExcelBean
{
private String[] tableHeards;// 表格头部名称必须与数据保持顺序
private List list;// 数据集合
private String filename;// 文件名
private String filepath;// 文件保存路径地址
/**
* 执行文件导出,默认系统发布路径，并返回文件保存路径
*
* @param tableHeards表格头部
* @param list数据
* @param filename文件名称
* @return filepatch文件保存的路径
*
* @auther zhuteng
* @time 2019年7月26日
*/
public String GenerateToExcel(String[] tableHeards, List list, String filename) throws Exception
{
if (tableHeards == null || tableHeards.length <= 0)
{
throw new Exception("无文件头名称!");
}
if (filename == null || "".equals(filename))
{
throw new Exception("无文件名称!");
}
this.tableHeards = tableHeards;
this.list = list;
this.filename = filename.split("\\.")[0] + ".xls";// 文件后缀初始化
// 临时存储至Tomcat webapps files发布路径
String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
StringBuffer strPath = new StringBuffer(",webapps,files,");
strPath.append("rubbish_file");
StringBuffer docsPath = new StringBuffer(System.getProperty("catalina.base"));
docsPath.append(strPath.toString().replace(',', File.separatorChar));
// 拼接路径
StringBuffer filePath = new StringBuffer(docsPath.toString());
filePath.append(FILE_SEPARATOR).append(this.filename);
this.filepath = filePath.toString();
// 执行文件保存
GenerateToExcel(this.tableHeards, this.list, this.filename, this.filepath);
return this.filepath;
}
/**
* 执行文件导出,默认系统发布路径，同时直接下载文件，删除临时文件
*
* @param tableHeards表格头部
* @param list数据
* @param filename文件名称
* @return HttpServletResponse用户请求返回对象
*
* @auther zhuteng
* @time 2019年7月26日
*/
public HttpServletResponse GenerateToExcel(String[] tableHeards, List list, String filename,
HttpServletResponse response)
throws Exception
{
if (response == null)
{
throw new Exception("用户对象为空!");
}
if (tableHeards == null || tableHeards.length <= 0)
{
throw new Exception("无文件头名称!");
}
if (filename == null || "".equals(filename))
{
throw new Exception("无文件名称!");
}
this.tableHeards = tableHeards;
this.list = list;
this.filename = filename.split("\\.")[0] + ".xls";// 文件后缀初始化
// 临时存储至Tomcat webapps files发布路径
String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
StringBuffer strPath = new StringBuffer(",webapps,files,");
strPath.append("rubbish_file");
StringBuffer docsPath = new StringBuffer(System.getProperty("catalina.base"));
docsPath.append(strPath.toString().replace(',', File.separatorChar));
// 拼接路径
StringBuffer filePath = new StringBuffer(docsPath.toString());
filePath.append(FILE_SEPARATOR).append(this.filename);
this.filepath = filePath.toString();
// 直接下载，删除临时文件
return downloadFileAndDropFile(response);
}
/**
* 执行文件导出
*
* @param tableHeards表格头部
* @param list数据
* @param filename文件名称
* @param filepatch文件保存地址路径_文件名_后缀结尾
* @return
*
* @auther zhuteng
* @time 2019年7月26日
*/
public void GenerateToExcel(String[] tableHeards, List list, String filename, String filepath) throws Exception
{
if (tableHeards == null || tableHeards.length <= 0)
{
throw new Exception("无文件头名称!");
}
if (filename == null || "".equals(filename))
{
throw new Exception("无文件名称!");
}
if (filepath == null || "".equals(filepath))
{
throw new Exception("无文件路径!");
}
this.tableHeards = tableHeards;
this.list = list;
this.filename = filename.split("\\.")[0] + ".xls";// 文件后缀初始化
this.filepath = filepath;
// 保存文件
ExportExcel ex = new ExportExcel();// 初始化
// 打开输出流
OutputStream os = new FileOutputStream(this.filepath);
try
{
ex.exportExcel(this.filename, this.tableHeards, this.list, os);
}
catch (Exception e)
{
throw new Exception("创建Excel异常！");
}
finally
{
// 关闭输出流
os.close();
}
}
/**
* 执行文件导出,并且删除源文件
*
* @param tableHeards表格头部
* @param list数据
* @param filename文件名称
* @param filepatch文件保存地址路径_文件名_后缀结尾
* @return HttpServletResponse用户请求返回对象
*
* @auther zhuteng
* @time 2019年7月26日
*/
public HttpServletResponse GenerateToExcel(String[] tableHeards, List list, String filename, String filepath,
HttpServletResponse response) throws Exception
{
if (response == null)
{
throw new Exception("用户对象为空!");
}
if (tableHeards == null || tableHeards.length <= 0)
{
throw new Exception("无文件头名称!");
}
if (filename == null || "".equals(filename))
{
throw new Exception("无文件名称!");
}
if (filepath == null || "".equals(filepath))
{
throw new Exception("无文件路径!");
}
this.tableHeards = tableHeards;
this.list = list;
this.filename = filename.split("\\.")[0] + ".xls";// 文件后缀初始化
this.filepath = filepath;
// 保存文件
ExportExcel ex = new ExportExcel();// 初始化
// 打开输出流
OutputStream os = new FileOutputStream(this.filepath);
try
{
ex.exportExcel(this.filename, this.tableHeards, this.list, os);
}
catch (Exception e)
{
throw new Exception("创建Excel异常！");
}
finally
{
// 关闭输出流
os.close();
}
// 直接下载，删除临时文件
return downloadFileAndDropFile(response);
}
/**
* 执行文件下载，并且删除临时保存的文件
*
* @return HttpServletResponse用户请求返回对象
*
* @Auther zhuteng
* @time 2019年7月26日
*/
public HttpServletResponse downloadFileAndDropFile(HttpServletResponse response) throws Exception
{
if (this.filename == null || "".equals(this.filename))
{
throw new Exception("无文件名称!");
}
if (this.filepath == null || "".equals(this.filepath))
{
throw new Exception("无文件路径!");
}
if (response == null)
{
throw new Exception("用户对象为空!");
}
this.filename = this.filename.split("\\.")[0] + ".xls";// 文件后缀初始化
this.filename = new String(this.filename.getBytes("UTF-8"), "ISO-8859-1");// 中文转码
// 使用浏览器下载方式下载文件
response.setContentType("text/html; charset=UTF-8"); // 设置编码字符
response.setContentType("application/octet-stream"); // 设置内容类型为下载类型
response.setHeader("Content-disposition", "attachment;filename=" + this.filename);// 设置下载的压缩文件名称
OutputStream out = response.getOutputStream(); // 创建页面返回方式为输出流，会自动弹出下载框
// 将打包后的文件写到客户端，输出的方法同上，使用缓冲流输出
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(this.filepath));
byte[] buff = new byte[bis.available()];
bis.read(buff);
bis.close();
out.write(buff);// 输出数据文件
out.flush();// 释放缓存
out.close();// 关闭输出流
// 删除文件
File file = new File(this.filepath);
// 路径为文件且不为空则进行删除
if (file.isFile() && file.exists())
{
file.delete();
}
return response;
}
/**
* 执行文件下载，自定义文件名称和文件路径，并且删除临时保存的文件
*
* @return HttpServletResponse用户请求返回对象
*
* @Auther zhuteng
* @time 2019年7月26日
*/
public HttpServletResponse downloadFileAndDropFile(String fileName, String filePath, HttpServletResponse response)
throws Exception
{
if (response == null)
{
throw new Exception("用户对象为空!");
}
if (fileName == null || "".equals(fileName))
{
throw new Exception("无文件名称!");
}
if (filePath == null || "".equals(filePath))
{
throw new Exception("无文件路径!");
}
this.filepath = filePath;
this.filename = fileName.split("\\.")[0] + ".xls";// 文件后缀初始化
this.filename = new String(this.filename.getBytes("UTF-8"), "ISO-8859-1");// 中文转码
// 使用浏览器下载方式下载文件
response.setContentType("text/html; charset=UTF-8"); // 设置编码字符
response.setContentType("application/octet-stream"); // 设置内容类型为下载类型
response.setHeader("Content-disposition", "attachment;filename=" + this.filename);// 设置下载的压缩文件名称
OutputStream out = response.getOutputStream(); // 创建页面返回方式为输出流，会自动弹出下载框
// 将打包后的文件写到客户端，输出的方法同上，使用缓冲流输出
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(this.filepath));
byte[] buff = new byte[bis.available()];
bis.read(buff);
bis.close();
out.write(buff);// 输出数据文件
out.flush();// 释放缓存
out.close();// 关闭输出流
// 删除文件
File file = new File(this.filepath);
// 路径为文件且不为空则进行删除
if (file.isFile() && file.exists())
{
file.delete();
}
return response;
}
}
测试代码初始化
package com.hms.common.util;
import java.math.BigDecimal;
import java.util.Date;
public class ProductBarCodeBean
{
private String usetime = "2019-07-27";// 使用日期，同operatetime，用于打印模板输出
private String p_name = "张三";
private String p_bed_name = "13";
private String patientnumber = "190712152";
private String anotherproductname = "接骨螺钉";// 耗材名称
private String productnorms = "13mm";// 规格
private String productstyles = "直径1.5mm";// 型号
private String transactioncode = "CN12645121";
private String factoryname = "山东威高";
private BigDecimal supplyprice = new BigDecimal("1300");// 耗材供货价
private BigDecimal counts = new BigDecimal(3);// 库存数量
private BigDecimal price = this.supplyprice.multiply(this.counts);// 金额
private String companyname = "山东威高医疗科技股份有限公司";
private String registrationcard = "国械注进2016812132";
private String parturitionbatch = "JAR102120";// 批号
private Date effectivetime = new Date();// 失效日期
private String departmentname = "手术室";
public String getUsetime()
{
return usetime;
}
public void setUsetime(String usetime)
{
this.usetime = usetime;
}
public String getP_name()
{
return p_name;
}
public void setP_name(String p_name)
{
this.p_name = p_name;
}
public String getP_bed_name()
{
return p_bed_name;
}
public void setP_bed_name(String p_bed_name)
{
this.p_bed_name = p_bed_name;
}
public String getPatientnumber()
{
return patientnumber;
}
public void setPatientnumber(String patientnumber)
{
this.patientnumber = patientnumber;
}
public String getAnotherproductname()
{
return anotherproductname;
}
public void setAnotherproductname(String anotherproductname)
{
this.anotherproductname = anotherproductname;
}
public String getProductnorms()
{
return productnorms;
}
public void setProductnorms(String productnorms)
{
this.productnorms = productnorms;
}
public String getProductstyles()
{
return productstyles;
}
public void setProductstyles(String productstyles)
{
this.productstyles = productstyles;
}
public String getTransactioncode()
{
return transactioncode;
}
public void setTransactioncode(String transactioncode)
{
this.transactioncode = transactioncode;
}
public String getFactoryname()
{
return factoryname;
}
public void setFactoryname(String factoryname)
{
this.factoryname = factoryname;
}
public BigDecimal getSupplyprice()
{
return supplyprice;
}
public void setSupplyprice(BigDecimal supplyprice)
{
this.supplyprice = supplyprice;
}
public BigDecimal getCounts()
{
return counts;
}
public void setCounts(BigDecimal counts)
{
this.counts = counts;
}
public BigDecimal getPrice()
{
return price;
}
public void setPrice(BigDecimal price)
{
this.price = price;
}
public String getCompanyname()
{
return companyname;
}
public void setCompanyname(String companyname)
{
this.companyname = companyname;
}
public String getRegistrationcard()
{
return registrationcard;
}
public void setRegistrationcard(String registrationcard)
{
this.registrationcard = registrationcard;
}
public String getParturitionbatch()
{
return parturitionbatch;
}
public void setParturitionbatch(String parturitionbatch)
{
this.parturitionbatch = parturitionbatch;
}
public Date getEffectivetime()
{
return effectivetime;
}
public void setEffectivetime(Date effectivetime)
{
this.effectivetime = effectivetime;
}
public String getDepartmentname()
{
return departmentname;
}
public void setDepartmentname(String departmentname)
{
this.departmentname = departmentname;
}
}
四、代码测试
4.1测试代码示例：
import java.util.ArrayList;
import java.util.List;
import com.hms.common.util.GenerateToExcelBean;
import com.hms.common.util.ProductBarCodeBean;
public class MainTest
{
public static void main(String[] args) throws Exception
{
String filename = "测试";// 文件名称
String filepatch = "F:\\Desktop\\测试.xls";// 保存路径
// Excel表头
String[] headers = { "手术日期", "患者姓名", "床号", "住院号", "耗材名称", "耗材规格", "耗材型号", "中标编码", "生产厂家", "单价", "数量", "金额",
"经销公司", "注册证号", "生产批号", "失效日期", "使用科室" };
List<ProductBarCodeBean> productUseList = new ArrayList<ProductBarCodeBean>();// 耗材使用信息
for (int i = 0; i < 50; i++)
{
productUseList.add(new ProductBarCodeBean());
}
new GenerateToExcelBean().GenerateToExcel(headers, productUseList, filename, filepatch);
System.out.println("导出成功");
}
}
测试结果
4.2web服务下载
前段发起下载请求,请求必须使用get方法下载文件
var obj=new Object();//查询条件
obj.exporttype=2;
obj= $.postJsonParam_To_GetParam(obj);//将json对象转为字符串【自己封装的方法，参考地址：(https://blog.csdn.net/u010636072/article/details/97519540)】
var URL="../reportform/reportform_ExportToExcel.do"+obj;
window.location.replace(URL);//下载文件
控制层调用service接口,service逻辑处理
// 1.拼接头部
String[] table_Heards = { "状态", "耗材唯一码", "产品唯一码", "耗材名称", "耗材名称简拼", "耗材别名", "耗材别名简拼", "耗材规格",
"耗材型号", "计数单位", "包装单位", "包装单位", "可使用次数", "申请价", "协议价", "中标价", "售价", "加价率", "是否计费", "招标方式",
"招标方式", "注册证号", "老注册证号", "注册证效期", "寄售管理", "条码管理", "是否高值", "供货方式", "是否二级库管理", "子库房名称",
"库位码", "是否his映射", "his收费名称", "his收费规格", "his售价", "是否第三方映射", "是否已做医保对照", "数据来源", "运输条件",
"冷链运输温度", "品牌", "产地", "产品性质", "产品类型", "一级类别", "二级类别", "三级类别", "四级类别", "68编码", "配送企业",
"生产厂家", "上级代理商" };
// 2.查询数据
List<ProductDetailsBean> productDetailsList = new ArrayList<ProductDetailsBean>();
productDetailsList = hcompanyproductdao.get_ProductDetailBean_ByInfo(oneBean);
// 3.保存excel文件,并获取文件保存地址
GenerateToExcelBean toExcelBean = new GenerateToExcelBean();// excel导出javaBean
// 4.保存文件,将文件保存到tomcat的临时文件内
String filename = "耗材规格明细";
String filePath = toExcelBean.GenerateToExcel(table_Heards, productDetailsList, filename);
// 5.返回文件路径
Map<String, String> pathMap = new HashMap<String, String>();
pathMap.put("fileName", filename);
pathMap.put("filePath", filePath);
return pathMap;
控制层下载文件
Map<String, String> pathMap = new HashMap<String, String>();//保存文件名称和路径
pathMap = hmaterialreportformservice.reportform_ExportToExcel(infoBean, userBean);//查询数据，接收文件回参
String filePath = pathMap.get("filePath");// 文件路径
String fileName = pathMap.get("fileName");//文件名称
GenerateToExcelBean toExcelBean = new GenerateToExcelBean();// excel导出javaBean,工具初始化
// 下载并删除临时文件
toExcelBean.downloadFileAndDropFile(fileName, filePath, response);
//结束
下载成功
1817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
