java poi zip_Java 批量下载excel,并对excel赋值,压缩为zip文件(POI版)

package com.neusoft.nda.servlet;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.Arrays;

import java.util.Calendar;

import java.util.Collections;

import java.util.Comparator;

import java.util.List;

import java.util.logging.Level;

import java.util.logging.Logger;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;

import org.apache.commons.lang.StringUtils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

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.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.tools.zip.ZipEntry;

import org.apache.tools.zip.ZipOutputStream;

import com.neusoft.nda.electronicrecord.service.MediaFileServlet;

import com.neusoft.nda.objectutils.utils.ObjectUtil;

import com.neusoft.nda.om.constants.ActionTypeConst;

import com.neusoft.nda.om.manager.IContainer;

import com.neusoft.nda.om.manager.IObjectManager;

import com.neusoft.nda.om.manager.ObjectManagerFactory;

import com.neusoft.nda.om.manager.dto.OMContextDTO;

import com.neusoft.nda.persistence.dataoperator.dto.ObjectDTO;

import com.neusoft.nda.utils.utils.ToolsUtil;

public class BeiKaoServlet extends HttpServlet{

/**

*

*/

private static final long serialVersionUID = -1166903384923406516L;

@Override

protected void doPost(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

downloadMulti(req, resp);

}

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

doPost(req, resp);

}

private void writeExcel(String finalXlsxPath,String id,String tempPath){

//注释掉了我的业务逻辑,下面只是对单元格的操作

//单元格如果没创建,则代码里创建createCell,如果已经创建,代码里改成getCell

File finalXlsxFile = createNewFile(finalXlsxPath);//复制模板,

Workbook workBook = null;

try {

workBook = getWorkbok(finalXlsxFile);

} catch (IOException e1) {

e1.printStackTrace();

}

Sheet sheet = workBook.getSheetAt(0);

workBook.createCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

workBook.createCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

//sheet 第一页

Row row1= sheet.getRow(2);

Cell c1=row1.getCell(6);

c1.setCellValue("");//3行7列

//sheet 第2页

Sheet sheet2 = workBook.getSheetAt(1);

HSSFFont font1 = (HSSFFont) workBook.createFont();

font1.setFontName("宋体");

font1.setFontHeightInPoints((short) 10);//设置字体大小

HSSFCellStyle cellStyle1 = (HSSFCellStyle) workBook.createCellStyle(); //卷内样式

cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

cellStyle1.setFont(font1);//宋体10号

cellStyle1.setWrapText(true);

cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

//从4行开始

Row rowsheet4= sheet2.createRow(k+3);

rowsheet4.setHeight((short) (42*20));//行高

Cell csheet=rowsheet4.createCell(0);//序号

csheet.setCellValue("");

csheet.setCellStyle(cellStyle1);

//封面 sheet3

Sheet sheet3 = workBook.getSheetAt(2);

//样式

HSSFCellStyle cellStyle=(HSSFCellStyle) workBook.createCellStyle();

cellStyle.setWrapText(true); //自动换行

HSSFFont font = (HSSFFont) workBook.createFont();

font.setFontName("宋体");

font.setFontHeightInPoints((short) 12);//设置字体大小

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

cellStyle.setFont(font);

cellStyle.setIndention((short) 2);//缩进2

cellStyle.setAlignment(CellStyle.ALIGN_LEFT);//靠左

Row rowsheet62= sheet3.getRow(5);

Cell csheet62=rowsheet62.getCell(1);

csheet62.setCellValue("");//

csheet62.setCellStyle(cellStyle);//自动换行

FileOutputStream out = null;

try {

out = new FileOutputStream(tempPath + File.separator + oto1.getBean().getString("DH")+".xls");

} catch (FileNotFoundException e2) {

e2.printStackTrace();

}

try {

workBook.write(out);

} catch (IOException e1) {

e1.printStackTrace();

}

try {

if (out != null) {

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 判断excel格式版本

*

* @param file

* @return

* @throws IOException

*/

private Workbook getWorkbok(File file) throws IOException {

Workbook wb = null;

FileInputStream in = new FileInputStream(file);

if (file.getName().endsWith(".xls")) { // Excel 2003

wb = new HSSFWorkbook(in);

} else if (file.getName().endsWith("xlsx")) { // Excel 2007/2010

wb = new XSSFWorkbook(in);

}

return wb;

}

private File createNewFile(String path) {

// 读取模板,并赋值到新文件************************************************************

// 文件模板路径

File file = new File(path);

if (!file.exists()) {

System.out.println("原模板文件不存在");

}

// 保存文件的路径

String realPath = file.getParent();

// 新的文件名

String newFileName =System.currentTimeMillis()+ ".xls";

// 判断路径是否存在

File dir = new File(realPath);

if (!dir.exists()) {

dir.mkdirs();

}

// 写入到新的excel

File newFile = new File(realPath, newFileName);

try {

newFile.createNewFile();

// 复制模板到新文件

FileUtils.copyFile(file, newFile);

} catch (Exception e) {

e.printStackTrace();

}

return newFile;

}

/**

*

*

下载多个文件

*

* @comment[注释说明]

* @authoryu-b, 2013-1-9

* @sinceNDAS 2.0

*

* @param request

* @param response

* @throws IOException

*/

private void downloadMulti(HttpServletRequest request,

HttpServletResponse response) throws IOException

{

String sourcePath = getServletContext().getRealPath("/WEB-INF/")

+ "/conf/ExcelModel/beikao.xls"; // 读取Excel文档 //用自己本地的路径

String id = request.getParameter("id");

String[] idsArr = id.split("_");

List ids = Arrays.asList(idsArr);

// 下载文件名

String downFileName = "download.zip";

// 生成缓存文件夹名

String sesstionId = request.getSession().getId();

String tempPath = ToolsUtil.getRealPath("platform" + File.separator

+ "seasplatform" + File.separator

+ sesstionId + File.separator + Calendar.getInstance().getTimeInMillis());

File tempFile = new File(tempPath);

if (!tempFile.exists())

{

tempFile.mkdirs();

}

OutputStream respOut = response.getOutputStream();

response.setCharacterEncoding("utf-8");

response.setHeader("Content-Disposition", "attachment; filename=""

+ new String(downFileName.getBytes(), "ISO-8859-1") + """);// 解决中文乱码问题

// 设置响应头和下载保存的文件名

String mimeType = null;

mimeType = this.getServletContext().getMimeType("zip");

if (StringUtils.isEmpty(mimeType))

{

mimeType = "APPLICATION/OCTET-STREAM";

}

response.setContentType(mimeType);

for(String objectId : ids)

{

writeExcel(sourcePath, objectId, tempPath);

}

creasteZip(new File(tempPath), respOut);

try

{

deleteFile(new File(tempPath));

respOut.flush();

respOut.close();

}

catch (IOException ex)

{

ex.printStackTrace();

}

}

/**

* 递归删除文件夹

*

* @param file

* @return void

*/

private void deleteFile(File file)

{

if (file.exists())

{// 判断文件是否存在

if (file.isFile())

{// 判断是否是文件

file.delete();// 删除文件

}

else if (file.isDirectory())

{// 否则如果它是一个目录

File[] files = file.listFiles();// 声明目录下所有的文件 files[];

for (int i = 0; i < files.length; i++)

{// 遍历目录下所有的文件

deleteFile(files[i]);// 把每个文件用这个方法进行迭代

}

file.delete();// 删除文件夹

}

}

else

{

System.out.println("所删除的文件不存在");

}

}

/**

* 压缩文件或者文件目录到指定的zip或者rar包

*

* @param inputFile 参数为文件类型的要压缩的文件或者文件夹

* @param out 输出流

* @return void

*/

private synchronized void creasteZip(File inputFile, OutputStream resOut)

{

ZipOutputStream out = null;

try

{

out = new ZipOutputStream(resOut);

out.setEncoding("gbk");

zip(inputFile, out, "");

}

catch (IOException e)

{

}

finally

{

try

{

out.close();

}

catch (IOException ex)

{

Logger.getLogger(MediaFileServlet.class.getName()).log(Level.SEVERE, null, ex);

}

}

}

/**

* 压缩文件或者文件目录到指定的zip或者rar包

*

* @param inputFile 参数为文件类型的要压缩的文件或者文件夹

* @param out 输出流

* @param base 基文件夹

* @return void

*/

private synchronized void zip(File inputFile, ZipOutputStream out, String base) throws IOException

{

if (inputFile.isDirectory())

{

File[] inputFiles = inputFile.listFiles();

out.putNextEntry(new ZipEntry(base + "/"));

base = base.length() == 0 ? "" : base + "/";

for (int i = 0; i < inputFiles.length; i++)

{

zip(inputFiles[i], out, base + inputFiles[i].getName());

}

}

else

{

if (base.length() > 0)

{

out.putNextEntry(new ZipEntry(base));

}

else

{

out.putNextEntry(new ZipEntry(inputFile.getName()));

}

FileInputStream in = new FileInputStream(inputFile);

try

{

int c;

byte[] by = new byte[1024];

while ((c = in.read(by)) != -1)

{

out.write(by, 0, c);

}

}

catch (IOException e)

{

}

finally

{

in.close();

}

}

}

}

package com.neusoft.nda.servlet; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Arrays; import java.util.Calendar; import java.util.Collections; import java.util.Comparator; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.io.FileUtils; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; 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.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.tools.zip.ZipEntry; import org.apache.tools.zip.ZipOutputStream; import com.neusoft.nda.electronicrecord.service.MediaFileServlet; import com.neusoft.nda.objectutils.utils.ObjectUtil; import com.neusoft.nda.om.constants.ActionTypeConst; import com.neusoft.nda.om.manager.IContainer; import com.neusoft.nda.om.manager.IObjectManager; import com.neusoft.nda.om.manager.ObjectManagerFactory; import com.neusoft.nda.om.manager.dto.OMContextDTO; import com.neusoft.nda.persistence.dataoperator.dto.ObjectDTO; import com.neusoft.nda.utils.utils.ToolsUtil; public class BeiKaoServlet extends HttpServlet{ /** * */ private static final long serialVersionUID = -1166903384923406516L; @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { downloadMulti(req, resp); } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doPost(req, resp); } private void writeExcel(String finalXlsxPath,String id,String tempPath){ //注释掉了我的业务逻辑,下面只是对单元格的操作 //单元格如果没创建,则代码里创建createCell,如果已经创建,代码里改成getCell File finalXlsxFile = createNewFile(finalXlsxPath);//复制模板, Workbook workBook = null; try { workBook = getWorkbok(finalXlsxFile); } catch (IOException e1) { e1.printStackTrace(); } Sheet sheet = workBook.getSheetAt(0); workBook.createCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 workBook.createCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 //sheet 第一页 Row row1= sheet.getRow(2); Cell c1=row1.getCell(6); c1.setCellValue("");//3行7列 //sheet 第2页 Sheet sheet2 = workBook.getSheetAt(1); HSSFFont font1 = (HSSFFont) workBook.createFont(); font1.setFontName("宋体"); font1.setFontHeightInPoints((short) 10);//设置字体大小 HSSFCellStyle cellStyle1 = (HSSFCellStyle) workBook.createCellStyle(); //卷内样式 cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 cellStyle1.setFont(font1);//宋体10号 cellStyle1.setWrapText(true); cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //从4行开始 Row rowsheet4= sheet2.createRow(k+3); rowsheet4.setHeight((short) (42*20));//行高 Cell csheet=rowsheet4.createCell(0);//序号 csheet.setCellValue(""); csheet.setCellStyle(cellStyle1); //封面 sheet3 Sheet sheet3 = workBook.getSheetAt(2); //样式 HSSFCellStyle cellStyle=(HSSFCellStyle) workBook.createCellStyle(); cellStyle.setWrapText(true); //自动换行 HSSFFont font = (HSSFFont) workBook.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12);//设置字体大小 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 cellStyle.setFont(font); cellStyle.setIndention((short) 2);//缩进2 cellStyle.setAlignment(CellStyle.ALIGN_LEFT);//靠左 Row rowsheet62= sheet3.getRow(5); Cell csheet62=rowsheet62.getCell(1); csheet62.setCellValue("");// csheet62.setCellStyle(cellStyle);//自动换行 FileOutputStream out = null; try { out = new FileOutputStream(tempPath + File.separator + oto1.getBean().getString("DH")+".xls"); } catch (FileNotFoundException e2) { e2.printStackTrace(); } try { workBook.write(out); } catch (IOException e1) { e1.printStackTrace(); } try { if (out != null) { out.flush(); out.close(); } } catch (IOException e) { e.printStackTrace(); } } /** * 判断excel格式版本 * * @param file * @return * @throws IOException */ private Workbook getWorkbok(File file) throws IOException { Workbook wb = null; FileInputStream in = new FileInputStream(file); if (file.getName().endsWith(".xls")) { // Excel 2003 wb = new HSSFWorkbook(in); } else if (file.getName().endsWith("xlsx")) { // Excel 2007/2010 wb = new XSSFWorkbook(in); } return wb; } private File createNewFile(String path) { // 读取模板,并赋值到新文件************************************************************ // 文件模板路径 File file = new File(path); if (!file.exists()) { System.out.println("原模板文件不存在"); } // 保存文件的路径 String realPath = file.getParent(); // 新的文件名 String newFileName =System.currentTimeMillis()+ ".xls"; // 判断路径是否存在 File dir = new File(realPath); if (!dir.exists()) { dir.mkdirs(); } // 写入到新的excel File newFile = new File(realPath, newFileName); try { newFile.createNewFile(); // 复制模板到新文件 FileUtils.copyFile(file, newFile); } catch (Exception e) { e.printStackTrace(); } return newFile; } /** * * 下载多个文件 * * @comment[注释说明] * @authoryu-b, 2013-1-9 * @sinceNDAS 2.0 * * @param request * @param response * @throws IOException */ private void downloadMulti(HttpServletRequest request, HttpServletResponse response) throws IOException { String sourcePath = getServletContext().getRealPath("/WEB-INF/") + "/conf/ExcelModel/beikao.xls"; // 读取Excel文档 //用自己本地的路径 String id = request.getParameter("id"); String[] idsArr = id.split("_"); List ids = Arrays.asList(idsArr); // 下载文件名 String downFileName = "download.zip"; // 生成缓存文件夹名 String sesstionId = request.getSession().getId(); String tempPath = ToolsUtil.getRealPath("platform" + File.separator + "seasplatform" + File.separator + sesstionId + File.separator + Calendar.getInstance().getTimeInMillis()); File tempFile = new File(tempPath); if (!tempFile.exists()) { tempFile.mkdirs(); } OutputStream respOut = response.getOutputStream(); response.setCharacterEncoding("utf-8"); response.setHeader("Content-Disposition", "attachment; filename="" + new String(downFileName.getBytes(), "ISO-8859-1") + """);// 解决中文乱码问题 // 设置响应头和下载保存的文件名 String mimeType = null; mimeType = this.getServletContext().getMimeType("zip"); if (StringUtils.isEmpty(mimeType)) { mimeType = "APPLICATION/OCTET-STREAM"; } response.setContentType(mimeType); for(String objectId : ids) { writeExcel(sourcePath, objectId, tempPath); } creasteZip(new File(tempPath), respOut); try { deleteFile(new File(tempPath)); respOut.flush(); respOut.close(); } catch (IOException ex) { ex.printStackTrace(); } } /** * 递归删除文件夹 * * @param file * @return void */ private void deleteFile(File file) { if (file.exists()) {// 判断文件是否存在 if (file.isFile()) {// 判断是否是文件 file.delete();// 删除文件 } else if (file.isDirectory()) {// 否则如果它是一个目录 File[] files = file.listFiles();// 声明目录下所有的文件 files[]; for (int i = 0; i < files.length; i++) {// 遍历目录下所有的文件 deleteFile(files[i]);// 把每个文件用这个方法进行迭代 } file.delete();// 删除文件夹 } } else { System.out.println("所删除的文件不存在"); } } /** * 压缩文件或者文件目录到指定的zip或者rar包 * * @param inputFile 参数为文件类型的要压缩的文件或者文件夹 * @param out 输出流 * @return void */ private synchronized void creasteZip(File inputFile, OutputStream resOut) { ZipOutputStream out = null; try { out = new ZipOutputStream(resOut); out.setEncoding("gbk"); zip(inputFile, out, ""); } catch (IOException e) { } finally { try { out.close(); } catch (IOException ex) { Logger.getLogger(MediaFileServlet.class.getName()).log(Level.SEVERE, null, ex); } } } /** * 压缩文件或者文件目录到指定的zip或者rar包 * * @param inputFile 参数为文件类型的要压缩的文件或者文件夹 * @param out 输出流 * @param base 基文件夹 * @return void */ private synchronized void zip(File inputFile, ZipOutputStream out, String base) throws IOException { if (inputFile.isDirectory()) { File[] inputFiles = inputFile.listFiles(); out.putNextEntry(new ZipEntry(base + "/")); base = base.length() == 0 ? "" : base + "/"; for (int i = 0; i < inputFiles.length; i++) { zip(inputFiles[i], out, base + inputFiles[i].getName()); } } else { if (base.length() > 0) { out.putNextEntry(new ZipEntry(base)); } else { out.putNextEntry(new ZipEntry(inputFile.getName())); } FileInputStream in = new FileInputStream(inputFile); try { int c; byte[] by = new byte[1024]; while ((c = in.read(by)) != -1) { out.write(by, 0, c); } } catch (IOException e) { } finally { in.close(); } } } }
经验分享 程序员 微信小程序 职场和发展