Java导出Excel以及word富文本
·
1、添加pom依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
2、富文本导出
package com.sxnxcloud.regulation.utils;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.poifs.filesystem.DocumentEntry;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
/**
* @ClassName: WordUtils
* @Description: TODO
* @Author yuf
* @Date 2020/3/24 17:18
*/
public class WordUtils {
public static void exportWord(HttpServletRequest request, HttpServletResponse response, String title, String text) {
try {
//word内容
String content="<html><body>" +
"<p style=\"text-align: center;\"><span style=\"font-family: 黑体, SimHei; font-size: 24px;\">"
+ title + "</span></p>" + text + "</body></html>";
byte b[] = content.getBytes("GBK"); //这里是必须要设置编码的，不然导出中文就会乱码。
ByteArrayInputStream bais = new ByteArrayInputStream(b);//将字节数组包装到流中
/*
* 关键地方
* 生成word格式 */
POIFSFileSystem poifs = new POIFSFileSystem();
DirectoryEntry directory = poifs.getRoot();
directory.createDocument("WordDocument", bais);
//输出文件
//输出文件
request.setCharacterEncoding("utf-8");
response.setContentType("application/msword");//导出word格式
response.addHeader("Content-Disposition", "attachment;filename=" +title+".doc");
ServletOutputStream ostream = response.getOutputStream();
poifs.writeFilesystem(ostream);
bais.close();
ostream.close();
poifs.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
3、导出Excel
package com.sxnxcloud.regulation.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.gocom.coframe.sdk.CofConstants;
import org.gocom.coframe.sdk.exception.CofErrorCode;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
@Slf4j
public class ExcelUtils {
// 字符编码格式
private static String charsetCode = "utf-8";
public static void download(HttpServletRequest request, HttpServletResponse response, String filePath, String fileName) {
try {
File file = new File(filePath);
/**
* 中文乱码解决
*/
String type = request.getHeader("User-Agent").toLowerCase();
if (type.indexOf("firefox") > 0 || type.indexOf("chrome") > 0) {
/**
* 谷歌或火狐
*/
fileName = new String(fileName.getBytes("gbk"), "ISO-8859-1");
} else {
/**
* IE
*/
fileName = URLEncoder.encode(fileName, charsetCode);
}
// 设置响应的头部信息
response.setHeader("content-disposition", "attachment;filename=" + fileName);
// 设置响应内容的类型
response.setContentType(getFileContentType(fileName) + "; charset=" + charsetCode);
// 设置响应内容的长度
response.setContentLength((int) file.length());
// 输出
outStream(new FileInputStream(file), response.getOutputStream());
} catch (Exception e) {
System.out.println("执行downloadFile发生了异常：" + e.getMessage());
}
}
/**
* 基础字节数组输出
*/
private static void outStream(InputStream is, OutputStream os) {
try {
byte[] buffer = new byte[10240];
int length = -1;
while ((length = is.read(buffer)) != -1) {
os.write(buffer, 0, length);
os.flush();
}
} catch (Exception e) {
System.out.println("执行 outStream 发生了异常：" + e.getMessage());
} finally {
try {
os.close();
} catch (IOException e) {
}
try {
is.close();
} catch (IOException e) {
}
}
}
/**
* 文件的内容类型
*/
private static String getFileContentType(String name){
String result = "";
String fileType = name.toLowerCase();
if (fileType.endsWith(".png")) {
result = "image/png";
} else if (fileType.endsWith(".gif")) {
result = "image/gif";
} else if (fileType.endsWith(".jpg") || fileType.endsWith(".jpeg")) {
result = "image/jpeg";
} else if(fileType.endsWith(".svg")){
result = "image/svg+xml";
}else if (fileType.endsWith(".doc")) {
result = "application/msword";
} else if (fileType.endsWith(".xls")) {
result = "application/x-excel";
} else if (fileType.endsWith(".zip")) {
result = "application/zip";
} else if (fileType.endsWith(".pdf")) {
result = "application/pdf";
} else {
result = "application/octet-stream";
}
return result;
}
private String outfile;
/**
*
* @param header 为存放表头的数组，将显示在表格中
* @param bodyList<HashMap<String,String>> 为存放表格内容的集合，泛型为HashMap<String,String>,key值应与header中的表头名完全一致
* @return
*/
public String generatorXls(String[] header, List<HashMap<String,String>> bodyList){
String files = CofConstants.EXCEL_CREAT_FILES;
Date date = new Date();
SimpleDateFormat datef = new SimpleDateFormat("yyyyMMdd");
String dates = datef.format(date);
files = files+dates;
String filesName = generatorXls(header,bodyList,files);
return filesName;
}
/**
*
* @param header 为存放表头的数组，将显示在表格中
* @param bodyList<HashMap<String,String>> 为存放表格内容的集合，泛型为HashMap<String,String>,key值应与header中的表头名完全一致
* @param filePath 生成文件路径名
* @return
*/
public String generatorXls(String[] header, List<HashMap<String,String>> bodyList,String filePath) {
for(int i = 0; i < bodyList.size(); i++){
if(bodyList.get(i).size()>header.length){
throw CofErrorCode.EXCEL_LENGTH_ERROR.runtimeException();
}
}
String pathLastChar = filePath.substring(filePath.length()-1);
if(!"/".equals(pathLastChar)){
filePath = filePath+"/";
}
Date date = new Date();
SimpleDateFormat datef = new SimpleDateFormat("yyyyMMdd");
SimpleDateFormat timef = new SimpleDateFormat("HHmmssSSS");
String dates = datef.format(date);
String times = timef.format(date);
String fileName ="sxnxOA_"+dates+times+".xls";
//EXCEL生成后本地路径
String excelFullPath=filePath;
File fold = new File(excelFullPath);
File file = new File(excelFullPath + fileName);
if (!fold.exists()) {
fold.mkdirs();
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
throw CofErrorCode.CREAT_FILES_FAILED.runtimeException();
}
}
}
log.info("=====创建文件成功========"+excelFullPath+fileName);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
addHeadRow(sheet,header);
int bodyNum = bodyList.size();
for(int i = 0; i < bodyNum; i++) {
bodyList.get(i);
HSSFRow row = sheet.createRow(i+1);
for(int j = 0; j < header.length; j++) {
row.createCell(j).setCellValue(bodyList.get(i).get(header[j]));
}
}
SavePic(excelFullPath+fileName);
sheetStyle(sheet,header); // 最后添加样式
// 将内存对象输出到文件
FileOutputStream fos = null;
try {
fos = new FileOutputStream(this.outfile);
wb.write(fos);
} catch (Exception e) {
throw CofErrorCode.CREAT_FILES_FAILED.runtimeException();
} finally {
try {
if (fos != null)
fos.close();
} catch (IOException e) {
throw CofErrorCode.CREAT_FILES_FAILED.runtimeException();
}
}
return excelFullPath+fileName;
}
private void addHeadRow(HSSFSheet sheet,String[] header) {
int headerLength = header.length;
HSSFRow row = sheet.createRow(0);
for(int i = 0; i < headerLength; i++) {
row.createCell(i).setCellValue(header[i]);
}
row.setHeightInPoints((float) 12.75);
}
private void sheetStyle(HSSFSheet sheet,String[] header) {
int headerLength = header.length;
for(int i = 0; i < headerLength; i++) {
sheet.setColumnWidth(i, 5000);
}
int rows = sheet.getLastRowNum();
log.info("共计写入行:" + (rows + 1));
// 头部样式
HSSFCellStyle headStyle = sheet.getWorkbook().createCellStyle();
// headStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
// headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
// headStyle.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index); //设置背景色
// headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置前景色
// headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
// headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
// headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
// headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
for(int j = 0; j < header.length; j++) {
sheet.getRow(0).getCell(j).setCellStyle(headStyle);
}
// 给所有单元格添加边框
HSSFCellStyle dataStyle = sheet.getWorkbook().createCellStyle();
// dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
// dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
// dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
// dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
for (int i = 1; i <= rows; i++) {//从第二行开始
for(int j = 0; j < header.length; j++) {
sheet.getRow(i).getCell(j).setCellStyle(dataStyle);
}
}
}
public void SavePic(String outfile) {
if (outfile != null)
this.outfile = outfile;
}
}
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)