需要注意的地方:
1.js构造表单并提交
2.js中文传参encodeURI(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.name,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 }
扫一扫
2692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
