import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.GET;
import javax.ws.rs.HeaderParam;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
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.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 下载excel实例
*
* @author zql
*/
public class DownloadExcel {
/**
* 空白模板实例
*
* @param userAgent
* @return
*/
@GET @Path("/downloadExcelEmpty") @Produces(MediaType.APPLICATION_JSON)
public void downloadExcelEmpty(@HeaderParam("user-agent") String userAgent, @Context HttpServletResponse response, @Context HttpServletRequest request) {
try {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
String fileName = "空白模板.xlsx";
// 处理文件名乱码问题
fileName=userAgent.toLowerCase().indexOf("msie") == -1 ? new String(fileName.getBytes("UTF-8"), "ISO8859-1") : URLEncoder.encode(fileName, "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
// 声明一个工作薄
Workbook workbook = new XSSFWorkbook();
/**
* 第一个Sheet开始
*/
// 在webbook中添加第一个sheet,对应Excel文件中的sheet
Sheet sheet = workbook.createSheet("信息表");
// 设置第一列的宽度
sheet.setColumnWidth(0, (25 * 256));
String[] oneSheetRow = {"内容","编号","名称","单位","负责人","电话","地点","日期","测定负责人","测定负责人电话","项目特征","条件及情况","气温","天气","起时间","止时间","总用时","总产量","结束"};
Row row = null;
for (int i = 0; i < 19; i++) {
row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue(oneSheetRow[i]);
}
/**
* 第二个Sheet开始
*/
// 在webbook中添加第二个sheet
Sheet sheet2 = workbook.createSheet("消耗表");
String[] twoSheetRow = {"序号","","","人数","起","止","延续(分钟)","单位","产量","用量","备注"};
// 新建一个样式
CellStyle style2 = workbook.createCellStyle();
// 水平居中
style2.setAlignment(CellStyle.ALIGN_CENTER);
// 垂直居中
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置自动换行
style2.setWrapText(true);
// 设置边框
style2.setBorderBottom(CellStyle.BORDER_THIN);
style2.setBorderLeft(CellStyle.BORDER_THIN);
style2.setBorderRight(CellStyle.BORDER_THIN);
Row TwoSheetOneRow = sheet2.createRow(0);
Row TwoSheetTwoRow = sheet2.createRow(1);
for (int i = 0; i < 11; i++) {
if (i == 1 || i == 2) {
continue;
}
if (i == 4 || i == 5 || i == 6) {
Cell cell = TwoSheetTwoRow.createCell(i);
cell.setCellValue(twoSheetRow[i]);
cell.setCellStyle(style2);
continue;
}
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress = new CellRangeAddress(0, 1, i, i);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress);
Cell cell = TwoSheetOneRow.createCell(i);
cell.setCellValue(twoSheetRow[i]);
cell.setCellStyle(style2);
}
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress2_1 = new CellRangeAddress(0, 0, 4, 6);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress2_1, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress2_1, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress2_1, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress2_1);
Cell cell2_1 = TwoSheetOneRow.createCell(4);
cell2_1.setCellValue("时间");
cell2_1.setCellStyle(style2);
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress2_2 = new CellRangeAddress(0, 1, 1, 2);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress2_2, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress2_2, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress2_2, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress2_2);
Cell cell2_2 = TwoSheetOneRow.createCell(1);
cell2_2.setCellValue("过程组成");
cell2_2.setCellStyle(style2);
String[] twoSheetCell = {"","拆除","拨出","更换","拨入","调整","连接","配、锯、打","配件","扣件","整改及补充"};
Row row2 = null;
// 行
for (int i = 2; i < 14; i++) {
// 创建行
row2 = sheet2.createRow(i);
Cell cell = null;
// 列
for (int j = 0; j < 11; j++) {
// 序号
if (j == 0) {
cell = row2.createCell(j);
cell.setCellValue(i - 1);
cell.setCellStyle(style2);
continue;
}
if (i != 2 && i != 13 && j == 2) {
cell = row2.createCell(j);
cell.setCellValue(twoSheetCell[i - 2]);
cell.setCellStyle(style2);
continue;
}
if (i == 12 && j == 1) {
cell = row2.createCell(j);
cell.setCellValue("检查");
cell.setCellStyle(style2);
continue;
}
cell = row2.createCell(j);
cell.setCellStyle(style2);
}
}
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress2_3 = new CellRangeAddress(2, 2, 1, 2);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress2_3, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress2_3, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress2_3, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress2_3);
Cell cell2_3 = sheet2.getRow(2).createCell(1);
cell2_3.setCellValue("准备");
cell2_3.setCellStyle(style2);
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress2_4 = new CellRangeAddress(3, 6, 1, 1);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress2_4, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress2_4, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress2_4, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress2_4);
Cell cell2_4 = sheet2.getRow(3).createCell(1);
cell2_4.setCellValue("拆除");
cell2_4.setCellStyle(style2);
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress2_5 = new CellRangeAddress(7, 11, 1, 1);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress2_5, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress2_5, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress2_5, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress2_5);
Cell cell2_5 = sheet2.getRow(7).createCell(1);
cell2_5.setCellValue("连接");
cell2_5.setCellStyle(style2);
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress2_6 = new CellRangeAddress(13, 13, 1, 2);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress2_6, sheet2, workbook);
RegionUtil.setBorderLeft(1, rangeAddress2_6, sheet2, workbook);
RegionUtil.setBorderRight(1, rangeAddress2_6, sheet2, workbook);
// 添加要合并地址到表格
sheet2.addMergedRegion(rangeAddress2_6);
Cell cell2_6 = sheet2.getRow(13).createCell(1);
cell2_6.setCellValue("结束");
cell2_6.setCellStyle(style2);
/**
* 第三个Sheet开始
*/
// 在webbook中添加第三个sheet
Sheet sheet3 = workbook.createSheet("测定表");
String[] threeSheetRow = {"序号","代号","名称规格","单位","消耗量","次数","损耗率","消耗量","测定用量","备注"};
// 新建一个样式
XSSFCellStyle style3 = (XSSFCellStyle) workbook.createCellStyle();
style3.setBorderBottom(CellStyle.BORDER_THIN);
style3.setBorderLeft(CellStyle.BORDER_THIN);
style3.setBorderRight(CellStyle.BORDER_THIN);
// 新建一个有背景色的样式
XSSFCellStyle style3_bg = (XSSFCellStyle) workbook.createCellStyle();
style3_bg.setBorderBottom(CellStyle.BORDER_THIN);
style3_bg.setBorderLeft(CellStyle.BORDER_THIN);
style3_bg.setBorderRight(CellStyle.BORDER_THIN);
// 设置填充方案
style3_bg.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置填充前景颜色
style3_bg.setFillForegroundColor(new XSSFColor(new Color(146, 208, 80)));
// 新建一个样式
XSSFCellStyle style3_font = (XSSFCellStyle) workbook.createCellStyle();
style3_font.setBorderBottom(CellStyle.BORDER_THIN);
style3_font.setBorderLeft(CellStyle.BORDER_THIN);
style3_font.setBorderRight(CellStyle.BORDER_THIN);
// 新建一个字体样式
XSSFFont xf = (XSSFFont) workbook.createFont();
// 给字体定义颜色
xf.setColor(new XSSFColor(new Color(255, 0, 0)));
// 把字体添加到样式
style3_font.setFont(xf);
Row threeSheetOneRow = null;
for (int i = 0; i < 3; i++) {
threeSheetOneRow = sheet3.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = threeSheetOneRow.createCell(j);
// 第一行是标题
if (i == 0) {
cell.setCellValue(threeSheetRow[j]);
}
// 第二列字体是红色的
if (j == 1) {
cell.setCellStyle(style3_font);
// 注意要结束当前,否则会被覆盖样式
continue;
}
// 第五列和第六列,样式不一样
if (j == 5 || j == 6) {
cell.setCellStyle(style3_bg);
continue;
}
cell.setCellStyle(style3);
}
}
/**
* 第四个Sheet开始
*/
// 在webbook中添加第四个sheet
Sheet sheet4 = workbook.createSheet("其它测定表");
// 新建一个样式
CellStyle style4 = workbook.createCellStyle();
// 水平居中
style4.setAlignment(CellStyle.ALIGN_CENTER);
// 垂直居中
style4.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置自动换行
style4.setWrapText(true);
// 设置边框
style4.setBorderLeft(CellStyle.BORDER_THIN);
style4.setBorderRight(CellStyle.BORDER_THIN);
style4.setBorderBottom(CellStyle.BORDER_THIN);
String[] fourSheetCol = {"序号","代号","名称型号","起","止","延续(分钟)","数量","人数","利用系数","用量"};
Row fourSheetOneRow = sheet4.createRow(0);
Row fourSheetTwoRow = sheet4.createRow(1);
for (int i = 0; i < 10; i++) {
if (i == 3 || i == 4 || i == 5) {
Cell cell = fourSheetTwoRow.createCell(i);
cell.setCellValue(fourSheetCol[i]);
cell.setCellStyle(style4);
continue;
}
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress = new CellRangeAddress(0, 1, i, i);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress, sheet4, workbook);
RegionUtil.setBorderLeft(1, rangeAddress, sheet4, workbook);
RegionUtil.setBorderRight(1, rangeAddress, sheet4, workbook);
// 添加要合并地址到表格
sheet4.addMergedRegion(rangeAddress);
Cell cell = fourSheetOneRow.createCell(i);
cell.setCellValue(fourSheetCol[i]);
cell.setCellStyle(style4);
}
// 指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress4 = new CellRangeAddress(0, 0, 3, 5);
// 添加要合并地址到表格
sheet4.addMergedRegion(rangeAddress4);
Cell cell4 = fourSheetOneRow.createCell(3);
cell4.setCellValue("时间");
cell4.setCellStyle(style4);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, rangeAddress4, sheet4, workbook);
RegionUtil.setBorderLeft(1, rangeAddress4, sheet4, workbook);
RegionUtil.setBorderRight(1, rangeAddress4, sheet4, workbook);
// 新建一个样式
CellStyle style4_thin = workbook.createCellStyle();
// 设置粗连界样式,BORDER_THICK粗连界
style4_thin.setBorderBottom(CellStyle.BORDER_THIN);
style4_thin.setBorderLeft(CellStyle.BORDER_THIN);
style4_thin.setBorderRight(CellStyle.BORDER_THIN);
Row rowThin4= null;
for (int i = 2; i < 6; i++) {
rowThin4 = sheet4.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = rowThin4.createCell(j);
cell.setCellStyle(style4_thin);
}
}
try {
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
效果如下