目录
一，jar包
<!--POI-3.9 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.9</version>
</dependency>
<!--POI-3.9 end-->
<!-- 如果需要 .xlsx 格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId> poi-ooxml-schema</artifactId>
<version>3.9</version>
</dependency>
二，工具类代码
package com.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.CollectionUtils;
/**
* ExcleTools excle工具类
*
* @author jinyihao
*
*/
public class ExcleTools {
private static final Logger logger = Logger.getLogger(ExcleTools.class.getName());
private static String upload_url= "D://下载//";
static {
// try {
// logger.log(Level.INFO,"ExcleTools 初始化开始");
// upload_url = BaseUtils.getProperties("upload_url");
// } catch (Exception e) {
// logger.log(Level.SEVERE,"ExcleTools 初始化异常 upload_url: "+upload_url
// ,e);
// }
}
/**
* 创建文件路径
*
* @param destFileName
* 字符串格式的 文件路径
* @return
*/
public static File createFile(String destFileName) {
File file = new File(destFileName);
if (!file.exists()) {
if (destFileName.endsWith(File.separator)) {
System.out.println("创建单个文件" + destFileName + "失败，目标文件不能为目录！");
return null;
}
// 判断目标文件所在的目录是否存在
if (!file.getParentFile().exists()) {
System.out.println("目标文件所在目录不存在，准备创建它！");
if (!file.getParentFile().mkdirs()) {
System.out.println("创建目标文件所在目录失败！");
return null;
}
}
try {
if (file.createNewFile()) {
System.out.println("创建单个文件" + destFileName + "成功！");
return file;
} else {
System.out.println("创建单个文件" + destFileName + "失败！");
}
} catch (IOException e) {
e.printStackTrace();
System.out.println("创建单个文件" + destFileName + "失败！" + e.getMessage());
}
}
return file;
}
/**
* 获取单元格的值,如果为公式，则获取公式解析后的值（非公式本身）
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null)
return cellValue;
int cType = cell.getCellType();
try {
switch (cType) {
// 这里会不会有问题？会不会返回null?
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue().toString();
} else {
// 此为XLS内存储的真实的值，XLS不是一个所见即所得的工具，如值为0.8125，如果设置单元格
// 为数值型，且小数位为2位，则见到的为0.81，而实际读取到的值仍为0.8125.此处添加对数值
// 型数据的格式化，以满足获取所见的数值所需；
// （实际上仍存在瑕疵，如设置的小数位为5位，但无法获取其数值类型，故无法取到正确的显示值）
cellValue = cell.getNumericCellValue() + "";
}
break;
case Cell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 是公式，获取公式值
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
cellValue = Byte.toString(cell.getErrorCellValue());
break;
default:
cellValue = cell.getStringCellValue();
break;
}
} catch (Exception e) {
return null;
}
if (cellValue != null) // 对单元格值取TRIM
cellValue = cellValue.trim();
return cellValue;
}
/**
* 设置格式 @param: @param workbook @param: @return @return: CellStyle @throws
*/
private static CellStyle getCellStyle(Workbook workbook) {
// 设置格式
CellStyle cellStyle = workbook.createCellStyle();
// 设置边框:
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
return cellStyle;
}
/**
* 导入Excel表格
*
* @param xlsPath：文件的路径：D://a.xls
* 支持xls 和xlsx
* @returnList<Map<String, String>>
* @throws IOException
*/
public static List<Map<String, Object>> importExcel(String xlsPath, String[] keys, int RowNum, int cell) {
List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
FileInputStream fileIn = null;
try {
fileIn = new FileInputStream(xlsPath);
// 根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = null;
if (xlsPath.endsWith(".xls")) {
wb0 = new HSSFWorkbook(fileIn);
} else if (xlsPath.endsWith(".xlsx")) {
wb0 = new XSSFWorkbook(fileIn);
} else {
return new ArrayList<Map<String, Object>>();// 格式不对返回，
}
// 获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0);
// 对Sheet中的每一行进行迭代
for (Row r : sht0) {
// 如果当前行的行号（从0开始）未达到2（第三行）则从新循环
if (r.getRowNum() < RowNum) {
continue;
}
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < keys.length; i++) {
map.put(keys[i], getCellValue(r.getCell(cell + i)));
}
temp.add(map);
}
} catch (Exception e) {
logger.log(Level.SEVERE, "ExcleTools importExcel ".concat(e.getMessage()));
} finally {
if (fileIn != null) {
try {
fileIn.close();
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools importExcel fileIn close ".concat(e.getMessage()));
}
}
}
return temp;
}
/*
* （非 Javadoc）导出Excel表格
*
* @see org.springframework.web.servlet.view.document.AbstractExcelView#
* buildExcelDocument(java.util.Map,
* org.apache.poi.hssf.usermodel.HSSFWorkbook,
* javax.servlet.http.HttpServletRequest,
* javax.servlet.http.HttpServletResponse)
*/
public static void exportExcelDocument(Map<String, Object> model, HttpServletRequest request,
HttpServletResponse response) {
Workbook workbook = getWorkbook(model);
String fileName = (String) model.get("fileName");// 表名
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
// 向哪输出
OutputStream ouputStream = null;
try {
ouputStream = response.getOutputStream();
workbook.write(ouputStream); // 把相应的Excel 工作簿存盘
ouputStream.flush();// 刷新缓冲，将缓冲区中的数据全部取出来
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelDocument ".concat(e.getMessage()));
} finally {
if (ouputStream != null) {
try {
ouputStream.close();
} catch (IOException e) {
logger.log(Level.SEVERE,
"ExcleTools exportExcelDocument ouputStream close ".concat(e.getMessage()));
}
}
}
}
@SuppressWarnings("rawtypes")
private static Workbook getWorkbook(Map<String, Object> model) {
Object[] cells = (Object[]) model.get("cells");// 表头
Object[] keys = (Object[]) model.get("keys");// key值顺序
List lists = (List) model.get("list");// 表数据
String sheetName = String.valueOf(model.get("sheetName"));
String fileName = String.valueOf(model.get("fileName"));// 表名
Workbook workbook = null;
if (CollectionUtils.isEmpty(lists) || StringTools.isBlank(sheetName, fileName) || StringTools.isNull(cells)
|| StringTools.isNull(keys)) {
logger.log(Level.SEVERE, "ExcleTools getWorkbook 生成Workbook失败：参数不能为空 ");
return null;
}
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook();
} else if (fileName.endsWith(".xlsx")) {
// workbook = new XSSFWorkbook();
logger.log(Level.SEVERE, "ExcleTools getWorkbook 缺少poi-ooxml-schema.jar,如果后期需要该格式的excle,需引入jar包 ");
return null;
} else {
logger.log(Level.SEVERE, "ExcleTools getWorkbook 文件格式异常,只支持excle文件格式 ");
return null;
}
fileName = fileName.substring(0, fileName.indexOf(".")).concat(DateUtil.getDate("yyyyMMddHHmmss"))
.concat(fileName.substring(fileName.indexOf(".")));
model.put("fileName", fileName);// 表名
Sheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultRowHeight((short) (2 * 256)); // 设置默认行高，表示2个字符的高度
sheet.setDefaultColumnWidth(12); // 设置默认列宽
CellStyle cellStyle = getCellStyle(workbook);
// 首先构造excel表头
Row row = sheet.createRow(0);
for (int i = 0; i < cells.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(String.valueOf(cells[i]));
cell.setCellStyle(cellStyle);
}
// 然后构造excel列表数据
int nums = 1;
for (int i = 0; i < lists.size(); i++) {
Map map = (Map) lists.get(i);// 获取数据
row = sheet.createRow(nums);// 添加一行
nums++;
for (int k = 0; k < keys.length; k++) {
Cell cell = row.createCell(k);
cell.setCellValue(String.valueOf(map.get(String.valueOf(keys[k]))));
cell.setCellStyle(cellStyle);
}
}
return workbook;
}
public static void exportExcelLoaclhost(Map<String, Object> model) {
Workbook workbook = getWorkbook(model);
if (workbook == null) {
return;
}
String fileName = (String) model.get("fileName");// 表名
OutputStream os = null;
try {
File file = createFile(upload_url.concat(fileName));
os = new FileOutputStream(file);
workbook.write(os);
} catch (Exception e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelLoaclhost ".concat(e.getMessage()));
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelLoaclhost os close ".concat(e.getMessage()));
}
}
}
}
public static void exportExcelLoaclhost(Map<String, Object> model,String upload_url) {
Workbook workbook = getWorkbook(model);
if (workbook == null) {
return;
}
String fileName = (String) model.get("fileName");// 表名
OutputStream os = null;
try {
File file = createFile(upload_url.concat(fileName));
os = new FileOutputStream(file);
workbook.write(os);
} catch (Exception e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelLoaclhost ".concat(e.getMessage()));
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelLoaclhost os close ".concat(e.getMessage()));
}
}
}
}
public static void main(String[] args) {
// List<Map<String, Object>> areaInfoList = importExcel("d:/模板.xlsx",
// new String[] { "no", "qwe" }, 1, 1);
// for (Map<String, Object> map : areaInfoList) {
// System.out.println(map.get("no") + " " + map.get("qwe"));
// }
// List<Map<String, Object>> list = new ArrayList<Map<String,
// Object>>();
// Map<String, Object> map = new HashMap<String, Object>();
// map.put("1", "1");
// map.put("2", "区域：圆");
// list.add(map);
// map = new HashMap<String, Object>();
// map.put("1", "2");
// map.put("2", "区域：矩形");
// list.add(map);
// map = new HashMap<String, Object>();
// map.put("1", "3");
// map.put("2", "区域：多边形");
// list.add(map);
// Map<String, Object> model = new HashMap<String, Object>();
// model.put("cells", new String[] { "序号", "敏感词"});
// model.put("sheetName", "sheet1");
// model.put("fileName", "模板.xls");
// model.put("list", list);
// model.put("keys", new String[] { "1", "2"});
// exportExcelLoaclhost(model);
String aString = "ssss";
System.out.println(aString.concat("aaa"));
}
}
三，测试
感觉直贴一个工具类，对新人不友好，把测试图发一下。代码在工具类里，被注释的那个
（一）导出
（二）导入
用我们刚导出的，试试导入解析
（三）导出到浏览器
controll
@ResponseBody
@RequestMapping(value = "/exportExcel")
public void exportExcel( HttpServletRequest request, HttpServletResponse response){
List<Map<String, Object>> list = new ArrayList<Map<String,
Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("1", "1");
map.put("2", "区域：圆");
list.add(map);
map = new HashMap<String, Object>();
map.put("1", "2");
map.put("2", "区域：矩形");
list.add(map);
map = new HashMap<String, Object>();
map.put("1", "3");
map.put("2", "区域：多边形");
list.add(map);
Map<String, Object> model = new HashMap<String, Object>();
model.put("cells", new String[] { "序号", "敏感词"});
model.put("sheetName", "sheet1");
model.put("fileName", "模板.xls");
model.put("list", list);
model.put("keys", new String[] { "1", "2"});
ExcleTools.exportExcelDocument( model, request, response);
}
html
简单a标签调用
<a href="http://127.0.0.1:8066/test/exportExcel">exportExcel</a>
（四）多sheet页
针对不确定多少sheet页的
package com.sgfd.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.CollectionUtils;
/**
* ExcleTools excle工具类
*
* @author jinyihao
*
*/
public class ExcleTools {
private static final Logger logger = Logger.getLogger(ExcleTools.class.getName());
private static String upload_url= "D://";
/**
* 创建文件路径
*
* @param destFileName
* 字符串格式的 文件路径
* @return
*/
public static File createFile(String destFileName) {
File file = new File(destFileName);
if (!file.exists()) {
if (destFileName.endsWith(File.separator)) {
System.out.println("创建单个文件" + destFileName + "失败，目标文件不能为目录！");
return null;
}
// 判断目标文件所在的目录是否存在
if (!file.getParentFile().exists()) {
System.out.println("目标文件所在目录不存在，准备创建它！");
if (!file.getParentFile().mkdirs()) {
System.out.println("创建目标文件所在目录失败！");
return null;
}
}
try {
if (file.createNewFile()) {
System.out.println("创建单个文件" + destFileName + "成功！");
return file;
} else {
System.out.println("创建单个文件" + destFileName + "失败！");
}
} catch (IOException e) {
e.printStackTrace();
System.out.println("创建单个文件" + destFileName + "失败！" + e.getMessage());
}
}
return file;
}
/**
* 获取单元格的值,如果为公式，则获取公式解析后的值（非公式本身）
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null)
return cellValue;
int cType = cell.getCellType();
try {
switch (cType) {
// 这里会不会有问题？会不会返回null?
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue().toString();
} else {
// 此为XLS内存储的真实的值，XLS不是一个所见即所得的工具，如值为0.8125，如果设置单元格
// 为数值型，且小数位为2位，则见到的为0.81，而实际读取到的值仍为0.8125.此处添加对数值
// 型数据的格式化，以满足获取所见的数值所需；
// （实际上仍存在瑕疵，如设置的小数位为5位，但无法获取其数值类型，故无法取到正确的显示值）
cellValue = cell.getNumericCellValue() + "";
}
break;
case Cell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 是公式，获取公式值
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
cellValue = Byte.toString(cell.getErrorCellValue());
break;
default:
cellValue = cell.getStringCellValue();
break;
}
} catch (Exception e) {
return null;
}
if (cellValue != null) // 对单元格值取TRIM
cellValue = cellValue.trim();
return cellValue;
}
/**
* 设置格式 @param: @param workbook @param: @return @return: CellStyle @throws
*/
private static CellStyle getCellStyle(Workbook workbook) {
// 设置格式
CellStyle cellStyle = workbook.createCellStyle();
// 设置边框:
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
return cellStyle;
}
/**
* 导入Excel表格
*
* @param xlsPath：文件的路径：D://a.xls
* 支持xls 和xlsx
* @returnList<Map<String, String>>
* @throws IOException
*/
public static List<Map<String, Object>> importExcel(String xlsPath, String[] keys, int RowNum, int cell) {
List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
FileInputStream fileIn = null;
try {
fileIn = new FileInputStream(xlsPath);
// 根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = null;
if (xlsPath.endsWith(".xls")) {
wb0 = new HSSFWorkbook(fileIn);
} else if (xlsPath.endsWith(".xlsx")) {
wb0 = new XSSFWorkbook(fileIn);
} else {
return new ArrayList<Map<String, Object>>();// 格式不对返回，
}
// 获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0);
// 对Sheet中的每一行进行迭代
for (Row r : sht0) {
// 如果当前行的行号（从0开始）未达到2（第三行）则从新循环
if (r.getRowNum() < RowNum) {
continue;
}
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < keys.length; i++) {
map.put(keys[i], getCellValue(r.getCell(cell + i)));
}
temp.add(map);
}
} catch (Exception e) {
logger.log(Level.SEVERE, "ExcleTools importExcel ".concat(e.getMessage()));
} finally {
if (fileIn != null) {
try {
fileIn.close();
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools importExcel fileIn close ".concat(e.getMessage()));
}
}
}
return temp;
}
/**
* 导出到浏览器
* @param model
* @param request
* @param response
*/
public static void exportExcelDocument(Map<String, Object> model, HttpServletRequest request,
HttpServletResponse response) {
Workbook workbook = getWorkbook(model);
String fileName = (String) model.get("fileName");// 表名
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
// 向哪输出
OutputStream ouputStream = null;
try {
ouputStream = response.getOutputStream();
workbook.write(ouputStream); // 把相应的Excel 工作簿存盘
ouputStream.flush();// 刷新缓冲，将缓冲区中的数据全部取出来
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelDocument ".concat(e.getMessage()));
} finally {
if (ouputStream != null) {
try {
ouputStream.close();
} catch (IOException e) {
logger.log(Level.SEVERE,
"ExcleTools exportExcelDocument ouputStream close ".concat(e.getMessage()));
}
}
}
}
@SuppressWarnings("rawtypes")
private static Workbook getWorkbook(Map<String, Object> model) {
Object[] cells = (Object[]) model.get("cells");// 表头
Object[] keys = (Object[]) model.get("keys");// key值顺序
List lists = (List) model.get("list");// 表数据
String[] sheetNames = (String[]) model.get("sheetNames");
String fileName = String.valueOf(model.get("fileName"));// 表名
Workbook workbook = null;
if (CollectionUtils.isEmpty(lists) || sheetNames==null || StringTools.isBlank(sheetNames[0], fileName) || StringTools.isNull(cells) || StringTools.isNull(keys)) {
logger.log(Level.SEVERE, "ExcleTools getWorkbook 生成Workbook失败：参数不能为空 ");
return null;
}
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook();
} else if (fileName.endsWith(".xlsx")) {
// workbook = new XSSFWorkbook();
logger.log(Level.SEVERE, "ExcleTools getWorkbook 缺少poi-ooxml-schema.jar,如果后期需要该格式的excle,需引入jar包 ");
return null;
} else {
logger.log(Level.SEVERE, "ExcleTools getWorkbook 文件格式异常,只支持excle文件格式 ");
return null;
}
fileName = fileName.substring(0, fileName.indexOf(".")).concat(DateUtil.getDate("yyyyMMddHHmmss"))
.concat(fileName.substring(fileName.indexOf(".")));
model.put("fileName", fileName);// 表名
createSheet( workbook , sheetNames[0], lists , keys , cells);
return workbook;
}
/**
* 多Sheet页导出
* @param model
* @return
*/
@SuppressWarnings("rawtypes")
private static Workbook getWorkbooks(Map<String, Object> model) {
Object[] cells = (Object[]) model.get("cells");// 表头
Object[] keys = (Object[]) model.get("keys");// key值顺序
List<List> list = (List<List>) model.get("list");// 表数据
String[] sheetNames = (String[]) model.get("sheetNames");
String fileName = String.valueOf(model.get("fileName"));// 表名
Workbook workbook = null;
if (CollectionUtils.isEmpty(list) || sheetNames==null || StringTools.isBlank(fileName) || StringTools.isNull(cells)|| StringTools.isNull(keys)) {
logger.log(Level.SEVERE, "ExcleTools getWorkbooks 生成Workbook失败：参数不能为空 ");
return null;
}
if(list.size()==0||sheetNames.length==0||list.size()!=sheetNames.length){
logger.log(Level.SEVERE, "ExcleTools getWorkbooks 生成Workbook失败：多Sheet导出，Sheet页名称长度与导出数据list长度不符 ");
return null;
}
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook();
} else if (fileName.endsWith(".xlsx")) {
// workbook = new XSSFWorkbook();
logger.log(Level.SEVERE, "ExcleTools getWorkbooks 缺少poi-ooxml-schema.jar,如果后期需要该格式的excle,需引入jar包 ");
return null;
} else {
logger.log(Level.SEVERE, "ExcleTools getWorkbooks 文件格式异常,只支持excle文件格式 ");
return null;
}
fileName = fileName.substring(0, fileName.indexOf(".")).concat(DateUtil.getDate("yyyyMMddHHmmss"))
.concat(fileName.substring(fileName.indexOf(".")));
model.put("fileName", fileName);// 表名
for (int i = 0; i < list.size(); i++) {
createSheet( workbook , sheetNames[i], list.get(i) , keys , cells);
}
return workbook;
}
/**
* 创建Sheet页
* @param workbook
* @param sheetName
* @param lists
* @param keys
* @param cells
* @return
*/
public static Workbook createSheet(Workbook workbook ,String sheetName,List lists ,Object[] keys ,Object[] cells){
Sheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultRowHeight((short) (2 * 256)); // 设置默认行高，表示2个字符的高度
sheet.setDefaultColumnWidth(12); // 设置默认列宽
CellStyle cellStyle = getCellStyle(workbook);
// 首先构造excel表头
Row row = sheet.createRow(0);
for (int i = 0; i < cells.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(String.valueOf(cells[i]));
cell.setCellStyle(cellStyle);
}
// 然后构造excel列表数据
int nums = 1;
for (int i = 0; i < lists.size(); i++) {
Map map = (Map) lists.get(i);// 获取数据
row = sheet.createRow(nums);// 添加一行
nums++;
for (int k = 0; k < keys.length; k++) {
Cell cell = row.createCell(k);
cell.setCellValue(String.valueOf(map.get(String.valueOf(keys[k]))));
cell.setCellStyle(cellStyle);
}
}
return workbook;
}
/**
* 指定路径导出 （单shell页）
* @param model
* @param upload_url
*/
public static void exportExcelLoaclhost(Map<String, Object> model,String upload_url) {
exportExcelLoaclhost(model, upload_url,false) ;
}
/**
* 指定路径导出
* @param model
* @param upload_url
* @param isLists 是否多shell页导出数据
*/
public static void exportExcelLoaclhost(Map<String, Object> model,String upload_url,boolean isLists) {
Workbook workbook = null;
if(isLists){
workbook = getWorkbooks(model);
}else{
workbook = getWorkbook(model);
}
writeFile(model, workbook,upload_url);
}
/**
* 导出 （单shell页）
* @param model
* @param upload_url
*/
public static void exportExcelLoaclhost(Map<String, Object> model) {
exportExcelLoaclhost(model,upload_url,false);
}
/**
* 导出
* @param model
* @param isLists 是否多shell页导出数据
*/
public static void exportExcelLoaclhost(Map<String, Object> model,boolean isLists) {
exportExcelLoaclhost(model,upload_url,isLists);
}
/**
* 创建文件并写入内容
* @param model
* @param workbook
*/
private static void writeFile(Map<String, Object> model,Workbook workbook,String upload_url){
if (workbook == null) {
return;
}
String fileName = (String) model.get("fileName");// 表名
OutputStream os = null;
try {
File file = createFile(upload_url.concat(fileName));
os = new FileOutputStream(file);
workbook.write(os);
} catch (Exception e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelLoaclhost ".concat(e.getMessage()));
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.log(Level.SEVERE, "ExcleTools exportExcelLoaclhost os close ".concat(e.getMessage()));
}
}
}
}
public static void main(String[] args) {
//导入
// List<Map<String, Object>> areaInfoList = importExcel("d:/模板20201022153228.xls",
// new String[] { "no", "qwe" }, 1, 1);
// for (Map<String, Object> map : areaInfoList) {
// System.out.println(map.get("no") + " " + map.get("qwe"));
// }
//导出
List<Map<String, Object>> list = new ArrayList<Map<String,
Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("1", "1");
map.put("2", "区域：圆");
list.add(map);
map = new HashMap<String, Object>();
map.put("1", "2");
map.put("2", "区域：矩形");
list.add(map);
map = new HashMap<String, Object>();
map.put("1", "3");
map.put("2", "区域：多边形");
list.add(map);
Map<String, Object> model = new HashMap<String, Object>();
model.put("cells", new String[] { "序号", "敏感词"});
model.put("sheetNames",new String[] { "sheet1"});
model.put("fileName", "模板.xls");
model.put("list", list);
model.put("keys", new String[] { "1", "2"});
exportExcelLoaclhost(model, "d:/");
List<List> lists = new ArrayList<>();
lists.add(list);
lists.add(list);
model.put("sheetNames",new String[] { "sheet1", "sheet2"} );
model.put("list", lists);
exportExcelLoaclhost(model, "d:/",true);
}
}
1822
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
