导入导出excel

import java.io.InputStream;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;/** * Excel工具类 * @author Administrator * */public class ExcelUtil{/**  * 导出报表Excel的方法  *   * @param title  *            excel中的sheet名称  * @param headers  *            表头  * @param result  *            结果集  * @param out  *            输出流  * @param pattern  *            时间格式  * @throws Exception  */  @SuppressWarnings("deprecation")public void exportoExcel(String title, String[] headers, String[] columns, List<HashMap<String, Object>> result, OutputStream out,String pattern) throws Exception {      // 声明一个工作薄      @SuppressWarnings("resource")HSSFWorkbook workbook = new HSSFWorkbook();      // 生成一个表格      HSSFSheet sheet = workbook.createSheet(title);      // 设置表格默认列宽度为20个字节      sheet.setDefaultColumnWidth((short) 20);      // 生成一个样式      HSSFCellStyle style = workbook.createCellStyle();      // 设置单元格背景色，设置单元格背景色以下两句必须同时设置      style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置填充色      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式          // 设置单元格上、下、左、右的边框线      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);     style.setBorderLeft(HSSFCellStyle.BORDER_THIN);      style.setBorderRight(HSSFCellStyle.BORDER_THIN);      style.setBorderTop(HSSFCellStyle.BORDER_THIN);      // 设置居中      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);      // 生成一个字体      HSSFFont font = workbook.createFont();      //font.setColor(HSSFColor.VIOLET.index);      // font.setFontHeightInPoints((short) 12);      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);      // 把字体应用到当前的样式      style.setFont(font);      // 指定当单元格内容显示不下时自动换行      style.setWrapText(true);      // 声明一个画图的顶级管理器      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();      // 产生表格标题行      // 表头的样式      HSSFCellStyle titleStyle = workbook.createCellStyle();// 创建样式对象      titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中      titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中      // 设置字体      HSSFFont titleFont = workbook.createFont(); // 创建字体对象      titleFont.setFontHeightInPoints((short) 15); // 设置字体大小      titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体      // titleFont.setFontName("黑体"); // 设置为黑体字      titleStyle.setFont(titleFont);          sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.length - 1)));// 指定合并区域      HSSFRow rowHeader = sheet.createRow(0);      HSSFCell cellHeader = rowHeader.createCell((short) 0); // 只能往第一格子写数据，然后应用样式，就可以水平垂直居中      HSSFRichTextString textHeader = new HSSFRichTextString(title);      cellHeader.setCellStyle(titleStyle);      cellHeader.setCellValue(textHeader);      HSSFRow row = sheet.createRow(1);      for (int i = 0; i < headers.length; i++) {          HSSFCell cell = row.createCell((short) i);          cell.setCellStyle(style);          HSSFRichTextString text = new HSSFRichTextString(headers[i]);          cell.setCellValue(text);      }      // 遍历集合数据，产生数据行      if (result != null) {          int index = 2;          for (HashMap<String, Object> map : result) {              // Field[] fields = t.getClass().getDeclaredFields();              row = sheet.createRow(index);              index++;              for (short i = 0; i < columns.length; i++) {                  HSSFCell cell = row.createCell(i);                   String fieldName = columns[i];                  Object value = map.get(fieldName);                String textValue = null;                  if (value == null) {                      textValue = "";                  } else if (value instanceof Date) {                      Date date = (Date) value;                      SimpleDateFormat sdf = new SimpleDateFormat(pattern);                      textValue = sdf.format(date);                  } else if (value instanceof byte[]) {                      // 有图片时，设置行高为60px;                      row.setHeightInPoints(60);                      // 设置图片所在列宽度为80px,注意这里单位的一个换算                      sheet.setColumnWidth(i, (short) (35.7 * 80));                      // sheet.autoSizeColumn(i);                      byte[] bsValue = (byte[]) value;                      HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,                              index);                      anchor.setAnchorType(2);                      patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));                  } else {                      // 其它数据类型都当作字符串简单处理                      textValue = value.toString();                  }                  if (textValue != null) {                      Pattern p = Pattern.compile("^//d+(//.//d+)?$");                      Matcher matcher = p.matcher(textValue);                      if (matcher.matches()) {                          // 是数字当作double处理                          cell.setCellValue(Double.parseDouble(textValue));                      } else {                          HSSFRichTextString richString = new HSSFRichTextString(textValue);                         cell.setCellValue(richString);                      }                  }                  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中                  cell.setCellStyle(style);              }          }      }      workbook.write(out);      out.flush();      out.close();  } /** * 导入excel * @param in  输入流 * @param startRow  从N行开始导入，第0行开始 * @param columns   字段名（输出map的key值） * @return */@SuppressWarnings({ "deprecation", "resource" })public List<HashMap<String,Object>> readExcelContent(InputStream in,int startRow,String[] columns){List<HashMap<String,Object>> content = new ArrayList<HashMap<String,Object>>();try {  POIFSFileSystem fs = new POIFSFileSystem(in);  Workbook wb = new HSSFWorkbook(fs);              HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);              // 得到总行数              int rowNum = sheet.getLastRowNum()+1;              // 正文内容应该从第二行开始,第一行为表头的标题              HashMap<String,Object> map = null;            startRow = startRow<1?1:startRow;            HSSFRow row = sheet.getRow(startRow);              int colNum = row.getPhysicalNumberOfCells();            for (int i = startRow; i < rowNum; i++) {                  row = sheet.getRow(i);                  if(row==null){                      break;                  }                  map = new HashMap<String, Object>();                for (int j = 0; j < colNum; j++) {                  String keyName = columns[j];                  Object value = "";                    HSSFCell cell = row.getCell(j);                      if(cell != null) {                          switch (cell.getCellType()) {                               case HSSFCell.CELL_TYPE_NUMERIC: // 数字                                                           if (HSSFDateUtil.isCellDateFormatted(cell)) {                                       //如果是date类型则 ，获取该cell的date值                                   value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();                                   } else { // 纯数字                                       double d = cell.getNumericCellValue();                                  if (d - (int) d < Double.MIN_VALUE) {                                   // 是否为int型                                  value = Integer.toString((int) d);                                  } else {                                    // 是否为double型                                  value = Double.toString(cell.getNumericCellValue());                                  }                              }                              break;                               case HSSFCell.CELL_TYPE_STRING: // 字符串                               value = cell.getStringCellValue() + "";                                   break;                               case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean                               value = cell.getBooleanCellValue() + "";                                   break;                               case HSSFCell.CELL_TYPE_FORMULA: // 公式                               value = cell.getCellFormula() + "";                                   break;                               case HSSFCell.CELL_TYPE_BLANK: // 空值                               value = "";                                   break;                               case HSSFCell.CELL_TYPE_ERROR: // 故障                               value = "";                                   break;                               default:                               value = "";                                   break;                          }                      } else {                      value = "";                      }                      map.put(keyName, value);                  }                  content.add(map);              }          } catch (Exception e) {              e.printStackTrace();          }  return content;}} 

//  导出excel Demo@RequestMapping("exportExcel")public void exportExcel(HttpServletResponse response){String title = "角色报表信息";String pattern = "yyyy-MM-dd HH:mm:ss";String[] headers = {"id","名称","上级Id","创建时间","修改时间","创建人","修改人"};String[] columns = {"ROLE_ID","ROLE_NAME","PARENT_ID","CREATE_DATE","UPDATE_DATE","CREATE_USERNAM","UPDATE_USERNAME"};List<HashMap<String, Object>> result= manageService.queryRoles();try {response .setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xls").getBytes(), "iso-8859-1"));          OutputStream out;out = response.getOutputStream();new ExcelUtil().exportoExcel(title, headers, columns, result, out, pattern);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}  }//  导入excel Demo@RequestMapping("importExcel")public List<HashMap<String,Object>> importExcel(@RequestParam("file") MultipartFile  file){List<HashMap<String, Object>> list = null;try {InputStream inputStream = file.getInputStream();String[] columns = {"ROLE_ID","ROLE_NAME","PARENT_ID","CREATE_DATE","UPDATE_DATE","CREATE_USERNAM","UPDATE_USERNAME"};list= new ExcelUtil().readExcelContent(inputStream, 2, columns);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}return list;  }