需要注意的地方:
1.js构造表单并提交
encodeURI("中文")),action接收并转换value = URLDecoder.decode(value,"utf-8");
3.excel2003和excel2007以上版本,java代码有所区别
4.excel边框,样式,字体,颜色等设置;excel单元格的合并
5.excel文件的下载
1.excel下载
1.1 js构造excel下载表单,设置查询参数
/* charset:UTF-8 */
$(document).ready(function() {
//添加form,用于下载excel文件
$("body").append( '<div class="form_container" style="display:none;">'+
'<form id="download_excel_form" action="" method="post">'+
'<input type="text name="nessesary" value="">'+
'<input type="submit" name="submit" id="form_container_submit">'+
'</form>'+
'</div>'
);
});
//重写方法:查询页面,根据查询条件,导出个人信息(完整信息)
function doExpExcel(){
//查询条件16个
//name,gender,birth,fhighestDegree全日制,nhighestAcademic非全日制最高学位,whetherPost是否在岗,
//stationId单位,note是否审核,note1是否委派,currentPosition现任职务
//accountingQualification会计专业技术资格,technicalPosition会计专业技术职务,administrativeLevel行政级别
//appointmentTime现任职务时间,unitClassification单位分类,jobPosition现从事会计工作岗位
var action = "/cwgl/cwgl/cwglpersonnel/CwglPersonnel.do?method=exportXlsx";
//获取查询参数
//单位名称
action = getSelectedValue("stationId", action);
//姓名
action += "&name="+$("#name").val();
//性别
action = getSelectedValue("gender", action);
//出生日期
action += "&birth="+$("#birth").val();
action = getSelectedValue("administrativeLevel", action);
action = getSelectedValue("fhighestDegree", action);
action = getSelectedValue("nhighestDegree", action);
action = getSelectedValue("whetherPost", action);
action = getSelectedValue("accountingQualification", action);
action = getSelectedValue("technicalPosition", action);
action += "&appointmentTime="+$("#appointmentTime").val();//现任职务时间
action = getSelectedValue("note1", action);
action = getSelectedValue("note", action);
action = getSelectedValue("unitClassification", action);
action = getSelectedValue("jobPosition", action);
console.log(action);
var form = $("#download_excel_form");
form.attr("action",encodeURI(encodeURI(action)));
// $("#download_excel_form").submit();
$("#form_container_submit").click();
}
//下拉列表所选值
function getSelectedValue(id,action){
var _txt = $("#"+id+"_dd_text").val();
$("#"+id+" option").each(function(){
if($(this).text()==_txt){
action += "&"+id+"="+$(this).val();
}
});
return action;
}
1.2 action 获取查询参数,查询数据,poi生成excel,下载excel
//导出数据为Xlsx文件
public void exportXlsx(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws UnsupportedEncodingException{
79个字段
String[] head = new String[]{
"从业资格档案号码","姓名","民族","身份证号","性别","出生日期","政治面貌","全日制最高学历","全日制最高学历毕业学校","全日制最高学历毕业时间",
"全日制最高学历所学专业","全日制最高学位","非全日制最高学历","非全日制学历毕业学校","非全日制学历毕业时间","非全日制学历所学专业","非全日制最高学位","非全日制最高学位毕业学校","非全日制最高学位毕业时间","非全日制最高学位所学专业",
"单位代码","单位名称","具体下属单位","工作单位经济类型","单位分类","工作单位电话","单位地址","单位邮政编码","会计行政职务","行政级别",
"行政级别任职命令号","现任职务","参加工作时间","从事会计工作时间","现从事会计工作岗位","珠算等级","珠算证号","珠算证取得时间","电算级别","电算证号",
"电算证取得时间","继续教育成绩","本年学时","继续教育开始时间","首次从业资格证发证机关","会计从业资格证取得方式","资格证最初取得时间","发证单位","从业资格证发证日期","注册时间",
"是否在岗","会计专业技术资格","会计专业技术资格取得方式","会计专业技术资格取得时间","会计专业技术资格证号或批文号","会计专业技术职务","会计专业技术职务聘任时间","会计专业技术职务任职命令号","非会计专业技术资格级别","非会计专业技术资格类型",
"非会计专业技术资格取得时间","非会计专业技术资格证书号或批文号","非会计专业技术资格取得方式","注册会计师","资产评估师","注册税务师","其他资格","IC卡号","诚信记录档案","有效证件名称",
"有效证件号","籍贯","出生地","家庭住址","联系电话","电子邮箱","外语掌握情况","是否审核","是否委派"};
//查询条件16个
//name,gender,birth,fhighestDegree全日制,nhighestAcademic非全日制最高学位,whetherPost是否在岗,
//stationId单位,note是否审核,note1是否委派,currentPosition现任职务
//accountingQualification会计专业技术资格,technicalPosition会计专业技术职务,administrativeLevel行政级别
//appointmentTime现任职务时间,unitClassification单位分类,jobPosition现从事会计工作岗位
LinkedList<String> names = new LinkedList<String>();
LinkedList<String> values = new LinkedList<String>();
@SuppressWarnings("unchecked")
Enumeration<String> nameEnum = request.getParameterNames();
while (nameEnum.hasMoreElements()) {
String name = (String) nameEnum.nextElement();
String value = request.getParameter(name);
if (value!=null&&!"".equals(value.trim())&&!"exportXlsx".equals(value)&&!"提交查询".equals(value)) {
Pattern pattern = Pattern.compile("([A-Z])");
Matcher matcher = pattern.matcher(name);
while(matcher.find()){
if(matcher.groupCount()>0){
name = matcher.replaceAll("_"+matcher.group(1).toLowerCase());
}
}
names.add(name);
value = URLDecoder.decode(value,"utf-8");
values.add(value);
}
}
CwglPersonnelManager manager = ((CwglPersonnelManager) getEntityManager());
List<Object[]> datas = manager.getAllData(names,values);
//1.将数据转换为excel
short validColNum = 79;
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("人员信息");//表名,表标题
//标题样式
XSSFCellStyle style = getNewCenterStyle(workbook,Color.gray,"title");
CellRangeAddress region = new CellRangeAddress(0,(short)0,0,validColNum-1);//设置合并的行列
sheet.addMergedRegion(region);//将单元格合并
setRegionStyle(sheet,region,style);//设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
// setRegionBorder(XSSFCellStyle.BORDER_DASHED, region, sheet, workbook);
//表头样式
setAllRangeStyle(sheet,style);
style = getNewCenterStyle(workbook, Color.LIGHT_GRAY, "header");
//row:表头,
XSSFRow headrow = sheet.createRow(1);
for (int i = 0; i < head.length; i++) {
XSSFCell headcell = headrow.createCell(i,XSSFCell.CELL_TYPE_STRING);
headcell.setCellValue(head[i]);
headcell.setCellStyle(style);
}
//内容数据样式
style = getNewCenterStyle(workbook, new Color(192, 192, 192),"body");;
//row:内容
for (int i = 0; i < datas.size(); i++) {
XSSFRow row = sheet.createRow(i+2);
Object[] rowdata = datas.get(i);//.values().toArray();
for (int j = 0; j < validColNum; j++) {
XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
cell.setCellStyle(style);
}
}
try
{
//保存excel到磁盘
@SuppressWarnings("deprecation")
String directory = request.getRealPath("/");
File file = new File(directory+"\\cwgl\\module\\cwglpersonnel\\人员信息.xlsx");
System.out.println(file.getAbsolutePath());
FileOutputStream fout = new FileOutputStream(file);
workbook.write(fout);
fout.close();
//下载excel
downLoadFile(request,response, file.getAbsolutePath(), "xlsx");
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取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) 9); //字体大小
}
style.setFont(font);
return style;
}
/**
* 设置合并区域样式
* 设置excel边框,样式
* @param sheet sheet
* @param range 合并区域
* @param cs 样式
*/
private 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 = 0; i < num; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
setRegionStyle(sheet, range, style);
}
}
/**
* excel 下载
* @param response response
* @param fileFullName 文件名包括路径
* @param fileType 文件类型:pdf,xls或xlsx,doc或docx
* @return
* @throws Exception
*/
public static boolean downLoadFile(HttpServletRequest request,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)||"docx".equals(fileType)){
response.setContentType("application/msword;charset=GBK");
}
//文件名
String fileName = fileFullName.substring(fileFullName.lastIndexOf("\\"));
String userAgent = request.getHeader("User-Agent");
//针对IE或者以IE为内核的浏览器：
if (userAgent.contains("MSIE")||userAgent.contains("Trident")) {
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
} else {
//非IE浏览器的处理：
fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
}
response.setHeader("Content-Disposition", "attachment;filename=\""+ fileName + "\"");
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;
}
/**
* 利用poi自带工具RegionUtil来设置边框
* @param border
* @param region
* @param sheet
* @param wb
*/
private static void setRegionBorder(int border, CellRangeAddress region, Sheet sheet,Workbook wb){
RegionUtil.setBorderBottom(border,region, sheet, wb);
RegionUtil.setBorderLeft(border,region, sheet, wb);
RegionUtil.setBorderRight(border,region, sheet, wb);
RegionUtil.setBorderTop(border,region, sheet, wb);
}
}
1.3 manager 数据接口
/**
* 查询所有字段,根据参数
* @param names names条件字段
* @param values values条件值
* @return
*/
public List<Object[]> getAllData(LinkedList<String> names,LinkedList<String> values){
StringBuffer sb = new StringBuffer();
//79个字段
sb.append(" select "+
"t.file_num,,t.nation,t.id_num,t.gender,t.birth,t.political_outlook,t.fhighest_degree,t.fgraduation_school,t.fgraduation_time,"+
"t.fschool_major,t.fhighest_academic,t.nhighest_degree,t.ngraduation_school,t.ngraduation_time,t.nschool_major,t.nhighest_academic,t.nzgraduation_school,t.nzgraduation_time,t.nzschool_major,"+
"t.station_id,t.station_name,t.subordinate_unit,t.economic_type,t.unit_classification,t.telephone,t.address,t.post,t.administrative,t.administrative_level,"+
"t.command_number,t.current_position,t.work_time,t.accounting_time,t.job_position,t.abacus_level,t.abacus_no,t.abacus_time,t.power_level,t.power_num,"+
"t.power_time,t.continuing_education,t.hours_year,t.continuing_time,t.issuing_authority,t.get_way,t.get_time,t.issuing_unit,t.issuing_time,t.registration_time,"+
"t.whether_post,t.accounting_qualification,t.qualification_way,t.qualification_time,t.qualification_num,t.technical_position,t.appointment_time,t.post_number,t.nqualification_level,t.nqualification_type,"+
"t.nqualification_gettime,t.npermit_no,t.nqualification_way,t.registered,t.assessment_division,t.tax_division,t.other_qualifications,t.ic,t.integrity_record,t.certificate_name,"+
"t.certificate_num,t.place_origin,t.place_birth,t.home_address,t.personal_phone,t.electronic_mail,t.foreign_language,t.note,t.note1 "+
"from CWGL_PERSONNEL t "+
"where 1=1 "
);
for (int i = 0; i < names.size(); i++) {
String name = names.get(i);
if ("birth".equals(name)||"appointmentTime".equals(name)) {
sb.append(" and to_char(to_date("+name+",'yyyy-MM-dd'),'yyyy-MM') = ").append("'"+ values.get(i) +"'");
}else{
sb.append(" and "+name+" = '").append(values.get(i)).append("'");
}
}
String wheresql = sb.toString();
Session session = getExtDao().openSession();
Query query = session.createSQLQuery(wheresql);
@SuppressWarnings("unchecked")
List<Object[]> list = query.list();
return list;
}
2.excel上传和下载完整代码
先上图:看着效果挺不错^_^
1 import java.awt.Color;
2 import java.io.BufferedInputStream;
3 import java.io.BufferedOutputStream;
4 import java.io.File;
5 import java.io.FileInputStream;
6 import java.io.FileOutputStream;
7 import java.io.IOException;
8 import java.io.InputStream;
9 import java.io.PrintWriter;
10 import java.text.DecimalFormat;
11 import java.util.ArrayList;
12 import java.util.Calendar;
13 import java.util.HashMap;
14 import java.util.Iterator;
15 import java.util.List;
16 import java.util.Map;
17 import java.util.UUID;
18
19 import javax.servlet.http.HttpServletResponse;
20
21 import org.apache.catalina.tribes.util.Arrays;
22 import org.apache.poi.hssf.usermodel.HSSFCell;
23 import org.apache.poi.hssf.usermodel.HSSFRow;
24 import org.apache.poi.hssf.usermodel.HSSFSheet;
25 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
26 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
27 import org.apache.poi.ss.util.CellRangeAddress;
28 import org.apache.poi.xssf.usermodel.XSSFCell;
29 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
30 import org.apache.poi.xssf.usermodel.XSSFColor;
31 import org.apache.poi.xssf.usermodel.XSSFFont;
32 import org.apache.poi.xssf.usermodel.XSSFRow;
33 import org.apache.poi.xssf.usermodel.XSSFSheet;
34 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
35 import org.springframework.web.multipart.MultipartFile;
36 import org.springframework.web.multipart.MultipartHttpServletRequest;
37 import org.springframework.web.multipart.commons.CommonsMultipartResolver;
38
39 import cn.com.jtv.kaanalysis.module.dzdynamicexcel.entity.DzDynamicExcel;
40 import cn.com.jtv.kaanalysis.module.dzdynamicexcel.manager.DzDynamicExcelManager;
41 import cn.com.jtv.kaanalysis.module.dzdynamicexcelsampleinfo.entity.DzDynamicExcelSampleinfo;
42 import cn.com.jtv.kaanalysis.module.dzdynamicexcelsampleinfo.manager.DzDynamicExcelSampleinfoManager;
43 import cn.com.jtv.kaanalysis.module.dzdynamicrule.entity.DzDynamicRule;
44 import cn.com.jtv.kaanalysis.module.dzdynamicrule.manager.DzDynamicRuleManager;
45 import cn.com.jtv.mf.core.utils.UtilDate;
46 import cn.com.jtv.mf.core.utils.json.UtilJson;
47 import cn.com.jtv.mf.core.web.LoginUserHolder;
48 import cn.com.jtv.mf.core.web.mvc.BaseEntityJsonAction;
49 /**
50 * 动态表格简单信息管理控制器.
51 * <p>
52 *
53 * @version 2016-08-25
54 * @author wanghj
55 */
56 public class DzDynamicExcelSampleinfoAction extends
57 BaseEntityJsonAction<DzDynamicExcelSampleinfo> {
58
59 //DzDynamicExcel
60 private DzDynamicExcelManager dEntityManager;
61
62 public DzDynamicExcelManager getdEntityManager() {
63 return dEntityManager;
64 }
65 public void setdEntityManager(DzDynamicExcelManager dEntityManager) {
66 this.dEntityManager = dEntityManager;
67 }
68
69 //DzDynamicRule
70 private DzDynamicRuleManager rEntityManager;
71
72 public DzDynamicRuleManager getrEntityManager() {
73 return rEntityManager;
74 }
75 public void setrEntityManager(DzDynamicRuleManager rEntityManager) {
76 this.rEntityManager = rEntityManager;
77 }
78
79 //表头规则排序字段
80 Integer sortNum = 1;
81
82
83
84
85
86 /**
87 * 导入Excel 2003.
88 */
89 public String importXls() throws Exception {
90 request.setCharacterEncoding("UTF-8");
91 Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
92 // 获得excel文件
93 // 解析器解析request的上下文
94 CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
95 request.getSession().getServletContext());
96 // 先判断request中是否包涵multipart类型的数据，
97 if (multipartResolver.isMultipart(request)) {
98 // 再将request中的数据转化成multipart类型的数据
99 MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
100 Iterator<String> iter = multiRequest.getFileNames();
101 while (iter.hasNext()) {
102 MultipartFile file = multiRequest.getFile(iter.next());
103 if (file != null) {
104 maps = parseAndSaveExcel2003(file);
105 request.setAttribute("fileName",file.getOriginalFilename());
106 request.setAttribute("maps",maps);
107 }
108 }
109 }
110 return "success";
111 }
112
113 //转换excel并保存其中数据到数据库
114 public Map<String, Map<String, String>> parseAndSaveExcel2003(MultipartFile file) {
115
116 Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
117 DzDynamicExcelSampleinfo sampleInfo = null;
118 try {
119 POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
120 // 新建WorkBook
121 HSSFWorkbook wb = new HSSFWorkbook(pois);
122 // 获取Sheet（工作薄）总个数
123 int sheetNumber = wb.getNumberOfSheets();
124
125 for (int i = 0; i < sheetNumber; i++) {
126
127 // 获取Sheet（工作薄）
128 HSSFSheet sheet = wb.getSheetAt(i);
129 // 开始行数
130 int firstRow = sheet.getFirstRowNum();
131 // 结束行数
132 int lastRow = sheet.getLastRowNum();
133 // 判断该Sheet（工作薄)是否为空
134 boolean isEmpty = false;
135 if (firstRow == lastRow) {
136 isEmpty = true;
137 }
138
139 if (!isEmpty) {// 工作薄不为空,获取其中数据,并保存到数据库
140 Map<String, String> map = new HashMap<String, String>();
141 int totalRownum = lastRow; //总行数
142 int importRownum = 0; //成功导入行数
143 // int failedRownum = 0; //失败行数
144 ArrayList<Integer> errorList = new ArrayList<Integer>();
145 // 获得表头固定数据
146 String dwmcid = LoginUserHolder.getLoginStationId();//登录这所在单位id,
147 //创建时间,一张表中,表头所包含的每一行的创建时间是相同的
148 String create_time = UtilDate.dateToString(Calendar.getInstance().getTime(),"yyy-MM-dd HH:mm:ss");
149 String table_id = UUID.randomUUID().toString();
150 String create_person = LoginUserHolder.getLoginUserId();//创建人id
151 String table_name = ""; //每张表的第一行内容为表名
152 String isheader = "1";
153 String logic_del = "0";
154 String id = ""; //UUID,每条数据的id,主键
155
156 //获取数据
157 List<DzDynamicExcel> list = new ArrayList<DzDynamicExcel>();
158 DzDynamicExcel entity = null;
159 boolean willExist = false;//如果解析到某一行是0,1,2,3...则说明表头到此为止.不用再解析了
160 Integer seriesNumber = 0; //需要,用于排序
161 List<String[]> tempExcels = new ArrayList<String[]>();
162 List<String[]> rulesData = new ArrayList<String[]>();
163
164 for (int j = firstRow; j <= lastRow; j++) {
165
166 // 获取一行
167 HSSFRow row = sheet.getRow(j);
168 // 开始列数
169 int firstCell = row.getFirstCellNum();
170 // 结束列数
171 int lastCell = row.getLastCellNum();
172 // 判断该行是否为空
173 String[] value = new String[lastCell];
174 if (firstCell != lastCell) {
175 for (int k = firstCell; k < lastCell; k++) {
176 // 获取一个单元格
177 HSSFCell cell = row.getCell(k);
178 value[k] = strFromCell(cell);
179 }
180 //是否是表头终止行
181 String checkStr = value[0]+value[1];
182 if("01".equals(checkStr)||"null1".equals(checkStr)) {
183 willExist= true;
184 }else{
185 tempExcels.add(value);
186 id = UUID.randomUUID().toString();
187 if(j==firstRow){//第一行是标题
188 table_name = strFromArr(value);
189 //删除数据库中相同的表
190 List<DzDynamicExcelSampleinfo> sampleinfos = ((DzDynamicExcelSampleinfoManager)getEntityManager()).deleteByTableName(table_name);
191 getEntityManager().delete(sampleinfos);
192 int count = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(table_name);
193 if(0!=count){
194 break;//删除失败
195 }
196 }else{
197 rulesData.add(value);//表头规则数据
198 entity = new DzDynamicExcel(dwmcid, create_time, table_id, table_name, isheader, logic_del,id,value,String.valueOf(seriesNumber++));
199 if(entity!=null){//导入成功
200 list.add(entity);
201 importRownum++;
202 }/*else {//导入失败
203 errorList.add(j+1);
204 failedRownum++;
205 }*/
206 }
207 }
208
209 }
210 if(willExist) break;
211 }
212
213 //先保存DzDynamicExcelSampleinfo,后保存DzDynamicExcel(因为数据库中有级联)
214 //一个工作薄一条记录一张表,保存多个工作薄
215 sampleInfo = new DzDynamicExcelSampleinfo( table_id,
216 dwmcid,
217 table_id,
218 table_name,
219 create_time,
220 create_person,
221 String.valueOf(tempExcels.size()==0?0:getValicColumnSum(tempExcels)), //有效列数
222 importRownum+""); //有效行数
223 ((DzDynamicExcelSampleinfoManager)getEntityManager()).save(sampleInfo);
224
225 //保存DzDynamicExcel
226 getdEntityManager().save(list);
227 //添加表头合并规则
228 int index = rulesData.size()-1;//2:例如0,1两行数据,
229 if (index>=0) {
230 List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
231 //表头规则倒序解析,并从倒数第二行开始(如果存在的话),并且每一个解析成功的对象都有一个排序字段(按照解析的次序)
232 for (int l =index-1 ; l >=0 ; l--) {
233 String[] row = (String[]) rulesData.get(l);
234 String[] last_row = null;//上一行数据
235
236 last_row = (String[]) rulesData.get(l+1);
237
238 List<DzDynamicRule> subRules = getMergeRuleToEntities(create_time, table_id,row,last_row);
239 if(subRules!=null){
240 rules.addAll(subRules);
241 }
242 }
243 if(rules!=null){
244 System.out.println(UtilJson.toJson(rules));
245 ((DzDynamicRuleManager)getrEntityManager()).save(rules);
246 }
247 int successRownum = importRownum==0?0:(importRownum+2);
248 map.put("totalRownum", (totalRownum+1)+"");
249 map.put("importRownum", successRownum+"");
250 map.put("failedRownum", (totalRownum+1-successRownum)+"");
251 map.put("errorList", Arrays.toString(errorList.toArray()));
252
253 maps.put("sheet"+i, map);
254 }else{
255 System.out.println("导入失败");;
256 }
257
258
259
260
261 }//--if
262
263 }
264
265
266 } catch (IOException e) {
267 e.printStackTrace();
268 }
269 return maps;
270 }
271
272 //将cell中不同类型的值全部转换为String类型
273 private String strFromCell(HSSFCell cell) {
274 String str = "";
275 if (cell != null) {
276 // 获取单元格，值的类型
277 int cellType = cell.getCellType();
278
279 if (cellType == 0) {
280 Object cellNumber = cell
281 .getNumericCellValue();
282 str = new DecimalFormat("#.##")
283 .format(cellNumber);
284 } else if (cellType == 1) {
285 str = cell.getStringCellValue() + "";
286 // }else if(cellType == 2){
287 } else if (cellType == 4) {
288 str = (cell.getBooleanCellValue()) + "";
289 } else {
290 str = "";
291 }
292 }
293 return str;
294 }
295 //将cell中不同类型的值全部转换为String类型
296 private String strFromXssfCell(XSSFCell cell) {
297 String str = "";
298 if (cell != null) {
299 // 获取单元格，值的类型
300 int cellType = cell.getCellType();
301
302 if (cellType == 0) {
303 Object cellNumber = cell
304 .getNumericCellValue();
305 str = new DecimalFormat("#.##")
306 .format(cellNumber);
307 } else if (cellType == 1) {
308 str = cell.getStringCellValue() + "";
309 // }else if(cellType == 2){
310 } else if (cellType == 4) {
311 str = (cell.getBooleanCellValue()) + "";
312 } else {
313 str = "";
314 }
315 }
316 return str;
317 }
318
319
320
321 /**
322 * 导入Excel 2007及以上版本.
323 */
324 public String importXlsx() throws Exception {
325 Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
326 // 获得前台上传的文件
327 Map<String, List<MultipartFile>> files = getUploadFiles();
328 MultipartFile file = files.get("fileselect").get(0);
329 // 格式校验
330 String name = file.getOriginalFilename();
331 if (!name.endsWith(".xlsx")) {
332 throw new RuntimeException("目前仅支持 xlsx 格式的文件");
333 }
334 // 导入数据
335 InputStream is = file.getInputStream();
336 XSSFWorkbook workbook = new XSSFWorkbook(is);
337 try {
338 maps = parseAndSaveExcel2007(workbook);
339 request.setAttribute("fileName",file.getOriginalFilename());
340 request.setAttribute("maps",maps);
341 } finally {
342 try {
343 workbook.close();
344 } catch (Exception ex) {
345 }
346 }
347 return "success";
348 }
349
350 private Map<String, Map<String, String>> parseAndSaveExcel2007(XSSFWorkbook workbook) {
351
352 Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
353 DzDynamicExcelSampleinfo sampleInfo = null;
354
355 int sheetNum = workbook.getNumberOfSheets();
356
357 //遍历sheet
358 for (int x = 0; x < sheetNum; x++) {
359
360 XSSFSheet sheet = workbook.getSheetAt(x);
361 int firstRow = sheet.getFirstRowNum();
362 int lastRow = sheet.getLastRowNum();
363 if(firstRow != lastRow) {
364
365 Map<String, String> map = new HashMap<String, String>();
366 int totalRownum = lastRow; //总行数
367 int importRownum = 0; //成功导入行数
368 // int failedRownum = 0; //失败行数
369 ArrayList<Integer> errorList = new ArrayList<Integer>();
370 // 获得表头固定数据
371 String dwmcid = LoginUserHolder.getLoginStationId();//登录这所在单位id,
372 //创建时间,一张表中,表头所包含的每一行的创建时间是相同的
373 String create_time = UtilDate.dateToString(Calendar.getInstance().getTime(),"yyy-MM-dd HH:mm:ss");
374 String table_id = UUID.randomUUID().toString();
375 String create_person = LoginUserHolder.getLoginUserId();//创建人id
376 String table_name = ""; //每张表的第一行内容为表名
377 String isheader = "1";
378 String logic_del = "0";
379 String id = ""; //UUID,每条数据的id,主键
380
381 //获取数据
382 List<DzDynamicExcel> list = new ArrayList<DzDynamicExcel>();
383 DzDynamicExcel entity = null;
384 boolean willExist = false;//如果解析到某一行是0,1,2,3...则说明表头到此为止.不用再解析了
385 Integer seriesNumber = 0; //需要,用于排序
386 List<String[]> tempExcels = new ArrayList<String[]>();
387 List<String[]> rulesData = new ArrayList<String[]>();
388 // 遍历行
389 for (int i = 0; i < lastRow; i++) {
390 XSSFRow row = sheet.getRow(i);
391 int firstCellNum = row.getFirstCellNum();
392 int lastCellNum = row.getLastCellNum();
393 if (firstCellNum == lastCellNum) break;
394
395 String[] value = new String[lastCellNum];
396 for (int j = firstCellNum; j <lastCellNum; j++) {//遍历列
397 XSSFCell cell = row.getCell(j);
398 value[j] = strFromXssfCell(cell);
399 }
400 //是否是表头终止行
401 String checkStr = value[0]+value[1];
402 if("01".equals(checkStr)||"null1".equals(checkStr)) {
403 willExist= true;
404 }else{
405 tempExcels.add(value);
406 id = UUID.randomUUID().toString();
407 if(i==firstRow){//第一行是标题
408 table_name = strFromArr(value);
409 //删除数据库中相同的表
410 List<DzDynamicExcelSampleinfo> sampleinfos = ((DzDynamicExcelSampleinfoManager)getEntityManager()).deleteByTableName(table_name);
411 getEntityManager().delete(sampleinfos);
412 int count = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(table_name);
413 if(0!=count){
414 break;//删除失败
415 }
416 }else{
417 rulesData.add(value);//表头规则数据
418 entity = new DzDynamicExcel(dwmcid, create_time, table_id, table_name, isheader, logic_del,id,value,String.valueOf(seriesNumber++));
419 if(entity!=null){//导入成功
420 list.add(entity);
421 importRownum++;
422 }/*else {//导入失败
423 errorList.add(j+1);
424 failedRownum++;
425 }*/
426 }
427 }
428 if(willExist) break;
429 }//遍历行结束
430
431
432 //先保存DzDynamicExcelSampleinfo,后保存DzDynamicExcel(因为数据库中有级联)
433 //一个工作薄一条记录一张表,保存多个工作薄
434 sampleInfo = new DzDynamicExcelSampleinfo( table_id,
435 dwmcid,
436 table_id,
437 table_name,
438 create_time,
439 create_person,
440 String.valueOf(tempExcels.size()==0?0:getValicColumnSum(tempExcels)), //有效列数
441 importRownum+""); //有效行数
442 ((DzDynamicExcelSampleinfoManager)getEntityManager()).save(sampleInfo);
443
444 //保存DzDynamicExcel
445 getdEntityManager().save(list);
446 //添加表头合并规则
447 int index = rulesData.size()-1;//2:0,1例如两行数据,
448 if (index>=0) {
449 List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
450 //表头规则倒序解析,并从倒数第二行开始(如果存在的话),并且每一个解析成功的对象都有一个排序字段(按照解析的次序)
451 for (int l =index-1 ; l >=0 ; l--) {
452 String[] row = (String[]) rulesData.get(l);
453 String[] last_row = null;//上一行数据
454
455 last_row = (String[]) rulesData.get(l+1);
456
457 List<DzDynamicRule> subRules = getMergeRuleToEntities(create_time, table_id,row,last_row);
458 if(subRules!=null){
459 rules.addAll(subRules);
460 }
461 }
462 if(rules!=null){
463 System.out.println(UtilJson.toJson(rules));
464 ((DzDynamicRuleManager)getrEntityManager()).save(rules);
465 }
466 int successRownum = importRownum==0?0:(importRownum+2);
467 map.put("totalRownum", (totalRownum+1)+"");
468 map.put("importRownum", successRownum+"");
469 map.put("failedRownum", (totalRownum+1-successRownum)+"");
470 map.put("errorList", Arrays.toString(errorList.toArray()));
471
472 maps.put("sheet"+x, map);
473 }else{
474 System.out.println("导入失败");;
475 }
476 }//--if(firstRow != lastRow) {
477
478 }//--for sheet end
479
480 return maps;
481 }
482
483
484 // 将数组转换为字符串
485 public static String strFromArr(String[] arr) {
486 String strs = "";
487 for (String str : arr) {
488 strs += (str == null ? "" : str);
489 }
490 return strs;
491 }
492
493 /**
494 * 处理多余的列,多余的null列不需要
495 * @param data 待处理的List
496 * @return 处理过的List
497 */
498 public Integer getValicColumnSum(List<String[]> data){
499 int len = data.size();
500 int maxIndex = 0;
501 if(data!=null){
502 for (int j = 0; j < len; j++) {//遍历每行数据
503 Object[] obj = data.get(j);
504 int maxLen = obj.length<100?obj.length:100;
505 for (int i = 0; i < maxLen; i++) { //这一行从后面第一个不为null的值的位置,
506 int currIndex = obj.length-i-1;
507 if(obj[currIndex]!=null&&!"".equals(obj[currIndex])){
508 if(maxIndex<currIndex){
509 maxIndex = currIndex;
510 }
511 int rowCol = data.get(j).length-1;
512 if(maxIndex<rowCol){
513 maxIndex= rowCol;
514 }
515 break;
516 }
517 }
518 }
519
520 }
521 return maxIndex+1;
522 }
523
524 /**
525 * 异步检查xlsx文件中的表是否已经存在
526 * @throws Exception
527 */
528 public void xlsxExist() throws Exception{
529 // 获得前台上传的文件
530 Map<String, List<MultipartFile>> files = getUploadFiles();
531 MultipartFile file = files.get("fileselect").get(0);
532
533 // 导入数据
534 InputStream is = file.getInputStream();
535 XSSFWorkbook workbook = new XSSFWorkbook(is);
536 Map<String, String> map = new HashMap<String, String>();
537 try {
538 map = checkXlsx(workbook);
539 String json = UtilJson.toJson(map);
540 PrintWriter out = response.getWriter();
541 out.print(json);
542 out.flush();
543 out.close();
544 } catch (IOException e) {
545 e.printStackTrace();
546 }finally {
547 try {
548 workbook.close();
549 } catch (Exception ex) {
550 }
551 }
552 }
553
554 private Map<String, String> checkXlsx(XSSFWorkbook workbook) {
555 Map<String, String> map = new HashMap<String,String>();
556 int sheetNum = workbook.getNumberOfSheets();
557 //遍历sheet
558 for (int x = 0; x < sheetNum; x++) {
559
560 XSSFSheet sheet = workbook.getSheetAt(x);
561 int firstRow = sheet.getFirstRowNum();
562 int lastRow = sheet.getLastRowNum();
563 if(firstRow == lastRow) break;
564
565 XSSFRow row = sheet.getRow(0);
566
567 int firstCellNum = row.getFirstCellNum();
568 int lastCellNum = row.getLastCellNum();
569 if (firstCellNum == lastCellNum) break;
570 String[] value = new String[lastCellNum];
571 if (firstCellNum != lastCellNum) {
572 for (int k = firstCellNum; k < lastCellNum; k++) {
573 // 获取一个单元格
574 XSSFCell cell = row.getCell(k);
575 String str = "";
576 if (cell != null) {
577 // 获取单元格，值的类型
578 int cellType = cell.getCellType();
579
580 if (cellType == 0) {
581 Object cellNumber = cell
582 .getNumericCellValue();
583 str = new DecimalFormat("#.##")
584 .format(cellNumber);
585 } else if (cellType == 1) {
586 str = cell.getStringCellValue() + "";
587 // }else if(cellType == 2){
588 } else if (cellType == 4) {
589 str = (cell.getBooleanCellValue()) + "";
590 } else {
591 str = "";
592 }
593 }
594 value[k] = str;
595 }
596
597 }
598 String tableName = strFromArr(value);
599 Integer num = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(tableName);
600 map.put(tableName,String.valueOf(num));
601 }
602 return map;
603 }
604
605 /**
606 * 异步检查Xls文件中的表是否已经给存在
607 * 传给前台的是json格式的map字符串,{表名:已经存在次数}
608 */
609 public void xlsExist() {
610
611 try {
612 request.setCharacterEncoding("UTF-8");
613 response.setCharacterEncoding("utf-8");
614 Map<String, String> maps = new HashMap<String, String>();
615 // 获得excel文件
616 // 解析器解析request的上下文
617 CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
618 request.getSession().getServletContext());
619 // 先判断request中是否包涵multipart类型的数据，
620 if (multipartResolver.isMultipart(request)) {
621 // 再将request中的数据转化成multipart类型的数据
622 MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
623 Iterator<String> iter = multiRequest.getFileNames();
624 while (iter.hasNext()) {
625 MultipartFile file = multiRequest.getFile(iter.next());
626 if (file != null) {
627 maps = checkXls(file);
628 }
629 }
630 }
631 String json = UtilJson.toJson(maps);
632 PrintWriter out = response.getWriter();
633 out.print(json);
634 out.flush();
635 out.close();
636 } catch (IOException e) {
637 e.printStackTrace();
638 }
639 }
640
641 private Map<String,String> checkXls(MultipartFile file) {
642 Map<String, String> map = new HashMap<String,String>();
643 try {
644 POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
645 // 新建WorkBook
646 HSSFWorkbook wb = new HSSFWorkbook(pois);
647 // 获取Sheet（工作薄）总个数
648 // int sheetNumber = wb.getNumberOfSheets();
649
650 // 获取Sheet（工作薄）
651 HSSFSheet sheet = wb.getSheetAt(0);
652 // 开始行数
653 int firstRow = sheet.getFirstRowNum();
654 // 结束行数
655 int lastRow = sheet.getLastRowNum();
656 // 判断该Sheet（工作薄)是否为空
657 boolean isEmpty = false;
658 if (firstRow == lastRow) {
659 isEmpty = true;
660 }
661
662 if (!isEmpty) {// 工作薄不为空,获取其中数据,并保存到数据库
663
664
665 // 获取一行
666 HSSFRow row = sheet.getRow(firstRow);
667 // 开始列数
668 int firstCell = row.getFirstCellNum();
669 // 结束列数
670 int lastCell = row.getLastCellNum();
671 // 判断该行是否为空
672 String[] value = new String[lastCell];
673 if (firstCell != lastCell) {
674 for (int k = firstCell; k < lastCell; k++) {
675 // 获取一个单元格
676 HSSFCell cell = row.getCell(k);
677 value[k] = strFromCell(cell);
678 }
679
680 }
681 String tableName = strFromArr(value);
682 Integer num = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(tableName);
683 map.put(tableName,String.valueOf(num));
684 }
685
686 } catch (IOException e) {
687 e.printStackTrace();
688 }
689 return map;
690 }
691
692
693 //根据表头的一行数据,得到合并规则
694 public List<DzDynamicRule> getMergeRuleToEntities(String createTime, String tableId,String[] row,String[] last_row){
695 List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
696 for (int i = 0; i < row.length; i++) {
697 String groupName = row[i];
698 if(!"".equals(groupName)){//空白单元格不处理
699 int field_count = 1;
700 for (int j = i+1; j < row.length; j++) {//判断当前单元格,后面的空白单元格数
701 if("".equals(row[j])){
702 field_count++;
703 }else{
704 break;
705 }
706 }
707 //完成一个单元格的判定了,并且这个单元格,是需要合并的
708 if(field_count>1){
709 String[] group = new String[]{groupName,"col_"+(i+1),field_count+""};
710 DzDynamicRule rule = new DzDynamicRule(createTime, UUID.randomUUID().toString(), tableId, sortNum++, group);
711 rules.add(rule);
712 i = i+field_count-1;
713 }else if(field_count==1&&last_row.length!=0&&i<last_row.length){//如果是一个单元格,需要判断上一行同列单元格是否为空?
714 if (!"".equals(last_row[i])) {//上行同列单元格为空,记录规则
715 String[] group = new String[]{groupName,"col_"+(i+1),"1"};
716 DzDynamicRule rule = new DzDynamicRule(createTime, UUID.randomUUID().toString(), tableId, sortNum++, group);
717 rules.add(rule);
718 i = i+field_count-1;
719 }
720 }
721 field_count = 1;
722 }
723 }
724 return rules;
725 }
726
727 //导出数据为Xlsx文件
728 public void exportXlsx(){
729
730 //1.将数据转换为excel
731 String tableId = request.getParameter("tableId");
732 System.out.println(tableId);
733 //获取表头,获取表头规则,获取数据
734 //表信息
735 List<Object[]> list = ((DzDynamicExcelSampleinfoManager) getEntityManager()).getSampleInfo(tableId);
736 Object[] sampleInfo = list.get(0);//表名,有效列数,有效行数
737 //获取表头,数据
738 List<Object[]> datas = ((DzDynamicExcelManager)getdEntityManager()).getData(tableId);
739 //获取表头规则
740 List<Object[]> rules = ((DzDynamicRuleManager)getrEntityManager()).getRules(tableId);
741
742 XSSFWorkbook workbook = new XSSFWorkbook();
743 XSSFSheet sheet = workbook.createSheet();
744
745 //row:表名
746 XSSFRow titlerow = sheet.createRow(0);
747 XSSFCell titlecell = titlerow.createCell(0,XSSFCell.CELL_TYPE_STRING);
748 titlecell.setCellValue(String.valueOf(sampleInfo[0]));//表名,表标题
749 //边框,样式
750 XSSFCellStyle style = getNewCenterStyle(workbook,Color.white,"title");
751 CellRangeAddress region = new CellRangeAddress(0,(short)0,0,Short.valueOf(sampleInfo[1]+"")-1);//设置合并的行列
752 setRegionStyle(sheet,region,style); //设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
753 sheet.addMergedRegion(region); //将单元格合并
754
755 //有效列数
756 int validColumn = Integer.valueOf(sampleInfo[1]+"");
757 int validRow = Integer.valueOf(sampleInfo[2]+"");
758 //row:表头,内容
759 for (int i = 0; i < datas.size(); i++) {
760 XSSFRow row = sheet.createRow(i+1);
761 Object[] rowdata = datas.get(i);
762 //表头
763 if (i+1<=validRow) {
764 style = getNewCenterStyle(workbook,Color.lightGray,"header");
765 for (int j = 0; j < validColumn; j++) {
766 XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
767 if(j>rowdata.length-1){
768 cell.setCellValue("");
769 continue;
770 }
771 cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
772 cell.setCellStyle(style);
773 }
774 //内容
775 }else{
776 style = getNewCenterStyle(workbook,new Color(214, 214, 214),"body");
777 for (int j = 0; j < validColumn; j++) {
778 XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
779 if(j>rowdata.length-1){
780 cell.setCellValue("");
781 continue;
782 }
783 cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
784 cell.setCellStyle(style);
785 }
786 }
787
788 }
789 //合并表头
790 sheet = mergeAllRange(sheet, validRow, validColumn);
791 //设置表头样式
792 style = getNewCenterStyle(workbook,Color.lightGray,"header");
793 setAllRangeStyle(sheet, style);
794 try
795 {
796 //保存excel到磁盘
797 @SuppressWarnings("deprecation")
798 String directory = request.getRealPath("/");
799 File file = new File(directory+"\\kaanalysis\\module\\dzdynamicexcelsampleinfo\\dzdynamic_export_excels\\"+sampleInfo[0]+".xlsx");
800 System.out.println(file.getAbsolutePath());
801 FileOutputStream fout = new FileOutputStream(file);
802 workbook.write(fout);
803 fout.close();
804
805 //下载excel
806 downLoadFile(response, file.getAbsolutePath(), "xlsx");
807 }catch (Exception e) {
808 e.printStackTrace();
809 }
810 }
811
812 /**
813 * 合并表头,并添加表头样式
814 * @param sheet
815 * @param validrow
816 * @param validcol
817 * @return
818 */
819 private static XSSFSheet mergeAllRange(XSSFSheet sheet,int validrow,int validcol){
820 XSSFCellStyle style = getNewCenterStyle(sheet.getWorkbook(),Color.lightGray,"header");
821 int countrow = 1;
822 int countcol = 1;
823 for (int row = 1; row <validrow; row++) {//eg:3,则1,2行需要合并,3行为最后一行表头,不需要合并
824 XSSFRow rowdata = sheet.getRow(row);
825 if (rowdata==null) {
826 continue;
827 }
828 for (int col = 0; col < validcol; col++) {
829 //当前单元格非空,
830 if (rowdata.getCell(col)!=null&&!"".equals(rowdata.getCell(col).getStringCellValue())) {
831 //计算空白列
832 for (int i = col+1; i < validcol; i++) {
833 if (rowdata.getCell(i)!=null&&!"".equals(rowdata.getCell(i).getStringCellValue())) {
834 break;
835 }
836 countcol++;
837 }
838 //计算空白行
839 for (int i = row+1; i <= validrow; i++) {
840 if(sheet.getRow(i).getCell(col)!=null&&!"".equals(sheet.getRow(i).getCell(col).getStringCellValue())){
841 break;
842 }
843
844 countrow++;
845 }
846 }
847 if (countcol!=1||countrow!=1) {
848 CellRangeAddress range = new CellRangeAddress(row,(short)(row+countrow-1),col,(short)(col+countcol-1));//设置合并的行列
849 setRegionStyle(sheet,range,style);//设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
850 sheet.addMergedRegion(range);//将单元格合并
851 //重新计数
852 countcol = 1;
853 countrow = 1;
854 }
855 }
856 }
857 return sheet;
858 }
859
860 /**
861 * 获取excel边框,样式
862 * @param workBook
863 * @param bgColor 背景色
864 * @param type 类型:title标题,Header表头,body内容
865 * @return
866 */
867
868 private static XSSFCellStyle getNewCenterStyle(XSSFWorkbook workBook,Color bgColor,String type){
869 XSSFCellStyle style = workBook.createCellStyle();;
870 style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
871 style.setWrapText(true);
872 style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
873 style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
874 style.setBorderRight(XSSFCellStyle.BORDER_THIN);
875 style.setBorderTop(XSSFCellStyle.BORDER_THIN);
876 style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
877 //字体
878 XSSFFont font = workBook.createFont();
879 if ("title".equals(type)) {
880 font.setFontName("黑体");
881 font.setColor(new XSSFColor(Color.green));//字体颜色
882 font.setFontHeight(60);
883 font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
884 font.setFontHeightInPoints((short) 22);
885 }else if ("header".equals(type)) {
886 font.setFontName("宋体");
887 font.setColor(new XSSFColor(Color.red));
888 font.setFontHeightInPoints((short) 12);
889 //设置单元格边框颜色
890 XSSFColor borderColor = new XSSFColor(Color.red);
891 style.setTopBorderColor(borderColor);
892 style.setBottomBorderColor(borderColor);
893 style.setLeftBorderColor(borderColor);
894 style.setRightBorderColor(borderColor);
895 //设置单元格背景色
896 style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
897 style.setFillBackgroundColor(new XSSFColor(bgColor));// 设置背景色,好像不起作用
898 style.setFillForegroundColor(new XSSFColor(bgColor));// 设置前景色
899 }else if ("body".equals(type)) {
900 font.setFontName("仿宋_GB2312");
901 font.setFontHeightInPoints((short) 10); //字体大小
902 }
903 style.setFont(font);
904 return style;
905 }
906
907 /**
908 * 设置合并区域样式
909 * 设置excel边框,样式
910 * @param sheet sheet
911 * @param range 合并区域
912 * @param cs 样式
913 */
914 private static void setRegionStyle(XSSFSheet sheet, CellRangeAddress range , XSSFCellStyle cs) {
915 for (int i = range.getFirstRow(); i <= range.getLastRow(); i ++) {
916 XSSFRow row = sheet.getRow(i);
917 if(range.getFirstColumn()!=range.getLastColumn()){
918 for (int j = range.getFirstColumn(); j <= range.getLastColumn(); j++) {
919 XSSFCell cell = row.getCell((short)j);
920 if( cell==null){
921 cell=row.createCell(j);
922 cell.setCellValue("");
923 }
924 cell.setCellStyle(cs);
925 }
926 }
927 }
928 }
929
930 /**
931 * 一次性设置所有合并区域,但不包括表题,其实只是表头
932 * @param sheet 工作薄
933 * @param style 样式
934 */
935 private void setAllRangeStyle(XSSFSheet sheet , XSSFCellStyle style){
936 int num = sheet.getNumMergedRegions();
937 for (int i = 1; i < num; i++) {
938 CellRangeAddress range = sheet.getMergedRegion(i);
939 setRegionStyle(sheet, range, style);
940 }
941 }
942
943 //excel 下载
944 public static boolean downLoadFile(HttpServletResponse response, String fileFullName, String fileType)
945 throws Exception {
946 File file = new File(fileFullName); //根据文件路径获得File文件
947 //设置文件类型(这样设置就不止是下Excel文件了，一举多得)
948 if("pdf".equals(fileType)){
949 response.setContentType("application/pdf;charset=GBK");
950 }else if("xls".equals(fileType)||"xlsx".equals(fileType)){
951 response.setContentType("application/msexcel;charset=GBK");
952 }else if("doc".equals(fileType)){
953 response.setContentType("application/msword;charset=GBK");
954 }
955
956 //文件名
957 response.setHeader("Content-Disposition", "attachment;filename=\""
958 + new String(fileFullName.substring(fileFullName.lastIndexOf("\\")).getBytes(), "ISO8859-1") + "\"");
959 response.setContentLength((int) file.length());
960 BufferedOutputStream output = null;
961 BufferedInputStream input = null;
962 try {
963 output = new BufferedOutputStream(response.getOutputStream());
964 InputStream fis = new BufferedInputStream(new FileInputStream(file));
965 byte[] buffer = new byte[fis.available()];
966 fis.read(buffer);
967 fis.close();
968 output.write(buffer);
969 output.flush(); //不可少
970 output.close();
971 response.flushBuffer();//不可少
972 } catch (Exception e) {
973 e.printStackTrace();
974 } finally {
975 //关闭流，不可少
976 if (input != null)
977 input.close();
978 if (output != null)
979 output.close();
980 }
981
982 return false;
983 }
984
985
986 }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
