项目中有大量的文件解析和文件导出的功能，所有弄了一堆工具类，总结一下
1 导出实体类
package org.atm.dc.app.entry;
import java.io.Serializable;
import java.util.List;
/**
* 导入对象实体
* Created by Administrator on 2019/5/9.
*/
public class ExcelData implements Serializable {
private static final long serialVersionUID = 6133772627258154184L;
/**
* 表头
*/
private List<String> titles;
/**
* 数据
*/
private List<List<Object>> rows;
/**
* 页签名称
*/
private String name;
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
2 工具类
package org.atm.dc.app.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
import org.atm.dc.app.entry.ExcelData;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;
/**
* Created by Administrator on 2019/5/9.
*/
public class ExcelUtils {
private static ReentrantLock lock=new ReentrantLock();
/**
* 使用浏览器选择路径下载
*
* @param response
* @param fileName
* @param data
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8"))));
exportExcel(data, response.getOutputStream());
}
public static int generateExcel(ExcelData excelData, String path) throws Exception {
File f = new File(path);
FileOutputStream out = new FileOutputStream(f);
return exportExcel(excelData, out);
}
private static synchronized int exportExcel(ExcelData data, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
int rowIndex = 0;
try {
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
XSSFSheet sheet = wb.createSheet(sheetName);
rowIndex = writeExcel(wb, sheet, data);
wb.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.flush();
//此处需要关闭 wb 变量
out.close();
}
return rowIndex;
}
/**
* 表显示字段
*
* @param wb
* @param sheet
* @param data
* @return
*/
private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
int rowIndex = 0;
rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
autoSizeColumns(sheet, data.getTitles().size() + 1);
return rowIndex;
}
/**
* 设置表头
*
* @param wb
* @param sheet
* @param titles
* @return
*/
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
int rowIndex = 0;
int colIndex = 0;
Font titleFont = wb.createFont();
//设置字体
titleFont.setFontName("simsun");
//设置粗体
titleFont.setBoldweight(Short.MAX_VALUE);
//设置字号
titleFont.setFontHeightInPoints((short) 14);
//设置颜色
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
//水平居中
titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//垂直居中
titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置图案颜色
titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
//设置图案样式
titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
titleRow.setHeightInPoints(25);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
/**
* 设置内容
*
* @param wb
* @param sheet
* @param rows
* @param rowIndex
* @return
*/
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex;
Font dataFont = wb.createFont();
dataFont.setFontName("simsun");
dataFont.setFontHeightInPoints((short) 14);
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
for (List<Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex);
dataRow.setHeightInPoints(25);
colIndex = 0;
for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}
/**
* 自动调整列宽
*
* @param sheet
* @param columnNumber
*/
private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
if (colWidth < 255 * 256) {
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
} else {
sheet.setColumnWidth(i, 6000);
}
// int orgWidth = sheet.getColumnWidth(i);
// int newWidth = (int) (sheet.getColumnWidth(i) + 100);
// if (newWidth > orgWidth) {
// sheet.setColumnWidth(i, newWidth);
// } else {
// sheet.setColumnWidth(i, orgWidth);
// }
sheet.autoSizeColumn(i, true);
}
}
/**
* 设置边框
*
* @param style
* @param border
* @param color
*/
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(BorderSide.TOP, color);
style.setBorderColor(BorderSide.LEFT, color);
style.setBorderColor(BorderSide.RIGHT, color);
style.setBorderColor(BorderSide.BOTTOM, color);
}
/**
* 读取sheet第一页
*
* @param file
* @return
*/
public static Sheet readSheet(MultipartFile file) {
lock.lock();
Sheet sheet=null;
try {
String fileName = file.getOriginalFilename();
InputStream is = null;
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
Workbook wb = null;
sheet = null;
is = file.getInputStream();
sheet = readSheet(is, isExcel2003, 0);
is.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
lock.unlock();
}
return sheet;
}
private static Sheet readSheet(InputStream inputStream, boolean isExcel2003, int index) {
Workbook wb = null;
Sheet sheet = null;
try {
if (isExcel2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
sheet = wb.getSheetAt(index);
} catch (IOException e) {
e.printStackTrace();
}
return sheet;
}
public static Sheet readSheet(File file, int index) {
Sheet sheet = null;
try {
String fileName = file.getName();
FileInputStream fileInputStream = new FileInputStream(file);
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
sheet=readSheet(fileInputStream,isExcel2003,index);
} catch (IOException e) {
e.printStackTrace();
}
return sheet;
}
public static int getNumberOfSheets(File file) {
try {
String fileName = file.getName();
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream inputStream = readSheet(file);
Workbook wb = null;
try {
if (isExcel2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
return wb.getNumberOfSheets();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return 0;
}
private static InputStream readSheet(File file) throws FileNotFoundException {
return new FileInputStream(file);
}
/**
* 获取cell值
*
* @param row
* @param i
* @return
*/
public static String getCellValue(Row row, int i) {
Cell cell = row.getCell(i);
if (cell == null) {
return "";
}
row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
}
}
上面这个工具类可以进行流的读入和Excel文件的输出（输出到浏览器或者输出到本地）
3 压缩工具类
package org.atm.dc.app.util;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Paragraph;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.hwpf.usermodel.TableCell;
import org.apache.poi.hwpf.usermodel.TableIterator;
import org.apache.poi.hwpf.usermodel.TableRow;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
/**
* Created by Administrator on 2019/5/13.
*/
public class IOUtils {
/**
* 将服务器文件存到压缩包中
* @param files
* @param outputStream
* @throws IOException
* @throws ServletException
*/
public static void zipFile(List<File> files, ZipOutputStream outputStream) throws IOException, ServletException {
try {
int size = files.size();
// 压缩列表中的文件
for (int i = 0; i < size; i++) {
File file = (File) files.get(i);
zipFile(file, outputStream);
}
} catch (IOException e) {
throw e;
}
}
public static void zipFile(File inputFile, ZipOutputStream outputstream) throws IOException, ServletException {
try {
if (inputFile.exists()) {
if (inputFile.isFile()) {
FileInputStream inStream = new FileInputStream(inputFile);
BufferedInputStream bInStream = new BufferedInputStream(inStream);
ZipEntry entry = new ZipEntry(inputFile.getName());
outputstream.putNextEntry(entry);
final int MAX_BYTE = 10 * 1024 * 1024; // 最大的流为10M
long streamTotal = 0; // 接受流的容量
int streamNum = 0; // 流需要分开的数量
int leaveByte = 0; // 文件剩下的字符数
byte[] inOutbyte; // byte数组接受文件的数据
streamTotal = bInStream.available(); // 通过available方法取得流的最大字符数
streamNum = (int) Math.floor(streamTotal / MAX_BYTE); // 取得流文件需要分开的数量
leaveByte = (int) streamTotal % MAX_BYTE; // 分开文件之后,剩余的数量
if (streamNum > 0) {
for (int j = 0; j < streamNum; ++j) {
inOutbyte = new byte[MAX_BYTE];
// 读入流,保存在byte数组
bInStream.read(inOutbyte, 0, MAX_BYTE);
outputstream.write(inOutbyte, 0, MAX_BYTE); // 写出流
}
}
// 写出剩下的流数据
inOutbyte = new byte[leaveByte];
bInStream.read(inOutbyte, 0, leaveByte);
outputstream.write(inOutbyte);
outputstream.closeEntry(); // Closes the current ZIP entry
// and positions the stream for
// writing the next entry
bInStream.close(); // 关闭
inStream.close();
}
} else {
throw new ServletException("文件不存在！");
}
} catch (IOException e) {
throw e;
}
}
public static void downloadFile(File file, HttpServletResponse response, boolean isDelete) {
try {
// 以流的形式下载文件。
BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file.getPath()));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(file.getName().getBytes("UTF-8"),"ISO-8859-1"));
toClient.write(buffer);
toClient.flush();
toClient.close();
if(isDelete)
{
file.delete(); //是否将生成的服务器端文件删除
}
}
catch (IOException ex) {
ex.printStackTrace();
}
}
public static ArrayList<File> getFiles(String path) throws Exception {
//目标集合fileList
ArrayList<File> fileList = new ArrayList<File>();
File file = new File(path);
if(file.isDirectory()){
File []files = file.listFiles();
for(File fileIndex:files){
//如果这个文件是目录，则进行递归搜索
if(fileIndex.isDirectory()){
getFiles(fileIndex.getPath());
}else {
//如果文件是普通文件，则将文件句柄放入集合中
fileList.add(fileIndex);
}
}
}
return fileList;
}
}
4 测试
1 导出excel（导出到浏览器）
public void export(HttpServletResponse response) {
String path = "";
File file = new File(path);
Sheet sheet = ExcelUtils.readSheet(file, 0);
//查询人员表
List<JSONObject> person_list = mongodb.findAll("person");
//excel表头列
List<String> titles_list = new ArrayList<>();
titles_list.add("姓名");
titles_list.add("年龄");
//数据集合
List<List<Object>> rows = new ArrayList<>();
for (int i = 0; i < person_list.size(); i++) {
JSONObject jsonObject = person_list.get(i);
List<Object> row_list = new ArrayList<>();
row_list.add(jsonObject.getString("name"));
row_list.add(jsonObject.getString("age"));
rows.add(row_list);
}
//写到excel
ExcelData excelData = new ExcelData();
//sheet名称
excelData.setName("人员表");
excelData.setRows(rows);
excelData.setTitles(titles_list);
try {
ExcelUtils.exportExcel(response, "人员表", excelData);
} catch (Exception e) {
e.printStackTrace();
}
}2 导出压缩文件
先把文件导出到服务器某个位置下 然后再将这些文件压缩成一个zip返回给浏览器，再把这些导出文件删除即可
public void export(HttpServletResponse response) {
//存到压缩文件的路径
String DOWNLOAD_ZIP_PATH="";
//存放excel文件的地方
String DOWNLOAD_EXCEL_PATH="";
String path = "";
File file = new File(path);
Sheet sheet = ExcelUtils.readSheet(file, 0);
//查询人员表
List<JSONObject> person_list = mongodb.findAll("person");
//excel表头列
List<String> titles_list = new ArrayList<>();
titles_list.add("姓名");
titles_list.add("年龄");
//数据集合
List<List<Object>> rows = new ArrayList<>();
for (int i = 0; i < person_list.size(); i++) {
JSONObject jsonObject = person_list.get(i);
List<Object> row_list = new ArrayList<>();
row_list.add(jsonObject.getString("name"));
row_list.add(jsonObject.getString("age"));
rows.add(row_list);
}
//写到excel
ExcelData excelData = new ExcelData();
//sheet名称
excelData.setName("人员表");
excelData.setRows(rows);
excelData.setTitles(titles_list);
try {
//ExcelUtils.exportExcel(response, "人员表", excelData);
//导出到服务器指定文件夹
ExcelUtils.generateExcel(excelData,DOWNLOAD_EXCEL_PATH+"人员表.xls");
} catch (Exception e) {
e.printStackTrace();
}
try {
//将导出excel转为zip进行导出
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
String zipName = "人员表" + ".zip";
String outFilePath = DOWNLOAD_ZIP_PATH;
File file2 = new File(outFilePath);
if (!file2.exists()) {
file2.mkdirs();
}
File fileZip = new File(outFilePath + zipName);
fileZip.createNewFile();
//所有的Excel文件
ArrayList<File> fileList = IOUtils.getFiles(DOWNLOAD_EXCEL_PATH);
FileOutputStream outStream = new FileOutputStream(fileZip);
ZipOutputStream toClient = new ZipOutputStream(outStream);
IOUtils.zipFile(fileList, toClient);
toClient.close();
outStream.close();
//导出zip到浏览器
IOUtils.downloadFile(fileZip, response, false);
//删除文件夹
org.atm.dc.utils.FileUtils.delete(DOWNLOAD_EXCEL_PATH);
org.atm.dc.utils.FileUtils.delete(DOWNLOAD_ZIP_PATH);
} catch (Exception e) {
e.printStackTrace();
}
}3 导入excel文件
直接调用上述工具类中的readsheet即可
@RequestMapping("/read2")
public void read2(MultipartFile file) {
Sheet sheet = ExcelUtils.readSheet(file);
int nums = sheet.getLastRowNum();
for (int i = 0; i <= nums; i++) {
Row row = sheet.getRow(i);
String cellValue1 = ExcelUtils.getCellValue(row, 0);//第一个cell
String cellValue2 = ExcelUtils.getCellValue(row, 1);//第二个cell
}
}若是我有使用错误或者描述不清楚的地方还请麻烦各位指点出来。。。。。
337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
