Excel下载

依赖

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

Controller

@RequestMapping("/down")

public void exportExcel(HttpServletRequest request, HttpServletResponse response)throws Exception {

OutputStream output = response.getOutputStream();

response.reset();

response.setCharacterEncoding("UTF-8");

String fileNeme ="下载" + System.currentTimeMillis() +".xlsx";

//设置下载的excel 文件名支持中文

fileNeme = URLEncoder.encode(fileNeme,"UTF-8");

response.setHeader("Content-disposition","attachment;filename=" + fileNeme);

response.setContentType("application/vnd.ms-excel;charset=utf-8");

String[] titles = {"测试表头","测试表头2"};

List> list =new ArrayList<>();

Map map =new HashMap<>();

map.put("0","测试");

map.put("1","测2");

list.add(map);

ExcelUtil.export2007ExcelByPOI("sheet", titles, list, output);

}

工具类

package com.start.utils;

import java.io.File;

import java.io.FileInputStream;

import java.io.OutputStream;

import java.text.DateFormat;

import java.text.NumberFormat;

import java.text.SimpleDateFormat;

import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

/** EXCEL 2003 */

private static final StringEXCEL_XLS ="xls";

/** EXCEL 2007/2010 */

private static final StringEXCEL_XLSX ="xlsx";

private ExcelUtil() {

}

/**

* 从EXCEl中读取数据，结构为：单元格一行用一个map记录，key：是单元格数字索引，从1开始，value：单元格的值

* @param file

* @return

* @throws Exception

public static List> readExcel(File file)throws Exception {

if (!file.exists()) {

return null;

}

List> dataList =new ArrayList>();

FileInputStream fis =new FileInputStream(file);

Workbook workBook =null;

if (file.getName().endsWith(EXCEL_XLS)) {// Excel 2003

workBook =new HSSFWorkbook(fis);

}else if (file.getName().endsWith(EXCEL_XLSX)) {// Excel 2007/2010

workBook =new XSSFWorkbook(fis);

}

int numberOfSheets = workBook.getNumberOfSheets();

for (int s =0; s < numberOfSheets; s++) {

Sheet sheetAt = workBook.getSheetAt(s);

// 获取标题行

Row titleRow = sheetAt.getRow(0);

// 获取总列数

int colCount = titleRow.getLastCellNum();

// 获取当前Sheet的总列数

int rowsCount = sheetAt.getPhysicalNumberOfRows();

for (int rowIndex =1; rowIndex < rowsCount; rowIndex++) {// 总行

Row currentRow = sheetAt.getRow(rowIndex);

if (currentRow ==null) {

continue;

}

// 定义返回的数据：key：列数，从1 开始；object ：列对应的值一个Map，一行数据

Map mapValue =new HashMap();

// 读取每一列的信息

for (int colIndex =0; colIndex < colCount; colIndex++) {

Cell cell = currentRow.getCell(colIndex);

if (cell ==null) {

continue;

}

String cellValue =null;

CellType cellType = cell.getCellType();

// 文本

if (cellType == CellType.STRING) {

cellValue = cell.getStringCellValue();

}

// 数字

else if (cellType == CellType.NUMERIC) {

// 先看是否是日期格式

if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {

// 读取日期格式

Date dateValue = cell.getDateCellValue();

SimpleDateFormat sdf =new SimpleDateFormat("yyyy-mm-dd");

cellValue = sdf.format(dateValue);

}else {

// cell.setBlank();

NumberFormat nf = NumberFormat.getInstance();

cellValue = nf.format(cell.getNumericCellValue());

if (cellValue.indexOf(",") >=0) {

cellValue = cellValue.replace(",","");

}

// 公式

else if (cellType == CellType.FORMULA) {

cellValue = String.valueOf(cell.getCellFormula());

}

// 空值

else if (cellType == CellType.BLANK) {

cellValue ="";

}

// BOOLEAN

else if (cellType == CellType.BOOLEAN) {

cellValue = String.valueOf(cell.getBooleanCellValue());

}

// ERROR

else if (cellType == CellType.ERROR) {

cellValue = String.valueOf(cell.getErrorCellValue());

}

if (cellValue !=null) {

mapValue.put(String.valueOf(colIndex +1), cellValue);

}

if (!mapValue.isEmpty()) {

dataList.add(mapValue);

}

return dataList;

}

/**

* 数据导入到EXCEL中，只支持导入到一个sheet页，格式为xlsx

* @param sheetName

* sheet页名称

* @param titles

* 表格首行字段名称

* @param dataMap

* 数据信息

* @param outputStream

* @throws Exception

public static void export2007ExcelByPOI(String sheetName, String[] titles, List> dataMap,

OutputStream outputStream)throws Exception {

// 声明一个工作簿【SXSSFWorkbook只支持.xlsx格式】

Workbook workbook =new SXSSFWorkbook(1000);// 内存中只存放1000条

// 生成一个表格

Sheet sheet = workbook.createSheet(sheetName);

// 设置表格的默认宽度为18个字节

sheet.setDefaultColumnWidth(18);

// 生成一个样式【用于表格标题】

CellStyle headStyle = workbook.createCellStyle();

// 设置单元格背景色

headStyle.setFillForegroundColor(HSSFColorPredefined.SKY_BLUE.getIndex());

headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

headStyle.setBorderTop(BorderStyle.THIN);// 单元格上边框

headStyle.setBorderBottom(BorderStyle.THIN);// 单元格下边框

headStyle.setBorderLeft(BorderStyle.THIN);// 单元格左边框

headStyle.setBorderRight(BorderStyle.THIN);// 单元格右边框

headStyle.setAlignment(HorizontalAlignment.CENTER);// 单元格水平居中

headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 单元格垂直居中

// 生成字体【用于表格标题】

Font headFont = workbook.createFont();

headFont.setFontHeightInPoints((short)12);

// 把字体应用到当前样式

headStyle.setFont(headFont);

// 生成一个样式【用于Excel中的表格内容】

CellStyle contentStyle = workbook.createCellStyle();

// 设置样式【用于Excel中的表格内容】

contentStyle.setBorderTop(BorderStyle.THIN);// 单元格上边框

contentStyle.setBorderBottom(BorderStyle.THIN);// 单元格下边框

contentStyle.setBorderLeft(BorderStyle.THIN);// 单元格左边框

contentStyle.setBorderRight(BorderStyle.THIN);// 单元格右边框

contentStyle.setAlignment(HorizontalAlignment.CENTER);// 单元格水平居中

contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 单元格垂直居中

contentStyle.setWrapText(true);// 单元格自动换行

// 生成字体

Font contentFont = workbook.createFont();

// 把字体应用到当前样式

contentStyle.setFont(contentFont);

// 产生表格标题行【表格的第一行】

Row headRow = sheet.createRow(0);

for (int i =0; i < titles.length; i++) {

Cell cell = headRow.createCell(i);

// 设置单元格为文本格式

cell.setBlank();

cell.setCellStyle(headStyle);

RichTextString text =new XSSFRichTextString(titles[i]);

cell.setCellValue(text);

}

// 遍历集合数据，产生EXCEL行【Excel表格的标题占用了一行】

int index =1;

for (Map temp : dataMap) {

// 创建一行

Row row = sheet.createRow(index);

Set keys = temp.keySet();

for (int i =0; i < keys.size(); i++) {

Cell cell = row.createCell(i);

cell.setCellStyle(contentStyle);

Object value = temp.get(String.valueOf(i));

String textValue ="";

// 判断类型之后进行类型转换

if (value ==null) {

cell.setCellValue(textValue);

}else if (valueinstanceof Integer) {

cell.setCellValue((Integer)value);

}else if (valueinstanceof Long) {

cell.setCellValue((Long)value);

}else if (valueinstanceof Double) {

cell.setCellValue((Double)value);

}else if (valueinstanceof Boolean) {

cell.setCellValue((Boolean)value);

}else if (valueinstanceof Date) {

DateFormat defaultFormatter =new SimpleDateFormat("yyyy-MM-dd");

textValue = defaultFormatter.format((Date)value);

}else if (valueinstanceof byte[]) {

}else {

textValue = value.toString();

}

// 如果是字符串

if (!"".equals(textValue)) {

RichTextString richString =new XSSFRichTextString(textValue);

// 设置单元格为文本格式

cell.setBlank();

cell.setCellValue(richString);

}

// 行加1

index++;

}

workbook.write(outputStream);

outputStream.flush();

workbook.close();

}

Excel下载

推荐阅读更多精彩内容