注意,我将一些方法抽出来放到文章末尾了,这几个方法为:
desStyle,titleStyle,celStyle
功能一.Excel单页导出,即将所有的数据导入一个sheet页
导出Excel的主方法,所需参数只需要有request和response即可,另外两个是我为了查询写入Excel数据而加入的(一个是我的service,一个是我的实体类),使用时直接复制此方法即可。
另外,其中会有一些方法,诸如getFormatDate_ymd和toUTF8String等都是一些自己写的格式数据格式的方法,这些方法取决于你的数据是否需要格式化,这些都无伤大雅,这几个方法我也在下面列出来了。:
public static String meBespokeExportedEexecl(
TsinghuaMeBespoke tsinghuaMeBespoke,
HttpServletResponse response, HttpServletRequest request,
TsinghuaMeBespokeService tsinghuaMeBespokeService) throws Exception {
List<TsinghuaMeBespoke> tsinghuaMeBespokeList = tsinghuaMeBespokeService.findMyList(tsinghuaMeBespoke);
HSSFWorkbook workBook = new HSSFWorkbook();
ServletOutputStream out = null;
String[] titles = {"序号", "姓名","性别", "身份证号","预约状态", "预约日期", "预约时段","体检套餐","套餐金额","实付金额","交费时间"
,"学号","考试号","手机","院","系","生日"};
CellStyle desStyle = workBook.createCellStyle();
CellStyle titleStyle = workBook.createCellStyle();
CellStyle celStyle = workBook.createCellStyle();
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
CellStyle contentOddStyle = workBook.createCellStyle();
contentOddStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentOddStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// contentOddStyle.setWrapText(true);
// contentOddStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
contentOddStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
contentOddStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentOddStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentOddStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
contentOddStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
CellStyle contentEvenStyle = workBook.createCellStyle();
contentEvenStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentEvenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// contentEvenStyle.setWrapText(true);
// contentEvenStyle.setFillForegroundColor(HSSFColor.WHITE.index);
contentEvenStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
contentEvenStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentEvenStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentEvenStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
contentEvenStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFSheet sheet = workBook.createSheet("**体检人员预约信息表");
Row desRow = sheet.createRow(0);
// 合并单元格:参数:起始行, 终止行, 起始列, 终止列
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, (titles.length - 1));
sheet.addMergedRegion(cra);
//sheet.addMergedRegion(new Region(0, (short) 0, 0, titles.length > 0 ? (short) (titles.length - 1) : (short) 0));
Cell descell = desRow.createCell(0);
desRow.setHeight((short)900);
descell.setCellValue("清华体检人员预约信息表");
descell.setCellStyle(desStyle(workBook, desStyle));
try{
// 标题信息
Row titleRow = sheet.createRow(1);
for (int i = 0; i < titles.length; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(titleStyle(workBook, titleStyle));
titleRow.setHeight((short) 600);
sheet.setColumnWidth(i, 7200);
}
Cell cell = null;
for (int j = 0; j < tsinghuaMeBespokeList.size(); j++) {
TsinghuaMeBespoke m = tsinghuaMeBespokeList.get(j);
Row row = sheet.createRow(j + 2);
row.setHeight((short) 300);
//数据设置
//人名
String realnamed=m.getRealname();
String realname;
if(realnamed!=null && !"".equals(realnamed)){
realname=realnamed;
}else{
realname=" ";
}
//性别
String sex=m.getSex();
if("1".equals(sex)){
sex="男";
}else if("2".equals(sex)){
sex="女";
}else{
sex=" ";
}
//身份证号
String identityCardd= m.getIdentityCard();
String identityCard;
if(identityCardd!=null && !"".equals(identityCardd)){
identityCard=identityCardd;
}else{
identityCard="";
}
//预约状态
String bespokeStatus= m.getBespokeStatus();
if("0".equals(bespokeStatus)){
bespokeStatus="未预约";
}else if("1".equals(bespokeStatus)){
bespokeStatus="已预约";
}else if("2".equals(bespokeStatus)){
bespokeStatus="已报到";
}else{
bespokeStatus=" "; //未录入
}
//预约日期
Date bespokeDated= m.getBespokeDate();
String bespokeDate="";
if(bespokeDated!=null && !"".equals(bespokeDated)){
bespokeDate=MyDateUtils.getFormatDate_ymd(bespokeDated);
}
//预约时段 开始
String periodStartd=m.getPeriodStart();
String periodStart="";
if(periodStartd!=null && !"".equals(periodStartd)){
periodStart=periodStartd;
}
//预约时段 结束
String periodEndd=m.getPeriodEnd();
String periodEnd="";
if(periodEndd!=null && !"".equals(periodEndd)){
periodEnd=periodEndd;
}
//预约时段
String periodTime=periodStart+"-"+periodEnd;
//体检套餐
String feescaleNamed=m.getFeescaleName();
String feescaleName="";
if(feescaleNamed!=null && !"".equals(feescaleNamed)){
feescaleName=feescaleNamed;
}
//套餐金额
Double feescaleMoneyd=m.getFeescaleMoney();
String feescaleMoney="";
if(feescaleMoneyd!=null && !"".equals(feescaleNamed)){
feescaleMoney=feescaleMoneyd.toString();
}
//实付金额
Double paymentMoneyd=m.getPaymentMoney();
String paymentMoney="";
if(paymentMoneyd!=null && !"".equals(paymentMoneyd)){
paymentMoney=paymentMoneyd.toString();
}
//交费时间
Date paymentDated= m.getPaymentTime();
String paymentDate="";
if(paymentDated!=null && !"".equals(paymentDated)){
paymentDate=MyDateUtils.getFormatDate_ymd(paymentDated);
}
//学号
String studentIdd=m.getStudentId();
String studentId="";
if(studentIdd!=null && !"".equals(studentIdd)){
studentId=studentIdd;
}
//考试号 EXAMINE_NUMBER
String examineNumberd=m.getExamineNumber();
String examineNumber="";
if(examineNumberd!=null && !"".equals(examineNumberd)){
examineNumber=examineNumberd;
}
//手机
String sjd=m.getSj();
String sj="";
if(sjd!=null && !"".equals(sjd)){
sj=sjd;
}
//院
String departmentd=m.getDepartment();
String department="";
if(departmentd!=null && !"".equals(departmentd)){
department=departmentd;
}
//系
String facultyd=m.getFaculty();
String faculty="";
if(facultyd!=null && !"".equals(facultyd)){
faculty=facultyd;
}
//生日
Date birthDated= m.getBirthDate();
String birthDate="";
if(birthDated!=null && !"".equals(birthDated)){
birthDate=MyDateUtils.getFormatDate_ymd(birthDated);
}
String[] cellContents = {
String.valueOf(j + 1),
realname,
sex,
identityCard,
bespokeStatus,
bespokeDate,
periodTime,
feescaleName,
feescaleMoney,
paymentMoney,
paymentDate,
studentId,
examineNumber,
sj,
department,
faculty,
birthDate
};
for (int i = 0; i < cellContents.length; i++) {
String content = cellContents[i];
cell = row.createCell(i);
cell.setCellValue(content);
cell.setCellStyle(celStyle(workBook, celStyle));
// cell.setCellStyle(j % 2 != 0 ? contentOddStyle : contentEvenStyle);
}
}
// 文件名
StringBuffer fileName = new StringBuffer("**体检人员预约信息列表");
fileName.append(MyDateUtils.getFormatDate(new Date()));
fileName.append(".xls");
response.setHeader("Content-disposition", "attachment;filename=" + toUTF8String(fileName.toString()));
response.flushBuffer();
out = response.getOutputStream();
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
几个无伤大雅的格式化的方法:
public static String toUTF8String(String s) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < s.length(); i++) {
char c = s.charAt(i);
if (c >= 0 && c <= 255) {
sb.append(c);
} else {
byte[] b;
try {
b = Character.toString(c).getBytes(StandardCharsets.UTF_8);
} catch (Exception ex) {
System.out.println(ex);
b = new byte[0];
}
for (int j = 0; j < b.length; j++) {
int k = b[j];
if (k < 0)
k += 256;
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}
public static String getFormatDate_ymd(Date date){
String formatStr="";
if(date!=null &&!"".equals(date)){
formatStr = new SimpleDateFormat("yyyy-MM-dd").format(date);
}
return formatStr;
}
功能二.Excel导出数据为多个sheet页
之所以会导出为多个sheet页,是因为excel的单个sheet页的容量是有限的,比如:
在Excel 2003中,每个工作表有65535行和256列,而在Excel 2007以上版本中,每个工作表有1048576行和16384列。
其实很简单,我们只需要仔细调整一些循环条件即可,判断新建sheet页的情况就可以了,代码如下,我就不再多赘述了,和上面的对比一下就能了解:
/**
* 资费列表导出Excel
* @param tsinghuaMeBespoke
* @param response
* @param request
* @return
* @throws Exception
*/
public static String meFeescaleExportedEexecl(
TsinghuaMeBespoke tsinghuaMeBespoke,
HttpServletResponse response, HttpServletRequest request,
TsinghuaMeBespokeService tsinghuaMeBespokeService) throws Exception {
List<TsinghuaMeBespoke> tsinghuaMeBespokeList = tsinghuaMeBespokeService.findFeeacleList(tsinghuaMeBespoke);
HSSFWorkbook workBook = new HSSFWorkbook();
ServletOutputStream out = null;
String[] titles = {"序号", "姓名", "身份证号", "交费时间", "交费金额"};
CellStyle desStyle = workBook.createCellStyle();
CellStyle titleStyle = workBook.createCellStyle();
CellStyle celStyle = workBook.createCellStyle();
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
CellStyle contentOddStyle = workBook.createCellStyle();
contentOddStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentOddStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// contentOddStyle.setWrapText(true);
// contentOddStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
contentOddStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
contentOddStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentOddStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentOddStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
contentOddStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
CellStyle contentEvenStyle = workBook.createCellStyle();
contentEvenStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentEvenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// contentEvenStyle.setWrapText(true);
// contentEvenStyle.setFillForegroundColor(HSSFColor.WHITE.index);
contentEvenStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
contentEvenStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
contentEvenStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
contentEvenStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
contentEvenStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
long sheetMax=0l;
long rowEveP=3; //每页的数据条数
try{
for (int sheetI = 0; sheetI< tsinghuaMeBespokeList.size(); sheetI++){
if(sheetI%rowEveP==0){ //取余条件为每个分页的数据条数
HSSFSheet sheet = workBook.createSheet("清华体检人员交费信息表<"+sheetMax+">");
Row desRow = sheet.createRow(0);
// 合并单元格:参数:起始行, 终止行, 起始列, 终止列
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, (titles.length - 1));
sheet.addMergedRegion(cra);
//sheet.addMergedRegion(new Region(0, (short) 0, 0, titles.length > 0 ? (short) (titles.length - 1) : (short) 0));
Cell descell = desRow.createCell(0);
desRow.setHeight((short)900);
descell.setCellValue("**体检人员交费信息表<"+sheetMax+">");
descell.setCellStyle(desStyle(workBook, desStyle));
// 标题信息
Row titleRow = sheet.createRow(1);
for (int i = 0; i < titles.length; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(titleStyle(workBook, titleStyle));
titleRow.setHeight((short) 600);
sheet.setColumnWidth(i, 7200);
}
Cell cell = null;
for (int j = 0; j <rowEveP; j++) { // tsinghuaMeBespokeList.size() 此处设置每个分页多少条数据
//判断是否超出数组容量
if(sheetI+j<tsinghuaMeBespokeList.size()){
TsinghuaMeBespoke m = tsinghuaMeBespokeList.get(sheetI+j); //j 每个分页的每条数据
Row row = sheet.createRow(j + 2);
row.setHeight((short) 300);
//日期格式化
String paymentDate =MyDateUtils.getFormatDate_ymd(m.getPaymentTime());
String[] cellContents = {
String.valueOf(j + 1),
m.getRealname(),
m.getIdentityCard(),
paymentDate,
m.getPaymentMoney().toString()
};
for (int i = 0; i < cellContents.length; i++) {
String content = cellContents[i];
cell = row.createCell(i);
cell.setCellValue(content);
cell.setCellStyle(celStyle(workBook, celStyle));
// cell.setCellStyle(j % 2 != 0 ? contentOddStyle : contentEvenStyle);
}
}
}
sheetMax++;
}
}
/*原代码块位置*/
// 文件名
StringBuffer fileName = new StringBuffer("**体检人员交费信息列表");
fileName.append(MyDateUtils.getFormatDate(new Date()));
fileName.append(".xls");
response.setHeader("Content-disposition", "attachment;filename=" + toUTF8String(fileName.toString()));
response.flushBuffer();
out = response.getOutputStream();
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
大事儿,几个抽出来的设置样式的方法(要不然代码太多不怎么好看)
//设置
public static CellStyle desStyle(HSSFWorkbook wb, CellStyle ccellStyle) {
ccellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// ccellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
// style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
ccellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
ccellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// ccellStyle.setWrapText(true);
ccellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
ccellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
ccellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
ccellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
//font.setColor(HSSFColor.BLACK.index);
font.setFontHeight((short) 800);
font.setFontHeightInPoints((short) 16);// 设置字体大小
ccellStyle.setFont(font);
return ccellStyle;
}
public static CellStyle titleStyle(HSSFWorkbook wb, CellStyle cellStyle) {
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// cellStyle.setWrapText(true);
// cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// font.setColor(HSSFColor.BLUE.index);
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
// cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
// cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
// cellStyle.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
// cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
return cellStyle;
}
public static CellStyle celStyle(HSSFWorkbook wb, CellStyle ccellStyle) {
ccellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// ccellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
// style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
ccellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
ccellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// ccellStyle.setWrapText(true);
ccellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
ccellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
ccellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
ccellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
Font font = wb.createFont();
// font.setBoldweight(Font.BOLDWEIGHT_BOLD);
//font.setColor(HSSFColor.BLACK.index);
font.setFontHeight((short) 1000);
font.setFontHeightInPoints((short) 12);// 设置字体大小
ccellStyle.setFont(font);
return ccellStyle;
}