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(); } } } }