maven:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
java代码:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
/**
* excel导出工具类
*/
public class ExcelUtils {
// excel默认宽度;
private static int width = 256 * 14;
// 默认字体
private static String excelfont = "微软雅黑";
private static HSSFWorkbook wb;
private static HSSFWorkbook workbookRead;
/**
* @param excelName 导出的EXCEL名字
* @param headers 导出的表格的表头
* @param ds_titles 导出的数据 map.get(key) 对应的 key
* @param data 数据集 List<Map>
*/
public static void export(String excelName, String[] headers, String[] ds_titles, List<Map<String, Object>> data,
HttpServletRequest request, HttpServletResponse response)
throws IOException {
export(excelName, "sheet1", headers, ds_titles, data, request, response);
}
/**
* @param excelName 导出的EXCEL名字
* @param sheetName 导出的SHEET名字 当前sheet数目只为1
* @param headers 导出的表格的表头
* @param ds_titles 导出的数据 map.get(key) 对应的 key
* @param data 数据集 List<Map>
*/
public static void export(String excelName, String sheetName, String[] headers, String[] ds_titles, List<Map<String, Object>> data,
HttpServletRequest request, HttpServletResponse response)
throws IOException {
int[] ds_format = new int[ds_titles.length];
int[] widths = new int[ds_titles.length];
for(int i=0;i<ds_titles.length;i++){
ds_format[i] = 1;
widths[i] = width;
}
export(excelName, sheetName, headers, ds_titles, ds_format, widths, data, request, response);
}
/**
* @param excelName 导出的EXCEL名字
* @param sheetName 导出的SHEET名字 当前sheet数目只为1
* @param headers 导出的表格的表头
* @param ds_titles 导出的数据 map.get(key) 对应的 key
* @param ds_format 导出数据的样式
* 1:String left; 2:String center 3:String right 4 int right 5:float ###,###.## right 6:number: #.00% 百分比 right
* @param widths 表格的列宽度 默认为 256*14
* @param data 数据集 List<Map>
*/
public static void export(String excelName, String sheetName, String[] headers, String[] ds_titles, int[] ds_format,
int[] widths, List<Map<String, Object>> data, HttpServletRequest request, HttpServletResponse response)
throws IOException {
HttpSession session = request.getSession();
session.setAttribute("state", null);
if (widths == null) {
widths = new int[ds_titles.length];
for (int i = 0; i < ds_titles.length; i++) {
widths[i] = width;
}
}
if (ds_format == null) {
ds_format = new int[ds_titles.length];
for (int i = 0; i < ds_titles.length; i++) {
ds_format[i] = 1;
}
}
// 设置文件名
String fileName = "";
if (StringUtils.isNotEmpty(excelName)) {
fileName = excelName;
}
wb = new HSSFWorkbook();
// 创建一个sheet
HSSFSheet sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName) ? sheetName : "excel");
// 创建表头,如果没有跳过
int headerrow = 0;
if (headers != null) {
HSSFRow row = sheet.createRow(headerrow);
// 表头样式
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBold(true);
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 11);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth((short) i, (short) widths[i]);
HSSFCell cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
}
headerrow++;
}
// 表格主体 解析list
if (data != null) {
List<HSSFCellStyle> styleList = new ArrayList<HSSFCellStyle>();
for (int i = 0; i < ds_titles.length; i++) { // 列数
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
if (ds_format[i] == 1) {
style.setAlignment(HorizontalAlignment.LEFT);
} else if (ds_format[i] == 2) {
style.setAlignment(HorizontalAlignment.CENTER);
} else if (ds_format[i] == 3) {
style.setAlignment(HorizontalAlignment.RIGHT);
// 整型
} else if (ds_format[i] == 4) {
style.setAlignment(HorizontalAlignment.RIGHT);
// 整型
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
} else if (ds_format[i] == 5) {
// float类型
style.setAlignment(HorizontalAlignment.RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
} else if (ds_format[i] == 6) {
// 百分比类型
style.setAlignment(HorizontalAlignment.RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
}
styleList.add(style);
}
for (int i = 0; i < data.size(); i++) { // 行数
HSSFRow row = sheet.createRow(headerrow);
@SuppressWarnings("rawtypes")
Map map = data.get(i);
for (int j = 0; j < ds_titles.length; j++) { // 列数
HSSFCell cell = row.createCell(j);
Object o = map.get(ds_titles[j]);
if (o == null || "".equals(o)) {
cell.setCellValue("");
} else if (ds_format[j] == 4) {
cell.setCellValue((Long.valueOf((map.get(ds_titles[j])) + "")).longValue());
} else if (ds_format[j] == 5 || ds_format[j] == 6) {
cell.setCellValue((Double.valueOf((map.get(ds_titles[j])) + "")).doubleValue());
} else {
cell.setCellValue(map.get(ds_titles[j]) + "");
}
cell.setCellStyle((HSSFCellStyle) styleList.get(j));
}
headerrow++;
}
}
fileName = fileName + ".xls";
String filename = "";
try {
filename = encodeChineseDownloadFileName(request, fileName);
} catch (Exception e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", filename);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
response.setHeader("Pragma", "No-cache");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
session.setAttribute("state", "open");
}
/**
* 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性
*/
public static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName) throws Exception {
String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent) {
if (-1 != agent.indexOf("Firefox")) {
filename = "=?UTF-8?B?" + (new String(Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
} else if (-1 != agent.indexOf("Chrome")) {
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {
filename = URLEncoder.encode(pFileName, "UTF-8");
filename = filename.replace("+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}
public static void main(String args[]){
try {
List<String> rs = getSheetData("D:\\工号比对结果.csv");
for(int i=0;i<rs.size();i++){
System.err.println(rs.get(i));
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取excel表格数据
* @param filePath 文件路径
*/
public static List<String> getSheetData(String filePath) throws IOException {
return getSheetData(filePath,2);
}
/**
* 读取excel表格数据
* @param filePath 文件路径
* @param msgLine 从第几行开始读取
*/
public static List<String> getSheetData(String filePath,int msgLine) throws IOException {
return getSheetData(filePath, "", msgLine);
}
/**
* 读取excel表格数据
* @param filePath 文件路径
* @param sheetName sheet名称:为空为读取所有
* @param msgLine 从第几行开始读取
*/
public static List<String> getSheetData(String filePath,String sheetName,int msgLine) throws IOException {
if(msgLine==0){
msgLine = 1;
}
List<String> rs = new ArrayList<String>();
workbookRead = new HSSFWorkbook(new FileInputStream(new File(filePath)));
HSSFSheet sheet = null;
String excelStName = "";
int cols = 0;
StringBuffer stringBuffer = new StringBuffer();
for (int i = 0; i < workbookRead.getNumberOfSheets(); i++) { // 获取每个Sheet表
sheet = workbookRead.getSheetAt(i);
excelStName = sheet.getSheetName();
if (!"".equals(sheetName) && !sheetName.equals(excelStName)) {
continue;
}
for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { // 获取每行
HSSFRow row = sheet.getRow(j);
if(j==0){
cols = row.getPhysicalNumberOfCells(); // 以第一行为准获取总列数
}
if(j+1 < msgLine){
continue;
}
stringBuffer = new StringBuffer();
for (int k = 0; k < cols; k++) { // 获取每个单元格
stringBuffer.append(StringUtil.strFormatNull(row.getCell(k))).append("|");
}
stringBuffer.append(excelStName);
rs.add(stringBuffer.toString());
}
}
return rs;
}
}