时间：2021-07-01 10:21:17 帮助过：4人阅读
package com.shiliu.game.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
/**
* SpringMVC 读取Excle工具类
* @author otowa
* @Date 2016-12-04 14:57
*
* @param <T>
*/
public class LeadingInExcel<T> {
//log4j输出
private Logger logger = Logger.getLogger(this.getClass());
// 时间的格式
private String format="yyyy-MM-dd";
/**
* 无参构造
*/
public LeadingInExcel() {
super();
}
/**
* 构造设置显示时间的格式
* @param format 例："yyyy-MM-dd"
*/
public LeadingInExcel(String format) {
super();
this.format = format;
}
/**
* 设置显示时间的格式
* @param format 例："yyyy-MM-dd"
*/
public void setFormat(String format) {
this.format = format;
}
/**
* 上传Excle文件、并读取其中数据、返回list数据集合
* @param multipart
* @param propertiesFileName properties文件名称
* @param kyeName properties文件中上传存储文件的路径
* @param sheetIndex 读取Excle中的第几页中的数据
* @param titleAndAttribute 标题名与实体类属性名对应的Map集合
* @param clazz 实体类.class
* @return 返回读取出的List集合
* @throws Exception
*/
public List<T> uploadAndRead(MultipartFile multipart,String propertiesFileName, String kyeName,int sheetIndex,
Map<String, String> titleAndAttribute,Class<T> clazz) throws Exception{
String originalFilename=null;
int i = 0;
boolean isExcel2003 = false;
//取出文件名称
originalFilename = multipart.getOriginalFilename();
//判断Excel是什么版本
i = isExcleVersion(originalFilename);
if(i==0)return null; else if(i==1)isExcel2003=true;
String filePath = readPropertiesFilePathMethod( propertiesFileName, kyeName);
File filePathname = this.upload(multipart, filePath, isExcel2003);
List<T> judgementVersion = judgementVersion(filePathname, sheetIndex, titleAndAttribute, clazz, isExcel2003);
return judgementVersion;
}
/**
* @描述：判断Excel是什么版本
* @param originalFilename
* @return
* 1 ：2003
* 2 ：2007
* 0 ：不是Excle版本
*/
public int isExcleVersion(String originalFilename){
int i = 0;
if(originalFilename.matches("^.+\\.(?i)(xls)$"))i = 1;
else
if(originalFilename.matches("^.+\\.(?i)(xlsx)$"))i = 2;
return i;
}
/**
* 读取properties文件中对应键的值
* @param propertiesFileName
* @param kyeName
* @return value值
*/
public String readPropertiesFilePathMethod(String propertiesFileName, String kyeName){
//读取properties文件
InputStream inputStream=null;
Properties properties=null;
String filePath=null;//读取出的文件路径
try {
inputStream= new FileInputStream(this.getClass().getClassLoader().getResource("/"+propertiesFileName+".properties").getPath());
properties=new Properties();
properties.load(inputStream);
filePath = properties.getProperty(kyeName);
} catch (FileNotFoundException e1) {
logger.error("未找到properties文件！", e1);
} catch (IOException e1) {
logger.error("打开文件流异常！", e1);
} finally{
//关闭流
if(inputStream!=null){
try {
inputStream.close();
} catch (IOException e) {
logger.error("关闭文件流异常！", e);
}
}
}
return filePath;
}
/**
* SpringMVC 上传Excle文件至本地
* @param multipart
* @param filePath 上传至本地的文件路径 例：D:\\fileupload
* @param isExcel2003 是否是2003版本的Excle文件
* @return 返回上传文件的全路径
* @throws Exception
*/
public File upload(MultipartFile multipart,String filePath,boolean isExcel2003) throws Exception{
//文件后缀
String extension=".xlsx";
if(isExcel2003)extension=".xls";
//指定上传文件的存储路径
File file=new File(filePath);
//接口强转实现类
CommonsMultipartFile commons=(CommonsMultipartFile) multipart;
//判断所属路径是否存在、不存在新建
if(file.exists())file.mkdirs();
/*
* 新建一个文件
* LongIdWorker longID工具类
*/
File filePathname=new File(file+File.separator+LongIdWorker.getDataId()+extension);
//将上传的Excel写入新建的文件中
try {
commons.getFileItem().write(filePathname);
} catch (Exception e) {
logger.error("写入文件异常", e);
}
return filePathname;
}
/**
* 读取本地Excel文件返回List集合
* @param filePathname
* @param sheetIndex
* @param titleAndAttribute
* @param clazz
* @param isExcel2003
* @return
* @throws Exception
*/
public List<T> judgementVersion(File filePathname,int sheetIndex,Map<String, String> titleAndAttribute,Class<T> clazz,boolean isExcel2003) throws Exception{
FileInputStream is=null;
POIFSFileSystem fs=null;
Workbook workbook=null;
try {
//打开流
is=new FileInputStream(filePathname);
if(isExcel2003){
//把excel文件作为数据流来进行传入传出
fs=new POIFSFileSystem(is);
//解析Excel 2003版
workbook = new HSSFWorkbook(fs);
}else{
//解析Excel 2007版
workbook=new XSSFWorkbook(is);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return readExcelTitle(workbook,sheetIndex,titleAndAttribute,clazz);
}
/**
* 判断接收的Map集合中的标题是否于Excle中标题对应
* @param workbook
* @param sheetIndex
* @param titleAndAttribute
* @param clazz
* @return
* @throws Exception
*/
private List<T> readExcelTitle(Workbook workbook,int sheetIndex,Map<String, String> titleAndAttribute,Class<T> clazz) throws Exception{
//得到第一个shell
Sheet sheet = workbook.getSheetAt(sheetIndex);
// 获取标题
Row titelRow = sheet.getRow(0);
Map<Integer, String> attribute = new HashMap<Integer, String>();
if (titleAndAttribute != null) {
for (int columnIndex = 0; columnIndex < titelRow.getLastCellNum(); columnIndex++) {
Cell cell = titelRow.getCell(columnIndex);
if (cell != null) {
String key = cell.getStringCellValue();
String value = titleAndAttribute.get(key);
if (value == null) {
value = key;
}
attribute.put(Integer.valueOf(columnIndex), value);
}
}
} else {
for (int columnIndex = 0; columnIndex < titelRow.getLastCellNum(); columnIndex++) {
Cell cell = titelRow.getCell(columnIndex);
if (cell != null) {
String key = cell.getStringCellValue();
attribute.put(Integer.valueOf(columnIndex), key);
}
}
}
return readExcelValue(workbook,sheet,attribute,clazz);
}
/**
* 获取Excle中的值
* @param workbook
* @param sheet
* @param attribute
* @param clazz
* @return
* @throws Exception
*/
private List<T> readExcelValue(Workbook workbook,Sheet sheet,Map<Integer, String> attribute,Class<T> clazz) throws Exception{
List<T> info=new ArrayList<T>();
//获取标题行列数
int titleCellNum = sheet.getRow(0).getLastCellNum();
// 获取值
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
// logger.debug("第--" + rowIndex);
// 1.若当前行的列数不等于标题行列数就放弃整行数据(若想放弃此功能注释4个步骤即可)
int lastCellNum = row.getLastCellNum();
if(titleCellNum != lastCellNum){
continue;
}
// 2.标记
boolean judge = true;
T obj = clazz.newInstance();
for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {//这里小于等于变成小于
Cell cell = row.getCell(columnIndex);
//处理单元格中值得类型
String value = getCellValue(cell);
// 3.单元格中的值等于null或等于"" 就放弃整行数据
if(value == null || "".equals(value)){
judge = false;
break;
}
/*
* 测试：查看自定义的title Map集合中定义的Excle标题和实体类中属性对应情况！
System.out.println("c:"+columnIndex+"\t"+attribute.get(Integer.valueOf(columnIndex)));
*/
Field field = clazz.getDeclaredField(attribute.get(Integer
.valueOf(columnIndex)));
Class<?> fieldType = field.getType();
Object agge = null;
if (fieldType.isAssignableFrom(Integer.class)) {
agge = Integer.valueOf(value);
} else if (fieldType.isAssignableFrom(Double.class)) {
agge = Double.valueOf(value);
} else if (fieldType.isAssignableFrom(Float.class)) {
agge = Float.valueOf(value);
} else if (fieldType.isAssignableFrom(Long.class)) {
agge = Long.valueOf(value);
} else if (fieldType.isAssignableFrom(Date.class)) {
agge = new SimpleDateFormat(format).parse(value);
} else if (fieldType.isAssignableFrom(Boolean.class)) {
agge = "Y".equals(value) || "1".equals(value);
} else if (fieldType.isAssignableFrom(String.class)) {
agge = value;
}
// 个人感觉char跟byte就不用判断了 用这两个类型的很少如果是从数据库用IDE生成的话就不会出现了
Method method = clazz.getMethod("set"
+ toUpperFirstCase(attribute.get(Integer
.valueOf(columnIndex))), fieldType);
method.invoke(obj, agge);
}
// 4. if
if(judge)info.add(obj);
}
return info;
}
/**
* @ 首字母大写
*/
private String toUpperFirstCase(String str) {
return str.replaceFirst(str.substring(0, 1), str.substring(0, 1)
.toUpperCase());
}
/**
* 功能:处理单元格中值得类型
* @param cell
* @return
*/
private String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
//判断是是日期型，转换日期格式，否则转换数字格式。
if(DateUtil.isCellDateFormatted(cell)){
Date dateCellValue = cell.getDateCellValue();
if(dateCellValue != null){
result = new SimpleDateFormat(this.format).format(dateCellValue);
}else{
result="";
}
}else{
result = new DecimalFormat("0").format(cell.getNumericCellValue());
};
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
/*
* 导入时如果为公式生成的数据则无值
*
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
*/
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
break;
}
}
return result.toString();
}
}
使用例子（项目代码只能模仿）：
/**
* 读取Excel中的用户信息插入数据库
* @param multipart
* @param session
* @return
*/
@RequestMapping(value="/batchimport")
@ResponseBody
public String batchImportMethod(
@RequestParam(value="gameId") String gameId,
@RequestParam(value="filename") MultipartFile multipart
){
//局部变量
LeadingInExcel<UserWhiteList> testExcel=null;
List<UserWhiteList> uploadAndRead=null;
boolean judgement = false;
String Msg =null;
String error = "";
//定义需要读取的数据
String formart = "yyyy-MM-dd";
String propertiesFileName = "config";
String kyeName = "file_path";
int sheetIndex = 0;
Map<String, String> titleAndAttribute=null;
Class<UserWhiteList> clazz=UserWhiteList.class;
//定义对应的标题名与对应属性名
titleAndAttribute=new HashMap<String, String>();
titleAndAttribute.put("手机号码", "phone");
titleAndAttribute.put("总抽奖次数", "playtimes");
//调用解析工具包
testExcel=new LeadingInExcel<UserWhiteList>(formart);
//解析excel，获取客户信息集合
try {
uploadAndRead = testExcel.uploadAndRead(multipart, propertiesFileName, kyeName, sheetIndex, titleAndAttribute, clazz);
} catch (Exception e) {
log.error("读取Excel文件错误！",e);
}
if(uploadAndRead != null && !"[]".equals(uploadAndRead.toString()) && uploadAndRead.size()>=1){
judgement = true;
}
if(judgement){
//把客户信息分为没100条数据为一组迭代添加客户信息（注：将customerList集合作为参数，在Mybatis的相应映射文件中使用foreach标签进行批量添加。）
//int count=0;
int listSize=uploadAndRead.size();
int toIndex=100;
for (int i = 0; i < listSize; i+=100) {
if(i+100>listSize){
toIndex=listSize-i;
}
List<UserWhiteList> subList = uploadAndRead.subList(i, i+toIndex);
/*
* 测试数据：
count=count+subList.size();
System.out.println("subList长度："+subList.size()+"\t总长度："+count);
*
for (UserJHDX userJHDX : subList) {
System.out.println("手机号："+userJHDX.getPhone()+"截止日期:"+userJHDX.getUptodate()+"流量值"+userJHDX.getFlux()+"总次数"+userJHDX.getTotal());
}
*/
/** 此处执行集合添加 */
userWhiteListService.batchInport(subList, gameId);
}
Msg ="批量导入EXCEL成功！";
}else{
Msg ="批量导入EXCEL失败！";
}
String res = "{ error:‘" + error + "‘, msg:‘" + Msg + "‘}";
return res;
}
package com.shiliu.game.utils;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
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.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* 导出Excel公共方法
*
* @author wkr
*
*/
public class LeadingOutExcel {
//导出文件的名字
private String fileName;
//显示的导出表的标题
private String title;
//导出表的列名
private String[] rowName;
private List<Object[]> dataList = new ArrayList<Object[]>();
private HttpServletResponse response = null;
private HSSFWorkbook workbook = null;
private OutputStream out = null;
//构造方法，传入要导出的数据
public LeadingOutExcel(String fileName,String title,String[] rowName,List<Object[]> dataList,HttpServletResponse response){
this.fileName = fileName;
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
this.response = response;
}
public void export () throws Exception{
HSSFWorkbook createExcel = this.createExcel();
this.writeInOutputStream(createExcel);
}
/*
* 导出数据
* */
public HSSFWorkbook createExcel() throws Exception{
try{
workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
/*
* 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);//设置开头两行
* 使用条件：
* HSSFRow rowRowName = sheet.createRow(0);设置索引2的位置创建行
* HSSFRow row = sheet.createRow(i+1);//创建所需的行数 改为i+3
*
*/
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象
/*
* 产生表格标题行
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1))); //设置开头两行合并
cellTiltle.setCellStyle(columnTopStyle); //设置列头单元格样式
cellTiltle.setCellValue("");//设置空单元格的内容。比如传入List的值是空的。
*/
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+1);//创建所需的行数
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(obj[j].toString()); //设置单元格的值
}
}
cell.setCellStyle(style); //设置单元格样式
}
}
//让列宽随着导出的列长自动适应
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.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//int length = currentCell.getStringCellValue().getBytes().length;
String stringCellValue = null;
try {
stringCellValue = currentCell.getStringCellValue();
} catch (Exception e) {
continue;
}
int length = stringCellValue==null?50:stringCellValue.getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
}catch(Exception e){
e.printStackTrace();
}
return workbook;
}
public void writeInOutputStream(HSSFWorkbook workbook) throws Exception{
//设置响应类型、与头信息
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
//清除资源
out.close();
}
/*
* 列头单元格样式
*/
public 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 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(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
package com.shiliu.game.utils;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
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.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* 导出Excel公共方法
*
* @author wkr
*
*/
public class LeadingOutExcel {
//导出文件的名字
private String fileName;
//显示的导出表的标题
private String title;
//导出表的列名
private String[] rowName;
private List<Object[]> dataList = new ArrayList<Object[]>();
private HttpServletResponse response = null;
private HSSFWorkbook workbook = null;
private OutputStream out = null;
//构造方法，传入要导出的数据
public LeadingOutExcel(String fileName,String title,String[] rowName,List<Object[]> dataList,HttpServletResponse response){
this.fileName = fileName;
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
this.response = response;
}
public void export () throws Exception{
HSSFWorkbook createExcel = this.createExcel();
this.writeInOutputStream(createExcel);
}
/*
* 导出数据
* */
public HSSFWorkbook createExcel() throws Exception{
try{
workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
/*
* 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);//设置开头两行
* 使用条件：
* HSSFRow rowRowName = sheet.createRow(0);设置索引2的位置创建行
* HSSFRow row = sheet.createRow(i+1);//创建所需的行数 改为i+3
*
*/
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象
/*
* 产生表格标题行
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1))); //设置开头两行合并
cellTiltle.setCellStyle(columnTopStyle); //设置列头单元格样式
cellTiltle.setCellValue("");//设置空单元格的内容。比如传入List的值是空的。
*/
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+1);//创建所需的行数
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(obj[j].toString()); //设置单元格的值
}
}
cell.setCellStyle(style); //设置单元格样式
}
}
//让列宽随着导出的列长自动适应
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.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//int length = currentCell.getStringCellValue().getBytes().length;
String stringCellValue = null;
try {
stringCellValue = currentCell.getStringCellValue();
} catch (Exception e) {
continue;
}
int length = stringCellValue==null?50:stringCellValue.getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
}catch(Exception e){
e.printStackTrace();
}
return workbook;
}
public void writeInOutputStream(HSSFWorkbook workbook) throws Exception{
//设置响应类型、与头信息
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
//清除资源
out.close();
}
/*
* 列头单元格样式
*/
public 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 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(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
使用例子（项目代码只能模仿）：
/**
* 条件导出
* @param gameId
* @param startDate
* @param endDate
* @param level
* @param response
*/
@RequestMapping(value="/conditionalExportDate")
public void conditionalExportDateMethod(
@RequestParam(value="gameId") String gameId,
@RequestParam(value="startDate") String startDate,
@RequestParam(value="endDate") String endDate,
@RequestParam(value="level") String level,
HttpServletResponse response
){
Map<String, Object> conditionMap = null;
List<PlayRecord> dataSet = null;
LeadingOutExcel leadingOutExcel = null; //工具类
//配置信息
String fileName = "client";
String format = "yyyy-MM-dd hh:mm:ss";
String title = "用户信息";
String[] rowName = { "编号","微信号","微信名称", "手机号", "参与活动时间","中奖等级", "获得奖品" };
//查询条件
conditionMap = new HashMap<String, Object>();
conditionMap.put("gameId", gameId);
//导出全部
if(!"请选择日期".equals(startDate) && !"请选择日期".equals(endDate)){
conditionMap.put("startDate", startDate);
conditionMap.put("endDate", endDate);
}
if(!"请输入中奖等级".equals(level)){
conditionMap.put("level", level);
}
dataSet = playRecordService.conditionQuery(conditionMap);
List<Object[]> dataList = new ArrayList<Object[]>();
Object[] objs = null;
for (int i = 0; i < dataSet.size(); i++) {
PlayRecord man = dataSet.get(i);
objs = new Object[rowName.length];
objs[0] = i;
objs[1] = man.getOpenid();
objs[2] = man.getNickName();
objs[3] = man.getPhoneNumber();
//日期类型处理
Date date = man.getPalyTime();
String dateStr = "";
if(date!=null){
SimpleDateFormat df = new SimpleDateFormat(format);
dateStr = df.format(date);
}
objs[4] = dateStr;
objs[5] = man.getLevel();
objs[6] = man.getAwardName();
dataList.add(objs);
}
leadingOutExcel = new LeadingOutExcel(fileName,title, rowName, dataList,response);
try {
leadingOutExcel.export();
} catch (Exception e) {
log.error("写入Excle出错！", e);
}
}
}
SpringMVC 实现POI读取Excle文件中数据导入数据库（上传）、导出数据库中数据到Excle文件中（下载）
标签：信息 char tca write 开头 apach pat position reac