excel上传和下载

import java.awt.Color;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID; import javax.servlet.http.HttpServletResponse; import org.apache.catalina.tribes.util.Arrays;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver; import cn.com.jtv.kaanalysis.module.dzdynamicexcel.entity.DzDynamicExcel;
import cn.com.jtv.kaanalysis.module.dzdynamicexcel.manager.DzDynamicExcelManager;
import cn.com.jtv.kaanalysis.module.dzdynamicexcelsampleinfo.entity.DzDynamicExcelSampleinfo;
import cn.com.jtv.kaanalysis.module.dzdynamicexcelsampleinfo.manager.DzDynamicExcelSampleinfoManager;
import cn.com.jtv.kaanalysis.module.dzdynamicrule.entity.DzDynamicRule;
import cn.com.jtv.kaanalysis.module.dzdynamicrule.manager.DzDynamicRuleManager;
import cn.com.jtv.mf.core.utils.UtilDate;
import cn.com.jtv.mf.core.utils.json.UtilJson;
import cn.com.jtv.mf.core.web.LoginUserHolder;
import cn.com.jtv.mf.core.web.mvc.BaseEntityJsonAction;
/**
* 动态表格简单信息管理控制器.
* <p>
*
* @version 2016-08-25
* @author wanghj
*/
public class DzDynamicExcelSampleinfoAction extends
BaseEntityJsonAction<DzDynamicExcelSampleinfo> { //DzDynamicExcel
private DzDynamicExcelManager dEntityManager; public DzDynamicExcelManager getdEntityManager() {
return dEntityManager;
}
public void setdEntityManager(DzDynamicExcelManager dEntityManager) {
this.dEntityManager = dEntityManager;
} //DzDynamicRule
private DzDynamicRuleManager rEntityManager; public DzDynamicRuleManager getrEntityManager() {
return rEntityManager;
}
public void setrEntityManager(DzDynamicRuleManager rEntityManager) {
this.rEntityManager = rEntityManager;
} //表头规则排序字段
Integer sortNum = 1; /**
* 导入Excel 2003.
*/
public String importXls() throws Exception {
request.setCharacterEncoding("UTF-8");
Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
// 获得excel文件
// 解析器解析request的上下文
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
request.getSession().getServletContext());
// 先判断request中是否包涵multipart类型的数据，
if (multipartResolver.isMultipart(request)) {
// 再将request中的数据转化成multipart类型的数据
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
Iterator<String> iter = multiRequest.getFileNames();
while (iter.hasNext()) {
MultipartFile file = multiRequest.getFile(iter.next());
if (file != null) {
maps = parseAndSaveExcel2003(file);
request.setAttribute("fileName",file.getOriginalFilename());
request.setAttribute("maps",maps);
}
}
}
return "success";
} //转换excel并保存其中数据到数据库
public Map<String, Map<String, String>> parseAndSaveExcel2003(MultipartFile file) { Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
DzDynamicExcelSampleinfo sampleInfo = null;
try {
POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
// 新建WorkBook
HSSFWorkbook wb = new HSSFWorkbook(pois);
// 获取Sheet（工作薄）总个数
int sheetNumber = wb.getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { // 获取Sheet（工作薄）
HSSFSheet sheet = wb.getSheetAt(i);
// 开始行数
int firstRow = sheet.getFirstRowNum();
// 结束行数
int lastRow = sheet.getLastRowNum();
// 判断该Sheet（工作薄)是否为空
boolean isEmpty = false;
if (firstRow == lastRow) {
isEmpty = true;
} if (!isEmpty) {// 工作薄不为空,获取其中数据,并保存到数据库
Map<String, String> map = new HashMap<String, String>();
int totalRownum = lastRow; //总行数
int importRownum = 0; //成功导入行数
// int failedRownum = 0; //失败行数
ArrayList<Integer> errorList = new ArrayList<Integer>();
// 获得表头固定数据
String dwmcid = LoginUserHolder.getLoginStationId();//登录这所在单位id,
//创建时间,一张表中,表头所包含的每一行的创建时间是相同的
String create_time = UtilDate.dateToString(Calendar.getInstance().getTime(),"yyy-MM-dd HH:mm:ss");
String table_id = UUID.randomUUID().toString();
String create_person = LoginUserHolder.getLoginUserId();//创建人id
String table_name = ""; //每张表的第一行内容为表名
String isheader = "1";
String logic_del = "0";
String id = ""; //UUID,每条数据的id,主键 //获取数据
List<DzDynamicExcel> list = new ArrayList<DzDynamicExcel>();
DzDynamicExcel entity = null;
boolean willExist = false;//如果解析到某一行是0,1,2,3...则说明表头到此为止.不用再解析了
Integer seriesNumber = 0; //需要,用于排序
List<String[]> tempExcels = new ArrayList<String[]>();
List<String[]> rulesData = new ArrayList<String[]>(); for (int j = firstRow; j <= lastRow; j++) { // 获取一行
HSSFRow row = sheet.getRow(j);
// 开始列数
int firstCell = row.getFirstCellNum();
// 结束列数
int lastCell = row.getLastCellNum();
// 判断该行是否为空
String[] value = new String[lastCell];
if (firstCell != lastCell) {
for (int k = firstCell; k < lastCell; k++) {
// 获取一个单元格
HSSFCell cell = row.getCell(k);
value[k] = strFromCell(cell);
}
//是否是表头终止行
String checkStr = value[0]+value[1];
if("01".equals(checkStr)||"null1".equals(checkStr)) {
willExist= true;
}else{
tempExcels.add(value);
id = UUID.randomUUID().toString();
if(j==firstRow){//第一行是标题
table_name = strFromArr(value);
//删除数据库中相同的表
List<DzDynamicExcelSampleinfo> sampleinfos = ((DzDynamicExcelSampleinfoManager)getEntityManager()).deleteByTableName(table_name);
getEntityManager().delete(sampleinfos);
int count = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(table_name);
if(0!=count){
break;//删除失败
}
}else{
rulesData.add(value);//表头规则数据
entity = new DzDynamicExcel(dwmcid, create_time, table_id, table_name, isheader, logic_del,id,value,String.valueOf(seriesNumber++));
if(entity!=null){//导入成功
list.add(entity);
importRownum++;
}/*else {//导入失败
errorList.add(j+1);
failedRownum++;
}*/
}
} }
if(willExist) break;
} //先保存DzDynamicExcelSampleinfo,后保存DzDynamicExcel(因为数据库中有级联)
//一个工作薄一条记录一张表,保存多个工作薄
sampleInfo = new DzDynamicExcelSampleinfo( table_id,
dwmcid,
table_id,
table_name,
create_time,
create_person,
String.valueOf(tempExcels.size()==0?0:getValicColumnSum(tempExcels)), //有效列数
importRownum+""); //有效行数
((DzDynamicExcelSampleinfoManager)getEntityManager()).save(sampleInfo); //保存DzDynamicExcel
getdEntityManager().save(list);
//添加表头合并规则
int index = rulesData.size()-1;//2:例如0,1两行数据,
if (index>=0) {
List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
//表头规则倒序解析,并从倒数第二行开始(如果存在的话),并且每一个解析成功的对象都有一个排序字段(按照解析的次序)
for (int l =index-1 ; l >=0 ; l--) {
String[] row = (String[]) rulesData.get(l);
String[] last_row = null;//上一行数据 last_row = (String[]) rulesData.get(l+1); List<DzDynamicRule> subRules = getMergeRuleToEntities(create_time, table_id,row,last_row);
if(subRules!=null){
rules.addAll(subRules);
}
}
if(rules!=null){
System.out.println(UtilJson.toJson(rules));
((DzDynamicRuleManager)getrEntityManager()).save(rules);
}
int successRownum = importRownum==0?0:(importRownum+2);
map.put("totalRownum", (totalRownum+1)+"");
map.put("importRownum", successRownum+"");
map.put("failedRownum", (totalRownum+1-successRownum)+"");
map.put("errorList", Arrays.toString(errorList.toArray())); maps.put("sheet"+i, map);
}else{
System.out.println("导入失败");;
} }//--if } } catch (IOException e) {
e.printStackTrace();
}
return maps;
} //将cell中不同类型的值全部转换为String类型
private String strFromCell(HSSFCell cell) {
String str = "";
if (cell != null) {
// 获取单元格，值的类型
int cellType = cell.getCellType(); if (cellType == 0) {
Object cellNumber = cell
.getNumericCellValue();
str = new DecimalFormat("#.##")
.format(cellNumber);
} else if (cellType == 1) {
str = cell.getStringCellValue() + "";
// }else if(cellType == 2){
} else if (cellType == 4) {
str = (cell.getBooleanCellValue()) + "";
} else {
str = "";
}
}
return str;
}
//将cell中不同类型的值全部转换为String类型
private String strFromXssfCell(XSSFCell cell) {
String str = "";
if (cell != null) {
// 获取单元格，值的类型
int cellType = cell.getCellType(); if (cellType == 0) {
Object cellNumber = cell
.getNumericCellValue();
str = new DecimalFormat("#.##")
.format(cellNumber);
} else if (cellType == 1) {
str = cell.getStringCellValue() + "";
// }else if(cellType == 2){
} else if (cellType == 4) {
str = (cell.getBooleanCellValue()) + "";
} else {
str = "";
}
}
return str;
} /**
* 导入Excel 2007及以上版本.
*/
public String importXlsx() throws Exception {
Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
// 获得前台上传的文件
Map<String, List<MultipartFile>> files = getUploadFiles();
MultipartFile file = files.get("fileselect").get(0);
// 格式校验
String name = file.getOriginalFilename();
if (!name.endsWith(".xlsx")) {
throw new RuntimeException("目前仅支持 xlsx 格式的文件");
}
// 导入数据
InputStream is = file.getInputStream();
XSSFWorkbook workbook = new XSSFWorkbook(is);
try {
maps = parseAndSaveExcel2007(workbook);
request.setAttribute("fileName",file.getOriginalFilename());
request.setAttribute("maps",maps);
} finally {
try {
workbook.close();
} catch (Exception ex) {
}
}
return "success";
} private Map<String, Map<String, String>> parseAndSaveExcel2007(XSSFWorkbook workbook) { Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
DzDynamicExcelSampleinfo sampleInfo = null; int sheetNum = workbook.getNumberOfSheets(); //遍历sheet
for (int x = 0; x < sheetNum; x++) { XSSFSheet sheet = workbook.getSheetAt(x);
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
if(firstRow != lastRow) { Map<String, String> map = new HashMap<String, String>();
int totalRownum = lastRow; //总行数
int importRownum = 0; //成功导入行数
// int failedRownum = 0; //失败行数
ArrayList<Integer> errorList = new ArrayList<Integer>();
// 获得表头固定数据
String dwmcid = LoginUserHolder.getLoginStationId();//登录这所在单位id,
//创建时间,一张表中,表头所包含的每一行的创建时间是相同的
String create_time = UtilDate.dateToString(Calendar.getInstance().getTime(),"yyy-MM-dd HH:mm:ss");
String table_id = UUID.randomUUID().toString();
String create_person = LoginUserHolder.getLoginUserId();//创建人id
String table_name = ""; //每张表的第一行内容为表名
String isheader = "1";
String logic_del = "0";
String id = ""; //UUID,每条数据的id,主键 //获取数据
List<DzDynamicExcel> list = new ArrayList<DzDynamicExcel>();
DzDynamicExcel entity = null;
boolean willExist = false;//如果解析到某一行是0,1,2,3...则说明表头到此为止.不用再解析了
Integer seriesNumber = 0; //需要,用于排序
List<String[]> tempExcels = new ArrayList<String[]>();
List<String[]> rulesData = new ArrayList<String[]>();
// 遍历行
for (int i = 0; i < lastRow; i++) {
XSSFRow row = sheet.getRow(i);
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
if (firstCellNum == lastCellNum) break; String[] value = new String[lastCellNum];
for (int j = firstCellNum; j <lastCellNum; j++) {//遍历列
XSSFCell cell = row.getCell(j);
value[j] = strFromXssfCell(cell);
}
//是否是表头终止行
String checkStr = value[0]+value[1];
if("01".equals(checkStr)||"null1".equals(checkStr)) {
willExist= true;
}else{
tempExcels.add(value);
id = UUID.randomUUID().toString();
if(i==firstRow){//第一行是标题
table_name = strFromArr(value);
//删除数据库中相同的表
List<DzDynamicExcelSampleinfo> sampleinfos = ((DzDynamicExcelSampleinfoManager)getEntityManager()).deleteByTableName(table_name);
getEntityManager().delete(sampleinfos);
int count = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(table_name);
if(0!=count){
break;//删除失败
}
}else{
rulesData.add(value);//表头规则数据
entity = new DzDynamicExcel(dwmcid, create_time, table_id, table_name, isheader, logic_del,id,value,String.valueOf(seriesNumber++));
if(entity!=null){//导入成功
list.add(entity);
importRownum++;
}/*else {//导入失败
errorList.add(j+1);
failedRownum++;
}*/
}
}
if(willExist) break;
}//遍历行结束 //先保存DzDynamicExcelSampleinfo,后保存DzDynamicExcel(因为数据库中有级联)
//一个工作薄一条记录一张表,保存多个工作薄
sampleInfo = new DzDynamicExcelSampleinfo( table_id,
dwmcid,
table_id,
table_name,
create_time,
create_person,
String.valueOf(tempExcels.size()==0?0:getValicColumnSum(tempExcels)), //有效列数
importRownum+""); //有效行数
((DzDynamicExcelSampleinfoManager)getEntityManager()).save(sampleInfo); //保存DzDynamicExcel
getdEntityManager().save(list);
//添加表头合并规则
int index = rulesData.size()-1;//2:0,1例如两行数据,
if (index>=0) {
List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
//表头规则倒序解析,并从倒数第二行开始(如果存在的话),并且每一个解析成功的对象都有一个排序字段(按照解析的次序)
for (int l =index-1 ; l >=0 ; l--) {
String[] row = (String[]) rulesData.get(l);
String[] last_row = null;//上一行数据 last_row = (String[]) rulesData.get(l+1); List<DzDynamicRule> subRules = getMergeRuleToEntities(create_time, table_id,row,last_row);
if(subRules!=null){
rules.addAll(subRules);
}
}
if(rules!=null){
System.out.println(UtilJson.toJson(rules));
((DzDynamicRuleManager)getrEntityManager()).save(rules);
}
int successRownum = importRownum==0?0:(importRownum+2);
map.put("totalRownum", (totalRownum+1)+"");
map.put("importRownum", successRownum+"");
map.put("failedRownum", (totalRownum+1-successRownum)+"");
map.put("errorList", Arrays.toString(errorList.toArray())); maps.put("sheet"+x, map);
}else{
System.out.println("导入失败");;
}
}//--if(firstRow != lastRow) { }//--for sheet end return maps;
} // 将数组转换为字符串
public static String strFromArr(String[] arr) {
String strs = "";
for (String str : arr) {
strs += (str == null ? "" : str);
}
return strs;
} /**
* 处理多余的列,多余的null列不需要
* @param data 待处理的List
* @return 处理过的List
*/
public Integer getValicColumnSum(List<String[]> data){
int len = data.size();
int maxIndex = 0;
if(data!=null){
for (int j = 0; j < len; j++) {//遍历每行数据
Object[] obj = data.get(j);
int maxLen = obj.length<100?obj.length:100;
for (int i = 0; i < maxLen; i++) { //这一行从后面第一个不为null的值的位置,
int currIndex = obj.length-i-1;
if(obj[currIndex]!=null&&!"".equals(obj[currIndex])){
if(maxIndex<currIndex){
maxIndex = currIndex;
}
int rowCol = data.get(j).length-1;
if(maxIndex<rowCol){
maxIndex= rowCol;
}
break;
}
}
} }
return maxIndex+1;
} /**
* 异步检查xlsx文件中的表是否已经存在
* @throws Exception
*/
public void xlsxExist() throws Exception{
// 获得前台上传的文件
Map<String, List<MultipartFile>> files = getUploadFiles();
MultipartFile file = files.get("fileselect").get(0); // 导入数据
InputStream is = file.getInputStream();
XSSFWorkbook workbook = new XSSFWorkbook(is);
Map<String, String> map = new HashMap<String, String>();
try {
map = checkXlsx(workbook);
String json = UtilJson.toJson(map);
PrintWriter out = response.getWriter();
out.print(json);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
workbook.close();
} catch (Exception ex) {
}
}
} private Map<String, String> checkXlsx(XSSFWorkbook workbook) {
Map<String, String> map = new HashMap<String,String>();
int sheetNum = workbook.getNumberOfSheets();
//遍历sheet
for (int x = 0; x < sheetNum; x++) { XSSFSheet sheet = workbook.getSheetAt(x);
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
if(firstRow == lastRow) break; XSSFRow row = sheet.getRow(0); int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
if (firstCellNum == lastCellNum) break;
String[] value = new String[lastCellNum];
if (firstCellNum != lastCellNum) {
for (int k = firstCellNum; k < lastCellNum; k++) {
// 获取一个单元格
XSSFCell cell = row.getCell(k);
String str = "";
if (cell != null) {
// 获取单元格，值的类型
int cellType = cell.getCellType(); if (cellType == 0) {
Object cellNumber = cell
.getNumericCellValue();
str = new DecimalFormat("#.##")
.format(cellNumber);
} else if (cellType == 1) {
str = cell.getStringCellValue() + "";
// }else if(cellType == 2){
} else if (cellType == 4) {
str = (cell.getBooleanCellValue()) + "";
} else {
str = "";
}
}
value[k] = str;
} }
String tableName = strFromArr(value);
Integer num = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(tableName);
map.put(tableName,String.valueOf(num));
}
return map;
} /**
* 异步检查Xls文件中的表是否已经给存在
* 传给前台的是json格式的map字符串,{表名:已经存在次数}
*/
public void xlsExist() { try {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("utf-8");
Map<String, String> maps = new HashMap<String, String>();
// 获得excel文件
// 解析器解析request的上下文
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
request.getSession().getServletContext());
// 先判断request中是否包涵multipart类型的数据，
if (multipartResolver.isMultipart(request)) {
// 再将request中的数据转化成multipart类型的数据
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
Iterator<String> iter = multiRequest.getFileNames();
while (iter.hasNext()) {
MultipartFile file = multiRequest.getFile(iter.next());
if (file != null) {
maps = checkXls(file);
}
}
}
String json = UtilJson.toJson(maps);
PrintWriter out = response.getWriter();
out.print(json);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
} private Map<String,String> checkXls(MultipartFile file) {
Map<String, String> map = new HashMap<String,String>();
try {
POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
// 新建WorkBook
HSSFWorkbook wb = new HSSFWorkbook(pois);
// 获取Sheet（工作薄）总个数
// int sheetNumber = wb.getNumberOfSheets(); // 获取Sheet（工作薄）
HSSFSheet sheet = wb.getSheetAt(0);
// 开始行数
int firstRow = sheet.getFirstRowNum();
// 结束行数
int lastRow = sheet.getLastRowNum();
// 判断该Sheet（工作薄)是否为空
boolean isEmpty = false;
if (firstRow == lastRow) {
isEmpty = true;
} if (!isEmpty) {// 工作薄不为空,获取其中数据,并保存到数据库 // 获取一行
HSSFRow row = sheet.getRow(firstRow);
// 开始列数
int firstCell = row.getFirstCellNum();
// 结束列数
int lastCell = row.getLastCellNum();
// 判断该行是否为空
String[] value = new String[lastCell];
if (firstCell != lastCell) {
for (int k = firstCell; k < lastCell; k++) {
// 获取一个单元格
HSSFCell cell = row.getCell(k);
value[k] = strFromCell(cell);
} }
String tableName = strFromArr(value);
Integer num = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(tableName);
map.put(tableName,String.valueOf(num));
} } catch (IOException e) {
e.printStackTrace();
}
return map;
} //根据表头的一行数据,得到合并规则
public List<DzDynamicRule> getMergeRuleToEntities(String createTime, String tableId,String[] row,String[] last_row){
List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
for (int i = 0; i < row.length; i++) {
String groupName = row[i];
if(!"".equals(groupName)){//空白单元格不处理
int field_count = 1;
for (int j = i+1; j < row.length; j++) {//判断当前单元格,后面的空白单元格数
if("".equals(row[j])){
field_count++;
}else{
break;
}
}
//完成一个单元格的判定了,并且这个单元格,是需要合并的
if(field_count>1){
String[] group = new String[]{groupName,"col_"+(i+1),field_count+""};
DzDynamicRule rule = new DzDynamicRule(createTime, UUID.randomUUID().toString(), tableId, sortNum++, group);
rules.add(rule);
i = i+field_count-1;
}else if(field_count==1&&last_row.length!=0&&i<last_row.length){//如果是一个单元格,需要判断上一行同列单元格是否为空?
if (!"".equals(last_row[i])) {//上行同列单元格为空,记录规则
String[] group = new String[]{groupName,"col_"+(i+1),"1"};
DzDynamicRule rule = new DzDynamicRule(createTime, UUID.randomUUID().toString(), tableId, sortNum++, group);
rules.add(rule);
i = i+field_count-1;
}
}
field_count = 1;
}
}
return rules;
} //导出数据为Xlsx文件
public void exportXlsx(){ //1.将数据转换为excel
String tableId = request.getParameter("tableId");
System.out.println(tableId);
//获取表头,获取表头规则,获取数据
//表信息
List<Object[]> list = ((DzDynamicExcelSampleinfoManager) getEntityManager()).getSampleInfo(tableId);
Object[] sampleInfo = list.get(0);//表名,有效列数,有效行数
//获取表头,数据
List<Object[]> datas = ((DzDynamicExcelManager)getdEntityManager()).getData(tableId);
//获取表头规则
List<Object[]> rules = ((DzDynamicRuleManager)getrEntityManager()).getRules(tableId); XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(); //row:表名
XSSFRow titlerow = sheet.createRow(0);
XSSFCell titlecell = titlerow.createCell(0,XSSFCell.CELL_TYPE_STRING);
titlecell.setCellValue(String.valueOf(sampleInfo[0]));//表名,表标题
//边框,样式
XSSFCellStyle style = getNewCenterStyle(workbook,Color.white,"title");
CellRangeAddress region = new CellRangeAddress(0,(short)0,0,Short.valueOf(sampleInfo[1]+"")-1);//设置合并的行列
setRegionStyle(sheet,region,style); //设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
sheet.addMergedRegion(region); //将单元格合并 //有效列数
int validColumn = Integer.valueOf(sampleInfo[1]+"");
int validRow = Integer.valueOf(sampleInfo[2]+"");
//row:表头,内容
for (int i = 0; i < datas.size(); i++) {
XSSFRow row = sheet.createRow(i+1);
Object[] rowdata = datas.get(i);
//表头
if (i+1<=validRow) {
style = getNewCenterStyle(workbook,Color.lightGray,"header");
for (int j = 0; j < validColumn; j++) {
XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
if(j>rowdata.length-1){
cell.setCellValue("");
continue;
}
cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
cell.setCellStyle(style);
}
//内容
}else{
style = getNewCenterStyle(workbook,new Color(214, 214, 214),"body");
for (int j = 0; j < validColumn; j++) {
XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
if(j>rowdata.length-1){
cell.setCellValue("");
continue;
}
cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
cell.setCellStyle(style);
}
} }
//合并表头
sheet = mergeAllRange(sheet, validRow, validColumn);
//设置表头样式
style = getNewCenterStyle(workbook,Color.lightGray,"header");
setAllRangeStyle(sheet, style);
try
{
//保存excel到磁盘
@SuppressWarnings("deprecation")
String directory = request.getRealPath("/");
File file = new File(directory+"\\kaanalysis\\module\\dzdynamicexcelsampleinfo\\dzdynamic_export_excels\\"+sampleInfo[0]+".xlsx");
System.out.println(file.getAbsolutePath());
FileOutputStream fout = new FileOutputStream(file);
workbook.write(fout);
fout.close(); //下载excel
downLoadFile(response, file.getAbsolutePath(), "xlsx");
}catch (Exception e) {
e.printStackTrace();
}
} /**
* 合并表头,并添加表头样式
* @param sheet
* @param validrow
* @param validcol
* @return
*/
private static XSSFSheet mergeAllRange(XSSFSheet sheet,int validrow,int validcol){
XSSFCellStyle style = getNewCenterStyle(sheet.getWorkbook(),Color.lightGray,"header");
int countrow = 1;
int countcol = 1;
for (int row = 1; row <validrow; row++) {//eg:3,则1,2行需要合并,3行为最后一行表头,不需要合并
XSSFRow rowdata = sheet.getRow(row);
if (rowdata==null) {
continue;
}
for (int col = 0; col < validcol; col++) {
//当前单元格非空,
if (rowdata.getCell(col)!=null&&!"".equals(rowdata.getCell(col).getStringCellValue())) {
//计算空白列
for (int i = col+1; i < validcol; i++) {
if (rowdata.getCell(i)!=null&&!"".equals(rowdata.getCell(i).getStringCellValue())) {
break;
}
countcol++;
}
//计算空白行
for (int i = row+1; i <= validrow; i++) {
if(sheet.getRow(i).getCell(col)!=null&&!"".equals(sheet.getRow(i).getCell(col).getStringCellValue())){
break;
} countrow++;
}
}
if (countcol!=1||countrow!=1) {
CellRangeAddress range = new CellRangeAddress(row,(short)(row+countrow-1),col,(short)(col+countcol-1));//设置合并的行列
setRegionStyle(sheet,range,style);//设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
sheet.addMergedRegion(range);//将单元格合并
//重新计数
countcol = 1;
countrow = 1;
}
}
}
return sheet;
} /**
* 获取excel边框,样式
* @param workBook
* @param bgColor 背景色
* @param type 类型:title标题,Header表头,body内容
* @return
*/ private static XSSFCellStyle getNewCenterStyle(XSSFWorkbook workBook,Color bgColor,String type){
XSSFCellStyle style = workBook.createCellStyle();;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//字体
XSSFFont font = workBook.createFont();
if ("title".equals(type)) {
font.setFontName("黑体");
font.setColor(new XSSFColor(Color.green));//字体颜色
font.setFontHeight(60);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font.setFontHeightInPoints((short) 22);
}else if ("header".equals(type)) {
font.setFontName("宋体");
font.setColor(new XSSFColor(Color.red));
font.setFontHeightInPoints((short) 12);
//设置单元格边框颜色
XSSFColor borderColor = new XSSFColor(Color.red);
style.setTopBorderColor(borderColor);
style.setBottomBorderColor(borderColor);
style.setLeftBorderColor(borderColor);
style.setRightBorderColor(borderColor);
//设置单元格背景色
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(new XSSFColor(bgColor));// 设置背景色,好像不起作用
style.setFillForegroundColor(new XSSFColor(bgColor));// 设置前景色
}else if ("body".equals(type)) {
font.setFontName("仿宋_GB2312");
font.setFontHeightInPoints((short) 10); //字体大小
}
style.setFont(font);
return style;
} /**
* 设置合并区域样式
* 设置excel边框,样式
* @param sheet sheet
* @param range 合并区域
* @param cs 样式
*/
private static void setRegionStyle(XSSFSheet sheet, CellRangeAddress range , XSSFCellStyle cs) {
for (int i = range.getFirstRow(); i <= range.getLastRow(); i ++) {
XSSFRow row = sheet.getRow(i);
if(range.getFirstColumn()!=range.getLastColumn()){
for (int j = range.getFirstColumn(); j <= range.getLastColumn(); j++) {
XSSFCell cell = row.getCell((short)j);
if( cell==null){
cell=row.createCell(j);
cell.setCellValue("");
}
cell.setCellStyle(cs);
}
}
}
} /**
* 一次性设置所有合并区域,但不包括表题,其实只是表头
* @param sheet 工作薄
* @param style 样式
*/
private void setAllRangeStyle(XSSFSheet sheet , XSSFCellStyle style){
int num = sheet.getNumMergedRegions();
for (int i = 1; i < num; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
setRegionStyle(sheet, range, style);
}
} //excel 下载
public static boolean downLoadFile(HttpServletResponse response, String fileFullName, String fileType)
throws Exception {
File file = new File(fileFullName); //根据文件路径获得File文件
//设置文件类型(这样设置就不止是下Excel文件了，一举多得)
if("pdf".equals(fileType)){
response.setContentType("application/pdf;charset=GBK");
}else if("xls".equals(fileType)||"xlsx".equals(fileType)){
response.setContentType("application/msexcel;charset=GBK");
}else if("doc".equals(fileType)){
response.setContentType("application/msword;charset=GBK");
} //文件名
response.setHeader("Content-Disposition", "attachment;filename=\""
+ new String(fileFullName.substring(fileFullName.lastIndexOf("\\")).getBytes(), "ISO8859-1") + "\"");
response.setContentLength((int) file.length());
BufferedOutputStream output = null;
BufferedInputStream input = null;
try {
output = new BufferedOutputStream(response.getOutputStream());
InputStream fis = new BufferedInputStream(new FileInputStream(file));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
output.write(buffer);
output.flush(); //不可少
output.close();
response.flushBuffer();//不可少
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭流，不可少
if (input != null)
input.close();
if (output != null)
output.close();
} return false;
} }