生成Excel表格功能
下面是生成excel表格的一个功能，从网上获取下来的，比较老旧的模板，复制代码后，可以直接使用导出功能，无需传入什么参数。
controller中，header 和 list 分别是导出的表头和表格内填充的数据，我们需要修改导出的数据也是在这个逻辑内书写。
jar包版本
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency>
controller层代码示例：
@ResponseBody
@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
public Object exportExcel(HttpServletRequest request, HttpServletResponse response) {
Object json = new Object();
try {
ExportData data = buildExportData(null);
ExcelUtil.exportExcel(data.getTitle(), data.getHeader(), data.getData(), response);
return json;
} catch (Exception e) {
return json;
}
}
private ExportData buildExportData(List list){
String[] header = {"序号1","序号2", "序号3", "序号4", "序号5", "序号6", "序号7", "序号8", "序号9", "序号10", "序号11", "序号12"};
List <String[]> dataList = new ArrayList <String[]>();
for (int i = 0; i < list.size(); i++) {
Object model = list.get(i);
String[] rowData = new String[12];
/* rowData[0] = String.valueOf(i + 1);
rowData[1] = model.getNumber();
rowData[2] = model.getPhase().getName();
rowData[3] = model.getDeliveryWays().getName();
rowData[4] = model.getReceive();
rowData[5] = model.getReceiveOrg();
rowData[6] = model.getSender();
rowData[7] = model.getFileContent();
rowData[8] = model.getCopies() + "";
rowData[9] = model.getCreator().getName();
rowData[10] = DateTimeUtil.format(model.getCreateTime(), "yyyy-MM-dd");
rowData[11] = model.getStampApplyNumber();*/
dataList.add(rowData);
}
ExportData result = new ExportData();
result.setTitle("File-Name");
result.setHeader(header);
result.setData(dataList);
return result;
}
Excel导入和导出功能：
/**
*
*/
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Consumer;
@SuppressWarnings("deprecation")
public class ExcelUtil {
private static short contentHeight = 445;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private HttpServletResponse response;
private int currRowNum = 0;
public ExcelUtil(String name, HttpServletResponse response) {
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet(name);
this.response = response;
sheet.setZoom(75);
}
public void setExportTitle(String title, String companDept, int headerLength) {
setExportTitle_(title, companDept, headerLength, null);
}
public void setExportTitle(String title, String companDept, int headerLength, Consumer <HSSFWorkbook> consumer2) {
setExportTitle_(title, companDept, headerLength, consumer2);
}
private void setExportTitle_(String title, String companDept, int headerLength, Consumer <HSSFWorkbook> consumer2) {
// 产生表格标题行
//设置标题
HSSFCellStyle columnTopStyle = ExcelUtil.getColumnTopStyle(workbook);
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 26);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("宋体");
columnTopStyle.setFont(font);
HSSFRow rowm = sheet.createRow(currRowNum);
rowm.setHeight((short) 610);
HSSFCell cellTiltle = rowm.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(currRowNum, ++currRowNum, 0, headerLength));
cellTiltle.setCellStyle(columnTopStyle);
if (StringUtils.isNotEmpty(companDept) && title.startsWith(companDept)) {
// 设置字体
HSSFFont font3 = workbook.createFont();
//设置字体大小
font3.setFontHeightInPoints((short) 22);
//字体加粗
font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font3.setFontName("宋体");
HSSFRichTextString hssfRichTextString = new HSSFRichTextString(title);
hssfRichTextString.applyFont(0, companDept.length(), font3);
hssfRichTextString.applyFont(companDept.length(), title.length(), font);
cellTiltle.setCellValue(hssfRichTextString);
} else {
HSSFRichTextString hssfRichTextString = new HSSFRichTextString(title);
hssfRichTextString.applyFont(0, title.length(), font);
cellTiltle.setCellValue(hssfRichTextString);
}
if (consumer2 == null) {
HSSFRow row = sheet.createRow(++currRowNum);
row.setHeight(contentHeight);
HSSFCell cell1 = row.createCell(0);
/**
* 日期行
*/
// 设置字体
HSSFFont font2 = workbook.createFont();
//设置字体大小
font2.setFontHeightInPoints((short) 12);
//设置字体名字
font2.setFontName("宋体");
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font2);
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cell1.setCellValue("日期：" + new Date());
cell1.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(currRowNum, currRowNum, 0, headerLength));
} else {
consumer2.accept(workbook);
}
}
public int setHeader(String[][] header) {
HSSFCellStyle headerStyle = ExcelUtil.getColumnTopStyle(workbook);
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 12);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("宋体");
headerStyle.setFont(font);
// 设置背景颜色IndexedColors.LIGHT_YELLOW.getIndex()这个是JAVA提供的，是一个short类型，
//我们需要做的就是用我们自定义RGB将这个颜色替换掉
headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//自定义RGB
HSSFPalette customPalette = workbook.getCustomPalette();
//这里的setColorAtIndex方法需要的参数是(short index, byte red, byte green, byte blue)
//这里的short我们直接用Java给我们提供的，我看有些人用的是自定义的short那个会有问题的
customPalette.setColorAtIndex(IndexedColors.GREEN.getIndex(), (byte) 216, (byte) 228, (byte) 188);
// 定义所需列数
int columnNum = header.length;
int n = 0;
int c = 0;
HSSFRow rowheader = sheet.createRow(++currRowNum);
rowheader.setHeight((short) 650);
HSSFRow rowheader2 = sheet.createRow(++currRowNum); // 在索引2的位置创建行(最顶端的行开始的第二行)
rowheader2.setHeight((short) 450);
while (c < columnNum) {
String[] header_ = header[n];
if (header_.length > 1) {
columnNum += header_.length - 1;
sheet.addMergedRegion(new CellRangeAddress(currRowNum - 1, currRowNum - 1, c, c + (header_.length - 1)));
for (int i = c; i < header_.length + c; i++) {
HSSFCell cellheader = rowheader.createCell(i); //创建列头对应个数的单元格
cellheader.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型\
cellheader.setCellStyle(headerStyle);
if (i == c) {
HSSFRichTextString text = new HSSFRichTextString(header_[0].split("\\|")[0]);
cellheader.setCellValue(text); //设置列头单元格的值
}
HSSFCell cellheader2 = rowheader2.createCell(i);
cellheader2.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text2 = new HSSFRichTextString(header_[i - c].split("\\|")[1]);
cellheader2.setCellValue(text2); //设置列头单元格的值
cellheader2.setCellStyle(headerStyle);
}
c += header_.length;
} else {
HSSFCell cellheader = rowheader.createCell(c); //创建列头对应个数的单元格
cellheader.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型\
cellheader.setCellStyle(headerStyle);
sheet.addMergedRegion(new CellRangeAddress(currRowNum - 1, currRowNum, c, c));
HSSFRichTextString text = new HSSFRichTextString(header_[0]);
cellheader.setCellValue(text); //设置列头单元格的值
HSSFCell cellheader2 = rowheader2.createCell(c);
cellheader2.setCellStyle(headerStyle);
c++;
}
n++;
}
//行冻结
getSheet().createFreezePane(0, currRowNum + 1, 0, currRowNum + 1);
return columnNum;
}
public void exportExcel(List <String[]> list, int columnNum, Consumer <HSSFSheet> consumer, Consumer <HSSFSheet> consumer2) {
try {
HSSFCellStyle style = getHssfCellStyle();
setExportData(list, response, workbook, sheet, style, columnNum, ++currRowNum, consumer, consumer2);
} catch (Exception e) {
e.printStackTrace();
}
}
public HSSFCellStyle getHssfCellStyle() {
//sheet样式定义【getColumnTopStyle()/getStyle()为自定义方法】
HSSFCellStyle style = getStyle(workbook); //单元格样式对象
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 12);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("宋体");
style.setFont(font);
return style;
}
private static void setExportData(List <String[]> list,
HttpServletResponse response,
HSSFWorkbook workbook,
HSSFSheet sheet, HSSFCellStyle style,
int columnNum, int hreadNum, Consumer <HSSFSheet> consumer,
Consumer <HSSFSheet> consumer2) {
//将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < list.size(); i++) {
String[] obj = list.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i + hreadNum);//创建所需的行数
row.setHeight(contentHeight);
for (int j = 0; j < obj.length; j++) {
HSSFCell cell = null; //设置单元格的数据类型
// if(j == 0){
// cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
// cell.setCellValue(i+1);
// }else{
// cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
// if(!"".equals(obj[j]) && obj[j] != null){
// cell.setCellValue((String)obj[j]); //设置单元格的值
// }
// }
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(obj[j]);
cell.setCellStyle(style); //设置单元格样式
}
}
if (consumer != null) {
consumer.accept(sheet);
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getRichStringCellValue() != null && currentCell.getStringCellValue() != null) {
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
String stringCellValue = currentCell.getStringCellValue();
int length = 0;
if (stringCellValue.lastIndexOf("\n") != -1) {
//length = stringCellValue.substring(0,stringCellValue.indexOf("\n")).getBytes().length;
String[] split = stringCellValue.split("\n");
//new Stream<String>().
List <String> strings = Arrays.asList(split);
length = strings.stream().map(s -> s.getBytes().length).max(Integer::compare).get();
if (split.length > 1)
currentRow.setHeight((short) ((split.length) * contentHeight));
} else {
length = stringCellValue.getBytes().length;
}
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
if ((columnWidth + 4) * 256 > 255 * 255) {
sheet.setColumnWidth(colNum, 125 * 255);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}
}
if (consumer2 != null) {
consumer2.accept(sheet);
}
if (workbook != null) {
OutputStream outputStream = null;
try {
String fileName = "Excel-" + new String(sheet.getSheetName().getBytes("gbk"), "iso8859-1") + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("application/msexcel");
response.setHeader("Content-Disposition", headStr);
outputStream = response.getOutputStream();
// File file = new File("d:/text.xls");//可能会抛异常：NullPointerException
// if(!file.exists()){
// file.createNewFile();
// }
// outputStream = new FileOutputStream(file);//1.打开资源：输出文件流；2.可能会抛异常：FileNotFoundException
workbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
} finally {
//正确关闭文件流
try {
workbook.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
try {
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
}
public static void exportExcel(String title, String[] header, List <String[]> list, HttpServletResponse response) {
try {
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()为自定义方法】
HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = getStyle(workbook); //单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (header.length - 1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 定义所需列数
int columnNum = header.length;
HSSFRow rowheader = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
HSSFCell cellheader = rowheader.createCell(n); //创建列头对应个数的单元格
cellheader.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(header[n]);
cellheader.setCellValue(text); //设置列头单元格的值
cellheader.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
setExportData(list, response, workbook, sheet, style, columnNum, 3, null, null);
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
public static List <Map <String, Object>> excelFileConvertToList(FileInputStream fis, int headRow, Map <String, Object> headFielMap) throws Exception {
Workbook wb = WorkbookFactory.create(fis);
Sheet sheet = wb.getSheetAt(0);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
List <Map <String, Object>> rows = new ArrayList <Map <String, Object>>();
boolean isAllNull = true;
for (int i = headRow; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
isAllNull = true;
Map <String, Object> cells = new HashMap <String, Object>();
for (Cell cell : row) {
Object obj = null;
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
obj = cell.getRichStringCellValue().getString().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
obj = format.format(new Date(cell.getDateCellValue().getTime()));
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
if (temp.indexOf(".") > -1) {
obj = String.valueOf(new BigDecimal(temp)).trim();
} else {
obj = temp.trim();
}
}
break;
case Cell.CELL_TYPE_BOOLEAN:
obj = Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
if (temp != null) {
temp = temp.replace("#N/A", "");
}
obj = temp;
break;
default:
obj = "";
}
if (obj != null && !"".equals(obj)) isAllNull = false;
if (headFielMap.containsKey(String.valueOf(cell.getColumnIndex()))) cells.put(headFielMap.get(String.valueOf(cell.getColumnIndex())).toString(), obj);
}
if (!isAllNull) rows.add(cells);
}
return rows;
}
public static File excelFileToFile(MultipartFile mutiFile, HttpServletRequest request) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String path = request.getSession().getServletContext().getRealPath("upload");
String saveFilePath = "D:\\uploadFile";
String fileName = mutiFile.getOriginalFilename();
File targetFile = new File(path, fileName);
if (!targetFile.exists()) {
targetFile.mkdirs();
}
mutiFile.transferTo(targetFile);
int index = fileName.indexOf(".");
String stuff = fileName.substring(index);
String sfileName = fileName.substring(0, index) + sdf.format(System.currentTimeMillis()) + stuff;
File saveFile = new File(saveFilePath, sfileName);
FileInputStream input = new FileInputStream(targetFile);
FileOutputStream output = new FileOutputStream(saveFile);
try {
int in = input.read();
while (in != -1) {
output.write(in);
in = input.read();
}
} catch (Exception e) {
System.out.println(e.getMessage());
throw e;
} finally {
input.close();
output.close();
}
return targetFile;
}
public static Map <String, Object> getExcelheadFieldMap(String[] heads) {
Map <String, Object> result = new HashMap <String, Object>();
for (int i = 0; i < heads.length; i++) {
result.put(String.valueOf(i), (heads[i].split(":"))[0]);
}
return result;
}
public static Map <String, String> getExcelheadFieldDispMap(String[] heads) {
Map <String, String> result = new HashMap <String, String>();
for (int i = 0; i < heads.length; i++) {
String[] items = heads[i].split(":");
result.put(items[0], items[1]);
}
return result;
}
public static void CheckRepeated(List <Map <String, Object>> lists, String[] specifiedFields, int headRow, Map <Integer, String> rMap) {
if (specifiedFields.length > 0) {
Map <String, Integer> cMap = new HashMap <String, Integer>();
for (int i = 0; i < lists.size(); i++) {
Map <String, Object> map = lists.get(i);
String kstr = "";
for (String field : specifiedFields) {
kstr += map.get(field) == null ? "" : map.get(field).toString() + ",";
}
if (cMap.containsKey(kstr)) {
String temp = rMap.containsKey(i) ? rMap.get(i) + ";" + "与第" + (cMap.get(kstr) + headRow + 1) + "行重复" : "与第" + (cMap.get(kstr) + headRow + 1) + "行重复";
rMap.put(i, temp);
} else {
cMap.put(kstr, i);
}
}
}
}
public static void CheckNull(List <Map <String, Object>> lists, String[] specifiedFields, Map <String, String> fieldDispMap, Map <Integer, String> rMap) {
if (specifiedFields.length > 0) {
for (int i = 0; i < lists.size(); i++) {
Map <String, Object> map = lists.get(i);
for (String field : specifiedFields) {
if (map.get(field) != null) {
String dispName = fieldDispMap.get(field);
String temp = rMap.containsKey(i) ? rMap.get(i) + ";" + dispName + "不能为空" : dispName + "不能为空";
rMap.put(i, temp);
}
}
}
}
}
public static void exportCheckResult(File file, Map <Integer, String> checkMap, int headRow, int columns, HttpServletResponse response) throws Exception {
Workbook wb = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = wb.getSheetAt(0);
for (Integer i : checkMap.keySet()) {
int rownum = headRow + i;
Row row = sheet.getRow(rownum);
if (row == null) continue;
Cell cell = row.createCell(columns);
if (cell == null) continue;
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor((short) 10);
cell.setCellStyle(style);
cell.setCellValue(checkMap.get(i));
}
String fileName = "error-" + new String(file.getName().getBytes("gbk"), "iso8859-1");
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("application/msexcel");
response.setHeader("Content-Disposition", headStr);
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (Exception e) {
// TODO: handle exception
System.out.println(e.getMessage());
throw e;
} finally {
if (outputStream != null) outputStream.close();
wb.close();
file.delete();
}
}
public static String saveCheckResult(File file, Map <Integer, String> checkMap, int headRow, int columns, HttpServletRequest request) throws Exception {
Workbook wb = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = wb.getSheetAt(0);
for (Integer i : checkMap.keySet()) {
int rownum = headRow + i;
Row row = sheet.getRow(rownum);
if (row == null) continue;
Cell cell = row.createCell(columns);
if (cell == null) continue;
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor((short) 10);
cell.setCellStyle(style);
cell.setCellValue(checkMap.get(i));
}
int index = file.getName().indexOf(".");
String stuff = file.getName().substring(index);
String fileName = "error-" + file.getName().substring(0, index) + String.valueOf(System.currentTimeMillis()).substring(4, 13) + stuff;
String path = request.getSession().getServletContext().getRealPath("upload");
File targetFile = new File(path, fileName);
FileOutputStream outputStream = new FileOutputStream(targetFile);
try {
wb.write(outputStream);
} catch (Exception e) {
throw e;
} finally {
if (outputStream != null) outputStream.close();
wb.close();
file.delete();
}
return path + "\\" + fileName;
}
public static void downloadErrorFile(String filePath, HttpServletRequest request, HttpServletResponse response) throws Exception {
File file = new File(filePath);
if (file.exists()) {
FileInputStream inputStream = new FileInputStream(file);
InputStream fis = new BufferedInputStream(inputStream);
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;fileName=" + new String(file.getName().getBytes("gbk"), "iso8859-1"));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
String extName = filePath.substring(filePath.lastIndexOf(".")).toUpperCase();
if (".XLS".equals(extName)) {
response.setHeader("Content-type", "application/vnd.ms-excel");
} else if (".XLSX".equals(extName)) {
response.setHeader("Content-type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else {
response.setContentType("multipart/form-data");
}
toClient.write(buffer);
toClient.flush();
toClient.close();
file.delete();
}
}
public HSSFWorkbook getWorkbook() {
return workbook;
}
public HSSFSheet getSheet() {
return sheet;
}
public int getCurrRowNum() {
return currRowNum;
}
public void setCurrRowNum(int currRowNum) {
this.currRowNum = currRowNum;
}
public static void main(String[] args) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date now = new Date();
System.out.println(format.format(now));
}
public void setExportTitleSpecial(String title, String companDept, int headerLength) {
// 产生表格标题行
//设置标题
HSSFCellStyle columnTopStyle = ExcelUtil.getColumnTopStyle(workbook);
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 26);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("宋体");
columnTopStyle.setFont(font);
HSSFRow rowm = sheet.createRow(currRowNum);
rowm.setHeight((short) 610);
HSSFCell cellTiltle = rowm.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(currRowNum, ++currRowNum, 0, headerLength));
cellTiltle.setCellStyle(columnTopStyle);
// 设置字体
HSSFFont font3 = workbook.createFont();
//设置字体大小
font3.setFontHeightInPoints((short) 22);
//字体加粗
font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font3.setFontName("宋体");
HSSFRichTextString hssfRichTextString = new HSSFRichTextString(title);
hssfRichTextString.applyFont(0, companDept.length(), font3);
hssfRichTextString.applyFont(companDept.length(), title.length(), font);
cellTiltle.setCellValue(hssfRichTextString);
HSSFRow row = sheet.createRow(++currRowNum);
row.setHeight(contentHeight);
HSSFCell cell1 = row.createCell(0);
/**
* 日期行
*/
// 设置字体
HSSFFont font2 = workbook.createFont();
//设置字体大小
font2.setFontHeightInPoints((short) 12);
//设置字体名字
font2.setFontName("宋体");
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font2);
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cell1.setCellValue("日期：" + new Date() + " 单位：元");
cell1.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(currRowNum, currRowNum, 0, headerLength));
}
}
导出对象模型：
import java.io.Serializable;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
public class ExportData implements Serializable {
private static final long serialVersionUID = 1L;
private String title;
private String[] header;
private List<String[]> data;
private List <Map<String, Object>> data_;
public List <Map<String, Object>> getData_() {
return data_;
}
public void setData_(List <Map<String, Object>> data_) {
this.data_ = data_;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String[] getHeader() {
return header;
}
public void setHeader(String[] header) {
this.header = header;
}
public List<String[]> getData() {
return data;
}
public void setData(List<String[]> data) {
this.data = data;
}
@Override
public String toString() {
return "ExcelData [title=" + title + ", header=" + Arrays.toString(header) + ", data=" + data + "]";
}
}
浙公网安备 33010602011771号