* 考试科目导入模板
* @param request
* @param response
*/
@RequestMapping(value="/downloadKskm",method=RequestMethod.POST)
public void downloadKskm(HttpServletRequest request,HttpServletResponse response){
//考试科目导入模板
String[][] content = new String[3][];
content[0] = new String[] { "考试名称","考试科目"};
content[1] = new String[] { "考试1","语文"};
content[2] = new String[] { "考试1","数学"};
Util util = new Util();
OutputStream os = null;
try {
response.setContentType("application/vnd.ms-excel; charset=iso-8859-1");
response.setHeader("Content-Disposition","attachment;filename="+new String("考试科目导入模板".getBytes("GBK"),"ISO-8859-1")+".xls");
response.setCharacterEncoding("iso-8859-1");
os=response.getOutputStream();
util.export("考试科目导入模板", null, content, null, os, null, "0,1",0);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
工具类
package com.lanou.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 生成excel表格
*
* @author
*
*/
public class Util {
/**
* 构造器
*
*/
public Util() {
}
/**
* 生成具有一定格式excel
*
* @param sheetName
* sheet名称,默认为sheet1
* @param nf
* 数字类型的格式 如:jxl.write.NumberFormat nf = new
* jxl.write.NumberFormat("#.##");默认无格式
* @param content
* 二维数组,要生成excel的数据来源
* @param 合并项
* 每一项的数据格式为0,1,0,2 即:把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
* @param os
* excel输出流
* @param row
* 需要水平居中的行,默认居左。以逗号分隔的字符串
* @param col
* 需要水平居中的列,默认居左。以逗号分隔的字符串
*/
public void export(String sheetName, NumberFormat nf, String[][] content,
String[] mergeInfo, OutputStream os, String row, String col,int q) {
WritableFont wf2=new WritableFont(WritableFont.createFont("宋体"),13,WritableFont.NO_BOLD, false); // 定义格式 字体 下划线 斜体 粗体 颜色
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
Set<Integer> rows = this.getInfo(row);
Set<Integer> cols = this.getInfo(col);
WritableWorkbook workbook = null;
try {
System.out.println(os);
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat(wf2);
if (rows.contains(i) || cols.contains(j)) {
format.setAlignment(jxl.format.Alignment.CENTRE);
} else {
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = null;
format.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
format.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.BLACK);
label=new Label(j, i, content[i][j], format);
for(int a=0;a<content.length;a++) {
sheet.setRowView(a,400, false); //设置行高
}
String[] a=content[q];
int e=0;
for(int c=0;c<a.length;c++) {
String d=a[c];
if(isNum(d)) {
e=(d.getBytes().length)*6+6;
}
e=d.getBytes().length+6;
sheet.setColumnView(c, e); //设置col显示样式
}
sheet.addCell(label);
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName
* sheet名称,默认为sheet1
* @param content
* 二维数组,要生成excel的数据来源
* @param os
* excel输出流
*/
public void exportFormatExcel(String[][] content, String sheetName,
OutputStream os) {
if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j], format);
sheet.addCell(label);
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName
* sheet名称,默认为sheet1
* @param content
* Map,要生成excel的数据来源
* @param os
* excel输出流
*/
public void exportFormatExcel(Map<String, String[][]> content,
String[] salary_name_array, String sheetName, OutputStream os)
{
if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
int index = 0;
for (int k = 0; k < salary_name_array.length; k++) {
String[][] value = (String[][]) content
.get(salary_name_array[k]);
if (value != null && value.length > 0) {
if (index != 0) {
index++;
}
WritableCellFormat format1 = new WritableCellFormat();
format1.setAlignment(jxl.format.Alignment.LEFT);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label1 = new Label(0, index, salary_name_array[k],
format1);
sheet.addCell(label1);
for (int i = 0; i < value.length; i++) {
index++;
for (int j = 0; j < value[i].length; j++) {
if (value[i][j] == null) {
value[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, index, value[i][j],
format);
sheet.addCell(label);
}
}
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 合并表格
* @param sheet
* 工作表
* @param mergeInfo
* 要合并的表格的信息
* @throws RowsExceededException
* @throws NumberFormatException
* @throws WriteException
*/
private void merge(WritableSheet sheet, String[] mergeInfo)
throws RowsExceededException, NumberFormatException, WriteException {
if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {
return;
} else if (!this.isMergeInfo(mergeInfo)) {
return;
} else {
for (String str : mergeInfo) {
String[] temp = str.split(",");
sheet.mergeCells(Integer.parseInt(temp[1]),
Integer.parseInt(temp[0]), Integer.parseInt(temp[3]),
Integer.parseInt(temp[2]));
}
}
}
/**
* 处理要居中的行或列的数据
*
* @param indexes
* 行标或列标
* @return 行坐标或列坐标组成的集合
*/
private Set<Integer> getInfo(String indexes) {
Set<Integer> set = new HashSet<Integer>();
if (VerifyUtil.isNullObject(indexes)) {
return set;
}
String[] temp = indexes.split(",", 0);
for (String str : temp) {
if (isNumeric(str)) {
set.add(Integer.parseInt(str));
}
}
return set;
}
/**
* 判断字符串是否由纯数字组成
*
* @param str
* 源字符串
* @return true是,false否
*/
private boolean isNumeric(String str) {
if (VerifyUtil.isNullObject(str)) {
return false;
}
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();
}
/**
* 判断字符串是否是数字
*
* @param str
* 源字符串
* @return true是,false否
*/
private boolean isNumber(String number) {
// 判断参数
if (VerifyUtil.isNullObject(number)) {
return false;
}
// 查看是否有小数点
int index = number.indexOf(".");
if (index < 0) {
return isNumeric(number);
} else {
// 如果有多个".",则不是数字
if (number.indexOf(".") != number.lastIndexOf(".")) {
return false;
}
String num1 = number.substring(0, index);
String num2 = number.substring(index + 1);
return isNumeric(num1) && isNumeric(num2);
}
}
/**
* 判断合并项内容是否合法
*
* @param mergeInfo
* 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
* @return true合法,false非法
*/
private boolean isMergeInfo(String[] mergeInfo) {
if (VerifyUtil.isNull1DArray(mergeInfo)) {
return false;
} else {
for (String str : mergeInfo) {
String[] temp = str.split(",");
if (VerifyUtil.isNull1DArray(temp) || temp.length != 4) {
return false;
} else {
for (String s : temp) {
if (!isNumeric(s)) {
return false;
}
}
}
}
}
return true;
}
public static void main(String[] args) {
Util ee = new Util();
String[][] content = new String[][] { { "学生信息表", "学籍号", "学生考号", "学生姓名","班级名称","学校名称" },
{ "第一行", "111", "111", "刘磊","七一","zz" }, { "第二行", "111", "111", "刘磊","七一","zz" },
{ "第三行", "111", "111", "刘磊","七一","zz" } };
try {
OutputStream os = new FileOutputStream("E:/test2.xls");
// ee.export(null,null, content,null, os);
ee.export(null, null, content,
null, os, null, "0,1,2,3,4,5",0);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void exports(String sheetName, NumberFormat nf, String[][] content,
String[] mergeInfo, File os, String row, String col,int q) {
WritableFont wf2=new WritableFont(WritableFont.createFont("宋体"),13,WritableFont.NO_BOLD, false); // 定义格式 字体 下划线 斜体 粗体 颜色
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
Set<Integer> rows = this.getInfo(row);
Set<Integer> cols = this.getInfo(col);
WritableWorkbook workbook = null;
try {
System.out.println(os);
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat(wf2);
if (rows.contains(i) || cols.contains(j)) {
format.setAlignment(jxl.format.Alignment.CENTRE);
} else {
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = null;
format.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
format.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.BLACK);
label=new Label(j, i, content[i][j], format);
for(int a=0;a<content.length;a++) {
sheet.setRowView(a,400, false); //设置行高
}
String[] a=content[q];
int e=0;
for(int c=0;c<a.length;c++) {
String d=a[c];
if(isNum(d)) {
e=(d.getBytes().length)*6+6;
}
e=d.getBytes().length+6;
sheet.setColumnView(c, e); //设置col显示样式
}
sheet.addCell(label);
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static boolean isNum(String str){
return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
}
public void exportd(String sheetName, NumberFormat nf, String[][] content,
String[] mergeInfo, File os, String row, String col) {
WritableFont wf2=new WritableFont(WritableFont.createFont("宋体"),13,WritableFont.NO_BOLD, false); // 定义格式 字体 下划线 斜体 粗体 颜色
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
Set<Integer> rows = this.getInfo(row);
Set<Integer> cols = this.getInfo(col);
WritableWorkbook workbook = null;
try {
System.out.println(os);
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat(wf2);
if (rows.contains(i) || cols.contains(j)) {
format.setAlignment(jxl.format.Alignment.CENTRE);
} else {
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = null;
format.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
format.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.BLACK);
label=new Label(j, i, content[i][j], format);
for(int a=0;a<content.length;a++) {
sheet.setRowView(a,400, false); //设置行高
int e=6;
sheet.setColumnView(a, e); //设置col显示样式
}
sheet.addCell(label);
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}