导入导出封装
//导出表头样式
public static WritableCellFormat HeadCss(HttpServletResponse response,HttpServletRequest request) throws Exception{
//表头样式
WritableFont wf_head = new WritableFont(WritableFont.createFont("黑体"),12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
WritableCellFormat wcf_head = new WritableCellFormat(wf_head);
wcf_head.setAlignment(jxl.format.Alignment.CENTRE);
wcf_head.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_head.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return wcf_head;
}
//表头样式
public static WritableCellFormat TitleCss(HttpServletResponse response,HttpServletRequest request) throws Exception{
WritableFont wf_title = new WritableFont(WritableFont.createFont("黑体"),12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
WritableCellFormat wcf_title = new WritableCellFormat(wf_title);
wcf_title.setAlignment(jxl.format.Alignment.CENTRE);
wcf_title.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return wcf_title;
}
//导出内容样式
public static WritableCellFormat TableCss(HttpServletResponse response,HttpServletRequest request) throws Exception{
//表头样式
WritableFont wf_table = new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcf_table = new WritableCellFormat(wf_table);
wcf_table.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
return wcf_table;
}
/**
* 导出模板
* @param response
* @param request
* @param columnnames 字段名称
* @param fieldnames 属性名称
* @param title 标题
* @param list 结果集
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response,HttpServletRequest request,
String[] columnnames,String[] fieldnames,String title,List<?> list) throws Exception{
WritableFont wf_head = new WritableFont(WritableFont.createFont("黑体"),12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
WritableCellFormat wcf_head = new WritableCellFormat(wf_head);
wcf_head.setAlignment(jxl.format.Alignment.CENTRE);
wcf_head.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_head.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
WritableFont wf_table = new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcf_table = new WritableCellFormat(wf_table);
wcf_table.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
// 取得输出流
OutputStream out = response.getOutputStream();
response.reset();
response.setHeader("Content-Disposition", "attachment;filename="+ new String((""+title+"").getBytes("gb2312"), "ISO8859-1") + ".xls");
response.setContentType("application/msexcel");
//生成EXCEL文件
WritableWorkbook workbook = Workbook.createWorkbook(out);
//创建EXCEL工作表
WritableSheet sheet = workbook.createSheet(""+title+"", 0);
//根据字段自适应宽度
// CellView cv = new CellView();
// cv.setSize(30);
//组装单元格
sheet.addCell(new Label(0, 0, ""+title+"", wcf_head));
for(int i=0;i<columnnames.length;i++){
sheet.setColumnView(i, 30);
sheet.addCell(new Label(i, 1, ""+columnnames[i]+"",wcf_head));
}
//合并标题单元格
sheet.mergeCells(0, 0, columnnames.length-1, 0);
//组装数据
//遍历结果集数
for (int i = 0; i < list.size(); i++){
Object obj = list.get(i);
//遍历字段数
for(int j=0;j<fieldnames.length;j++){
sheet.addCell(new Label(j, 2+i, String.valueOf(ReportUtil.getMethod(fieldnames[j], obj)),wcf_table));
}
}
//释放资源
ReportUtil.releaseCell(workbook, out);
}
//导出释放资源
public static void releaseCell(WritableWorkbook workbook,OutputStream out) throws IOException, WriteException{
workbook.write();
workbook.close();
out.flush();
out.close();
}
//根据对象属性获取get方法 返回object
public static Object getMethod(String fieldname,Object obj) throws Exception {
Class<?> clazz = obj.getClass();
Field field = clazz.getDeclaredField(fieldname);// 获得属性
PropertyDescriptor pd = new PropertyDescriptor(field.getName(),clazz);
Method getMethod = pd.getReadMethod();// 获得get方法
if (getMethod != null) {
Object o = getMethod.invoke(obj);//执行get方法返回一个Object
return o;
}
return null;
}
//根据对象属性获得set方法 set值
public static void setMethod(String fieldname,String columnvalue,Object obj) throws Exception {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Class<?> clazz = obj.getClass();
Field field = clazz.getDeclaredField(fieldname);// 获得属性
field.setAccessible(true);
if(field.getGenericType().toString().equals("class java.util.Date")){//日期类型
field.set(obj, df.parse(columnvalue));
}else if(field.getGenericType().toString().equals("class java.lang.Double")){//Double类型
field.set(obj, Double.valueOf(columnvalue));
}else if(field.getGenericType().toString().equals("class java.lang.Integer")||field.getType().toString().equals("int")){//整型
field.set(obj, Integer.valueOf(columnvalue));
}else{
field.set(obj, columnvalue);
}
}
/**
* 导入模板
* @param response
* @param request
* @param fieldnames 属性名称
* @param obj 对象名称
* @return
* @throws Exception
*/
// public static List<?> importExcel(HttpServletResponse response,HttpServletRequest request,
// String[] fieldnames,Object obj) throws Exception{
// List<Object> list = new ArrayList<Object>();
// response.setContentType("text/html;charset=utf-8");
//
// MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)request;
// MultipartFile multipartFile = multipartRequest.getFile("file");
// Workbook wb = Workbook.getWorkbook(multipartFile.getInputStream());
// Sheet sheet = wb.getSheet(0);
//
//
// for (int j = 1; j < sheet.getRows(); j++){
// for(int i = 0; i < fieldnames.length; i++){
// if(Utils.validateStringNull(sheet.getCell(j, i).getContents())){
// ReportUtil.setMethod(fieldnames[j],sheet.getCell(i, j).getContents(),obj);
// }
// }
//// list.add(obj);
//
// }
// wb.close();
// return list;
//
//
// }
@SuppressWarnings("unchecked")
public void importExcel(HttpServletResponse response,HttpServletRequest request,
String[] fieldnames,Object obj,Map<Integer,String> map,Object comm) throws Exception{
response.setContentType("text/html;charset=utf-8");
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)request;
MultipartFile multipartFile = multipartRequest.getFile("file");
Workbook wb = Workbook.getWorkbook(multipartFile.getInputStream());
Sheet sheet = wb.getSheet(0);
for (int j = 1; j < sheet.getRows(); j++){
for(int i = 0; i < fieldnames.length; i++){
if(Utils.validateStringNull(sheet.getCell(i, j).getContents())){
if(map.get(i)==null){
ReportUtil.setMethod(fieldnames[i],sheet.getCell(i, j).getContents(),obj);
}else{
//转换数值
Method m = comm.getClass().getDeclaredMethod(map.get(i), String.class);//获取方法
Map<String,String> comap = (Map<String,String>)m.invoke(comm, "caption");
ReportUtil.setMethod(fieldnames[i],comap.get(sheet.getCell(i, j).getContents()),obj);
}
}
}
hibernateTemplate.save(obj);
hibernateTemplate.flush();
}
wb.close();
}
public static WritableCellFormat HeadCss(HttpServletResponse response,HttpServletRequest request) throws Exception{
//表头样式
WritableFont wf_head = new WritableFont(WritableFont.createFont("黑体"),12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
WritableCellFormat wcf_head = new WritableCellFormat(wf_head);
wcf_head.setAlignment(jxl.format.Alignment.CENTRE);
wcf_head.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_head.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return wcf_head;
}
//表头样式
public static WritableCellFormat TitleCss(HttpServletResponse response,HttpServletRequest request) throws Exception{
WritableFont wf_title = new WritableFont(WritableFont.createFont("黑体"),12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
WritableCellFormat wcf_title = new WritableCellFormat(wf_title);
wcf_title.setAlignment(jxl.format.Alignment.CENTRE);
wcf_title.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
return wcf_title;
}
//导出内容样式
public static WritableCellFormat TableCss(HttpServletResponse response,HttpServletRequest request) throws Exception{
//表头样式
WritableFont wf_table = new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcf_table = new WritableCellFormat(wf_table);
wcf_table.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
return wcf_table;
}
/**
* 导出模板
* @param response
* @param request
* @param columnnames 字段名称
* @param fieldnames 属性名称
* @param title 标题
* @param list 结果集
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response,HttpServletRequest request,
String[] columnnames,String[] fieldnames,String title,List<?> list) throws Exception{
WritableFont wf_head = new WritableFont(WritableFont.createFont("黑体"),12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
WritableCellFormat wcf_head = new WritableCellFormat(wf_head);
wcf_head.setAlignment(jxl.format.Alignment.CENTRE);
wcf_head.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_head.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
WritableFont wf_table = new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat wcf_table = new WritableCellFormat(wf_table);
wcf_table.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
// 取得输出流
OutputStream out = response.getOutputStream();
response.reset();
response.setHeader("Content-Disposition", "attachment;filename="+ new String((""+title+"").getBytes("gb2312"), "ISO8859-1") + ".xls");
response.setContentType("application/msexcel");
//生成EXCEL文件
WritableWorkbook workbook = Workbook.createWorkbook(out);
//创建EXCEL工作表
WritableSheet sheet = workbook.createSheet(""+title+"", 0);
//根据字段自适应宽度
// CellView cv = new CellView();
// cv.setSize(30);
//组装单元格
sheet.addCell(new Label(0, 0, ""+title+"", wcf_head));
for(int i=0;i<columnnames.length;i++){
sheet.setColumnView(i, 30);
sheet.addCell(new Label(i, 1, ""+columnnames[i]+"",wcf_head));
}
//合并标题单元格
sheet.mergeCells(0, 0, columnnames.length-1, 0);
//组装数据
//遍历结果集数
for (int i = 0; i < list.size(); i++){
Object obj = list.get(i);
//遍历字段数
for(int j=0;j<fieldnames.length;j++){
sheet.addCell(new Label(j, 2+i, String.valueOf(ReportUtil.getMethod(fieldnames[j], obj)),wcf_table));
}
}
//释放资源
ReportUtil.releaseCell(workbook, out);
}
//导出释放资源
public static void releaseCell(WritableWorkbook workbook,OutputStream out) throws IOException, WriteException{
workbook.write();
workbook.close();
out.flush();
out.close();
}
//根据对象属性获取get方法 返回object
public static Object getMethod(String fieldname,Object obj) throws Exception {
Class<?> clazz = obj.getClass();
Field field = clazz.getDeclaredField(fieldname);// 获得属性
PropertyDescriptor pd = new PropertyDescriptor(field.getName(),clazz);
Method getMethod = pd.getReadMethod();// 获得get方法
if (getMethod != null) {
Object o = getMethod.invoke(obj);//执行get方法返回一个Object
return o;
}
return null;
}
//根据对象属性获得set方法 set值
public static void setMethod(String fieldname,String columnvalue,Object obj) throws Exception {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Class<?> clazz = obj.getClass();
Field field = clazz.getDeclaredField(fieldname);// 获得属性
field.setAccessible(true);
if(field.getGenericType().toString().equals("class java.util.Date")){//日期类型
field.set(obj, df.parse(columnvalue));
}else if(field.getGenericType().toString().equals("class java.lang.Double")){//Double类型
field.set(obj, Double.valueOf(columnvalue));
}else if(field.getGenericType().toString().equals("class java.lang.Integer")||field.getType().toString().equals("int")){//整型
field.set(obj, Integer.valueOf(columnvalue));
}else{
field.set(obj, columnvalue);
}
}
/**
* 导入模板
* @param response
* @param request
* @param fieldnames 属性名称
* @param obj 对象名称
* @return
* @throws Exception
*/
// public static List<?> importExcel(HttpServletResponse response,HttpServletRequest request,
// String[] fieldnames,Object obj) throws Exception{
// List<Object> list = new ArrayList<Object>();
// response.setContentType("text/html;charset=utf-8");
//
// MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)request;
// MultipartFile multipartFile = multipartRequest.getFile("file");
// Workbook wb = Workbook.getWorkbook(multipartFile.getInputStream());
// Sheet sheet = wb.getSheet(0);
//
//
// for (int j = 1; j < sheet.getRows(); j++){
// for(int i = 0; i < fieldnames.length; i++){
// if(Utils.validateStringNull(sheet.getCell(j, i).getContents())){
// ReportUtil.setMethod(fieldnames[j],sheet.getCell(i, j).getContents(),obj);
// }
// }
//// list.add(obj);
//
// }
// wb.close();
// return list;
//
//
// }
@SuppressWarnings("unchecked")
public void importExcel(HttpServletResponse response,HttpServletRequest request,
String[] fieldnames,Object obj,Map<Integer,String> map,Object comm) throws Exception{
response.setContentType("text/html;charset=utf-8");
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)request;
MultipartFile multipartFile = multipartRequest.getFile("file");
Workbook wb = Workbook.getWorkbook(multipartFile.getInputStream());
Sheet sheet = wb.getSheet(0);
for (int j = 1; j < sheet.getRows(); j++){
for(int i = 0; i < fieldnames.length; i++){
if(Utils.validateStringNull(sheet.getCell(i, j).getContents())){
if(map.get(i)==null){
ReportUtil.setMethod(fieldnames[i],sheet.getCell(i, j).getContents(),obj);
}else{
//转换数值
Method m = comm.getClass().getDeclaredMethod(map.get(i), String.class);//获取方法
Map<String,String> comap = (Map<String,String>)m.invoke(comm, "caption");
ReportUtil.setMethod(fieldnames[i],comap.get(sheet.getCell(i, j).getContents()),obj);
}
}
}
hibernateTemplate.save(obj);
hibernateTemplate.flush();
}
wb.close();
}
