import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet; import java.util.List;
import java.util.Map; /**
* <p>excel 简单表格 赋值接口<p>
* @version 1.0
* @author li_hao
* @date 2017年1月18日
*/
public interface ColVal { void setColVal(List<Map<String, Object>> list, String[] keys, Sheet sheet, CellStyle colValCellStyle);
}
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.List;
import java.util.Map;
import java.util.Objects; /**
* <p>excel 简单表格 赋值 实现<p>
* @version 1.0
* @author li_hao
* @date 2017年1月18日
*/
public class CommonColVal implements ColVal { /**
* @param list 数据map
* @param keys 列值 key
* @param sheet {@link Sheet}
* @param colValCellStyle {@link CellStyle}
*/
@Override
public void setColVal(List<Map<String, Object>> list, String[] keys, Sheet sheet, CellStyle colValCellStyle) {
int rowNum = 1; // 行号
int i = 0;
for (; i < list.size(); i++, rowNum++) {
// 创建行
Row row = sheet.createRow(rowNum);
// 获取当前行 数据map
Map<String, Object> rowValMap = list.get(i); // 遍历 key值 给每个列赋值
for (int j = 0; j < keys.length; j++) {
Object cellVal = rowValMap.get(keys[j]); Cell cell = row.createCell(j);
cell.setCellValue(Objects.toString(cellVal, ""));
cell.setCellStyle(colValCellStyle);
}
}
}
}
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map; /**
* <p>导出Excel文档工具类<p>
* @version 1.0
* @author li_hao
* @date 2017年1月18日
*/
public class PoiUtils { private static CellStyle buildColValCellStyle(Workbook wb) {
CellStyle filedValCellStyle = wb.createCellStyle();
Font f2 = wb.createFont();
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第二种单元格的样式（用于值）
filedValCellStyle.setFont(f2);
filedValCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
filedValCellStyle.setBorderRight(CellStyle.BORDER_THIN);
filedValCellStyle.setBorderTop(CellStyle.BORDER_THIN);
filedValCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
filedValCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
filedValCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return filedValCellStyle;
} private static CellStyle buildColNameCellStyle(Workbook wb) {
// 创建两种单元格格式
CellStyle filedNameCellStyle = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置第一种单元格的样式（用于列名）
filedNameCellStyle.setFont(f);
filedNameCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
filedNameCellStyle.setBorderRight(CellStyle.BORDER_THIN);
filedNameCellStyle.setBorderTop(CellStyle.BORDER_THIN);
filedNameCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
filedNameCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
return filedNameCellStyle;
} /**
* 创建excel文档，
*
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
*/
public static Workbook createSheet(Workbook wb, String sheetName, String titleName, List<Map<String, Object>> list, String[] keys, String columnNames[], ColVal colVal) { // 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet(sheetName); setColLength(keys, sheet);
CellStyle colNameCellStyle = buildColNameCellStyle(wb);
CellStyle colValCellStyle = buildColValCellStyle(wb); setTitle(titleName, sheet, colNameCellStyle, new CellRangeAddress(0, 0, 0, keys.length - 1));
setColName(columnNames, sheet, colNameCellStyle, 1);
colVal.setColVal(list, keys, sheet, colValCellStyle); return wb;
} /**
* 创建excel文档，
*
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
*/
public static Workbook createSheetNoTitle(Workbook wb, String sheetName, List<Map<String, Object>> list, String[] keys, String columnNames[], ColVal colVal) { Sheet sheet = wb.createSheet(sheetName);
setColLength(keys, sheet);
CellStyle colNameCellStyle = buildColNameCellStyle(wb);
CellStyle colValCellStyle = buildColValCellStyle(wb);
setColName(columnNames, sheet, colNameCellStyle, 0);
colVal.setColVal(list, keys, sheet, colValCellStyle); return wb;
} private static void setTitle(String titleName, Sheet sheet1, CellStyle colNameCellStyle, CellRangeAddress cellRangeAddress) {
//表头
Row titleRow = sheet1.createRow(0);
Cell titleRowCell = titleRow.createCell(0);
titleRowCell.setCellValue(titleName);
titleRowCell.setCellStyle(colNameCellStyle);
sheet1.addMergedRegion(cellRangeAddress);
} private static void setColLength(String[] keys, Sheet sheet1) {
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < keys.length; i++) {
sheet1.setColumnWidth((short) i, (short) (35.7 * 150));
}
} private static void setColName(String[] columnNames, Sheet sheet1, CellStyle cs, int rowNum) {
// 列名行
Row row = sheet1.createRow(rowNum); //设置列名
for (int i = 0; i < columnNames.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
} public static void setResponse(HttpServletResponse response, String fileName, ByteArrayOutputStream os) throws IOException {
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} }
代码示例：
