导出excel接口
1.
//根据模板导出excel方法
@RequestMapping(params = "exportLabel")
public void exportLabel(LabelBusinessEntity labelBusiness,HttpServletRequest request,HttpServletResponse response, ModelMap modelMap) {
//获取需要导出的excel模板（可实现一套代码可适应多种模板，只需上传需要导出的模板即可）
String template = request.getParameter("template");
//需要导出的数据id
String labelIds = request.getParameter("ids");
String[] labelArr = labelIds.split(",");
//需要导出的模板需求
FileCustemplateEntity fileCustemplate = systemService.getEntity(FileCustemplateEntity.class, template);
//一个sheet需要导出几个数据
int lablequantity = fileCustemplate.getLablequantity();
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
List<String> fileFieldList = new ArrayList<String>();
//获取需要导出的字段和标签(可以是文本、二维码、条形码，也可以是图片)
String sql = "select count(1) from file_custemplate_excelcolumn where template_id='"+fileCustemplate.getTemplateId()+"'";
if(!"0".equals(jdbcTemplate.queryForObject(sql, String.class))) {
sql = "select template_orginkey,label from file_custemplate_excelcolumn where template_id='"+fileCustemplate.getTemplateId()+"'";
List<Map<String,Object>> queryForList = jdbcTemplate.queryForList(sql);
//得到LabelBusinessEntity 类
Class aClass = LabelBusinessEntity.class;
int a = 0;
Map<String, Object> exportObj = null;
//循环需要导出的数据
for(int i = 0; i < labelArr.length; i++) {
//判断求余为0则存入下一个Map中(求余判断是否存入下一个sheet)
if((i % lablequantity) == 0) {
a = 1;
exportObj = new HashMap<String, Object>();
}
LabelBusinessEntity labelBus = labelBusinessService.getEntity(LabelBusinessEntity.class, labelArr[i]);
//循环插入需要导出的字段，通过反射机制的到值
for(Map<String,Object> map : queryForList) {
if(StringUtil.isNotEmpty(map.get("template_orginkey"))) {
//拼接get方法
String methodName = "get" + String.valueOf(map.get("template_orginkey")).substring(0,1).toUpperCase() + String.valueOf(map.get("template_orginkey")).substring(1);
try {
//通过反射的到方法
Method method = aClass.getMethod(methodName);
if(StringUtil.isNotEmpty(method.invoke(labelBus))) {
//判断导出的标签
if("条形码".equals(String.valueOf(map.get("label"))) || "二维码".equals(String.valueOf(map.get("label")))) {
if(i == 0) {
//将是条形码或二维码的字段存入list中
fileFieldList.add(String.valueOf(map.get("template_orginkey")));
}
//存入excel中以便通过值获取需要插入图片的位置
exportObj.put(String.valueOf(map.get("template_orginkey")) + a, String.valueOf(method.invoke(labelBus)) + "~" + a);
} else {
exportObj.put(String.valueOf(map.get("template_orginkey")) + a, String.valueOf(method.invoke(labelBus)));
}
} else {
exportObj.put(String.valueOf(map.get("template_orginkey")) + a, "");
}
//生成条形码
if("条形码".equals(String.valueOf(map.get("label")))) {
if(StringUtil.isNotEmpty(method.invoke(labelBus))) {
//调用生成条形码方法
BufferedImage image = BarCodeUtils.insertWords(BarCodeUtils.getBarCode(String.valueOf(method.invoke(labelBus))), String.valueOf(method.invoke(labelBus)));
String time = UUID.randomUUID() +".jpg";
String fileUrl = "D:\\ldtrade\\BrCode\\" + time;
File file = new File(fileUrl);
if (!file.getParentFile().exists()) {
try {
file.getParentFile().mkdirs();
} catch (Exception e) {
e.printStackTrace();
}
}
ImageIO.write(image, "jpg", file);
exportObj.put(String.valueOf(map.get("template_orginkey")) + "_" + a, fileUrl);
}
} else if("二维码".equals(String.valueOf(map.get("label")))) {//生成二维码
if(StringUtil.isNotEmpty(method.invoke(labelBus))) {
String fileUrl = "D:\\ldtrade\\QrCode";
File file = new File(fileUrl);
if (!file.getParentFile().exists()) {
try {
file.getParentFile().mkdirs();
} catch (Exception e) {
e.printStackTrace();
}
}
if(StringUtil.isNotEmpty(method.invoke(labelBus))) {
//调用生成二维码方法
String fileName = QRCodeUtils.encode(String.valueOf(method.invoke(labelBus)), fileUrl, true);
exportObj.put(String.valueOf(map.get("template_orginkey")) + "_" + a, fileUrl + "\\" + fileName);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
//判断求余为0或为最后一项是添加到list中；
int b = i + 1;
if((b % lablequantity) == 0 || b == labelArr.length) {
exportObj.put("templatename", labelBus.getTemplatename());
list.add(exportObj);
}
a++;
}
}
//从数据库中获取需要导出的模板
String templateUrlSql = "select count(1) from tablename where note='"+fileCustemplate.getId()+"' and extend='xls' or extend='xlsx'";
String fileUrl = "";
//判断模板是否存在
if(!"0".equals(jdbcTemplate.queryForObject(templateUrlSql, String.class))) {
templateUrlSql = "select id,realpath,extend from tablename where note='"+fileCustemplate.getId()+"' and extend='xls' or extend='xlsx' order by createdate desc limit 1";
Map<String, Object> queryForMap = jdbcTemplate.queryForMap(templateUrlSql);
fileUrl = String.valueOf(queryForMap.get("realpath"));
}
String lujing = request.getSession().getServletContext().getRealPath("/");
String lujing1 = "";
InputStream in;
try {
lujing1 = lujing + fileUrl;
//得到模板
File file = new File(lujing1);
if (file.exists()) {
//读取文件流
in = new FileInputStream(file);
/*XSSFWorkbook work = null;
work = new XSSFWorkbook(in);*/
//开始导出
labelBusinessService.exportTemplateData(request, response, in, list, labelArr, lablequantity, fileFieldList);
}
} catch (Exception e) {
e.printStackTrace();
}
}
导出方法
2.
@Override
public void exportTemplateData(HttpServletRequest request, HttpServletResponse response, InputStream in, List<Map<String, Object>> list, String[] labelArr, int lablequantity, List<String> fileFieldList) {
OutputStream os = null;
try {
if(StringUtil.isNotEmpty(list.get(0).get("templatename"))) {
//定义导出流
os = getOutputStream(String.valueOf(list.get(0).get("templatename")), response);
} else {
os = getOutputStream("数据", response);
}
List<String> listSheetNames = new ArrayList<String>();
for (int i = 0; i < list.size(); i++) {
int a = i + 1;
//定义sheet的名称
listSheetNames.add(String.valueOf(a));
}
//复制sheet并赋值
XLSTransformer transformer = new XLSTransformer();
//通过transformMultipleSheetsList可复制多sheet，图片不会复制;
//第一个参数：in为输入流；
//第二个参数：list为需要写入excel的值（默认第一个sheet会取list.get(0)的值，第二个sheet会取list.get(1)的值，以此类推；
//第三个参数：listSheetNames为sheet的名称，不能重复，会根据listSheetNames.size()来生成几个sheet;
//第四个参数：“map”为如果传入的list为List<Map>的类型，就可以直接定义为"map"；
//第五个参数：这个参数不常用直接定义为普通的map就行；
//第六个参数：此为从第几个sheet开始；
Workbook workbook = transformer.transformMultipleSheetsList(in, list, listSheetNames, "map", new HashMap(), 0);
//复制sheet和赋值后，重新转为输入流
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//写入byteArray输出流中
workbook.write(bos);
byte[] barray = bos.toByteArray();
//转为输入流
InputStream is = new ByteArrayInputStream(barray);
//转为HSSFWorkbook，处理excel，往excel中插入图片
HSSFWorkbook hssfWork = new HSSFWorkbook(is);
//获取名称为1的sheet（就是第一个sheet）
HSSFSheet sourceSheet = hssfWork.getSheet("1");
HSSFPatriarch drawingPatriarch = sourceSheet.getDrawingPatriarch();
if(StringUtil.isNotEmpty(drawingPatriarch)) {
//获取此sheet中的图片
List<HSSFShape> pictureList = sourceSheet.getDrawingPatriarch().getChildren();
//将图片复制到其它sheet中
for (int i = 2; i < list.size() + 1; i++) {
for (HSSFShape shape : pictureList) {
//复制图片方法
copyPicture(hssfWork, String.valueOf(i), shape);
}
}
//循环根据条形码、二维码字段插入图片
for(String fileField : fileFieldList) {
//根据模板中的首个图片获取样式后，依次将图片插入到对应位置中
hssfWork = filedInsertPicture(hssfWork, list, sourceSheet, pictureList, fileField, lablequantity);
}
}
//写入成功后转化为输出流
hssfWork.write(os);
TSUser user = ResourceUtil.getSessionUser();
for(String lableId : labelArr) {
if(StringUtil.isNotEmpty(lableId)) {
LabelBusinessEntity labelBus = systemService.getEntity(LabelBusinessEntity.class, lableId);
labelBus.setState("3");
labelBus.setPrinter(user.getRealName());
labelBus.setPrinterdate(new Date());
systemService.saveOrUpdate(labelBus);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
os.close();
in.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
定义输出流
3.
/**
* 导出文件时为Writer生成OutputStream.
*
* @param fileName 文件名
* @param response response
* @return ""
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
复制图片方法
4.
//复制图片方法 参数二为复制到目标sheet的名称。参数三位第几个图片HSSFShape。
public static void copyPicture(HSSFWorkbook workbook, String targetSheetName, HSSFShape shape) throws IOException {
HSSFSheet targetSheet = workbook.getSheet(targetSheetName);
Map<String, Object> sourceSheetPicture = getPicturesFromHSSFSheet(shape);
if (!sourceSheetPicture.isEmpty()) {
HSSFPatriarch drawing = targetSheet.createDrawingPatriarch();
drawing.createPicture((HSSFClientAnchor) sourceSheetPicture.get("pictureAnchor"),
workbook.addPicture((byte[]) sourceSheetPicture.get("pictureByteArray"), Integer.parseInt(sourceSheetPicture.get("pictureType").toString())));
}
}
//获取excel中的图片信息
public static Map<String, Object> getPicturesFromHSSFSheet(HSSFShape shape) throws IOException {
Map<String, Object> map = new HashMap<String, Object>();
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFAnchor anchor = picture.getAnchor();
HSSFPictureData pictureData = picture.getPictureData();
map.put("pictureAnchor", anchor);
map.put("pictureByteArray", pictureData.getData());
map.put("pictureType", pictureData.getFormat());
}
return map;
}
插入图片方法
5.
//根据模板中的首个图片获取样式后，依次将图片插入到对应位置中
public HSSFWorkbook filedInsertPicture(HSSFWorkbook hssfWork, List<Map<String, Object>> list, HSSFSheet sourceSheet, List<HSSFShape> pictureList, String filed, int lablequantity) throws Exception {
//获取第一个模板图片字符串所在的位置
List<Map<String,Object>> findRowList = findRow(sourceSheet, String.valueOf(list.get(0).get(filed + "1")), "save");
//根据图片字符串的位置信息判断得到图片的
if(findRowList.size() > 0) {
if(StringUtil.isNotEmpty(findRowList.get(0).get("rowIndex"))) {
Map<String, Integer> pictureStyleMap = new HashMap<String, Integer>();
int rowIndex = Integer.valueOf(String.valueOf(findRowList.get(0).get("rowIndex")));
int columnIndex = Integer.valueOf(String.valueOf(findRowList.get(0).get("columnIndex")));
//获取模板中需要插入图片的样式
for (HSSFShape shape : pictureList) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if((anchor.getRow2() == rowIndex || anchor.getRow1() == rowIndex) && (anchor.getCol1() == columnIndex || anchor.getCol2() == columnIndex)) {
int rowPoor = anchor.getRow2() - anchor.getRow1();
int colPoor = anchor.getCol2() - anchor.getCol1();
//图片列的起始位置与文字定位列的差值，在插入图片是需要加上差值
if(anchor.getCol1() > columnIndex) {
pictureStyleMap.put("colAdd", anchor.getCol1() - columnIndex);
}
if(anchor.getRow1() > rowIndex) {
pictureStyleMap.put("rowAdd", anchor.getRow1() - rowIndex);
}
pictureStyleMap.put("dx1", anchor.getDx1());
pictureStyleMap.put("dy1", anchor.getDy1());
pictureStyleMap.put("dx2", anchor.getDx2());
pictureStyleMap.put("dy2", anchor.getDy2());
pictureStyleMap.put("rowPoor", rowPoor);
pictureStyleMap.put("colPoor", colPoor);
break;
}
}
//根据获取到图片的样式，根据sheet依次插入对应的图片
if(!pictureStyleMap.isEmpty()) {
for (int i = 0; i < list.size(); i++) {
HSSFSheet hssfSheet = hssfWork.getSheet(String.valueOf((i + 1)));
for(int y = 1; y <= lablequantity; y ++) {
if(StringUtil.isNotEmpty(list.get(i).get(filed + y))) {
//获取指定字符的位置
List<Map<String,Object>> findRowList2 = findRow(hssfSheet, String.valueOf(list.get(i).get(filed + y)), "clear");
if(findRowList2.size() > 0) {
if(StringUtil.isNotEmpty(list.get(i).get(filed + "_" + y))) {
if(StringUtil.isNotEmpty(findRowList2.get(0).get("rowIndex")) && StringUtil.isNotEmpty(findRowList2.get(0).get("columnIndex"))) {
int colAdd = 0;
int rowAdd = 0;
//插入图片是如果列有差值则需要相加；
if(StringUtil.isNotEmpty(pictureStyleMap.get("colAdd"))) {
colAdd = pictureStyleMap.get("colAdd");
}
if(StringUtil.isNotEmpty(pictureStyleMap.get("rowAdd"))) {
rowAdd = pictureStyleMap.get("rowAdd");
}
int row1 = Integer.valueOf(String.valueOf(findRowList2.get(0).get("rowIndex"))) + rowAdd;
int col1 = Integer.valueOf(String.valueOf(findRowList2.get(0).get("columnIndex"))) + colAdd;
int col2 = col1 + pictureStyleMap.get("colPoor");
int row2 = row1 + pictureStyleMap.get("rowPoor");
picture(hssfWork, hssfSheet, String.valueOf(list.get(i).get(filed + "_" + y)), ".jpg", pictureStyleMap.get("dx1"), pictureStyleMap.get("dy1"),pictureStyleMap.get("dx2"),pictureStyleMap.get("dy2"),
(short)col1, row1, (short) col2, row2);
}
} else {
break;
}
}
}
}
}
}
}
}
return hssfWork;
}
将图片插入到指定单元格
6.
//将图片插入到指定的单元格中
public void picture(HSSFWorkbook workbook, HSSFSheet sheet, String fileUrl, String fileType, int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) {
try {
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
File file = new File(fileUrl);
if(file.exists()) {
//生成一个表格，设置表格名称
InputStream is = new FileInputStream(file);
// 输入流
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
byte[] buffer = new byte[5];
//先读后写,循环读写
int len = 0;
while ((len = is.read(buffer)) != -1) {
byteArrayOut.write(buffer, 0, len);
}
/* BufferedImage bufferImg = ImageIO.read(is);
if (fileType.equals(".jpg")) {
ImageIO.write(bufferImg, "jpg", byteArrayOut);
} else if (fileType.equals(".png")) {
ImageIO.write(bufferImg, "png", byteArrayOut);
}*/
HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2,col1, row1, col2, row2);
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
} catch (Exception e) {
e.printStackTrace();
}
}
根据指定字符串获取单元格位置
7.
//根据指定字符串获取单元格位置
public List<Map<String, Object>> findRow(HSSFSheet sheet, String cellContent, String type) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
Map<String, Object> map = new HashMap<String, Object>();
int columnIndex = cell.getColumnIndex();
int rowIndex = cell.getRowIndex();
map.put("columnIndex", columnIndex);
map.put("rowIndex", rowIndex);
list.add(map);
if("clear".equals(type)) {
cell.setCellValue("");
}
}
}
}
}
return list;
}
模板
在需要插入二维码或条形码的位置留一个同样大小的图，代码中会获取这张图片的样式，将样式赋值到即将插入到excel中的二维码或条形码，这样就可以精准控制每一个插入的图片的大小和位置；
453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
