Java excel导入导出

excel导出

 

public class Writer {
    
      public static String setFileDownloadHeader(HttpServletRequest request, String fileName) {
          final String userAgent = request.getHeader("USER-AGENT");
          String finalFileName = null;
          try {
            
              if(StringUtils.contains(userAgent, "MSIE")){//IE浏览器
                  finalFileName = URLEncoder.encode(fileName,"UTF8");
              }else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
                  finalFileName = new String(fileName.getBytes(), "ISO8859-1");
              }else{
                  finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
              }
            
          } catch (UnsupportedEncodingException e) {
          }
          return finalFileName;
      }

}

 

 

public void selectAll(HttpServletRequest request,
   HttpServletResponse response) {
  // excel数据导出
  System.out.println("excel数据导出");
  // 创建一个工作簿
  HSSFWorkbook workBook = new HSSFWorkbook();

  // 创建一个工作表，名为：第一页
  HSSFSheet sheet = workBook.createSheet("sheet1");

  // 设置单元格的宽度(0:表示第一行的第一个单元格，1：第一行的第二个单元格)
  sheet.setColumnWidth((short) 0, 2500);
  sheet.setColumnWidth((short) 1, 5000);
  sheet.setColumnWidth((short) 2, 5000);

  // 创建一个单元格，从0开始
  HSSFRow row = sheet.createRow((short) 0);

  // 构造一个数组设置第一行之后的单元格
  HSSFCell[] cell = new HSSFCell[1];

  for (int i = 0; i < cell.length; i++) {
   cell[i] = row.createCell(i);
  }
  cell[0].setCellValue("证件号");
  // cell[1].setCellValue("姓名");
  // cell[2].setCellValue("单位");

  // 获得从数据库中查询出来的数据
  List<BlackListItem> list = blackListDao.selectAll();

  // 循环list中的数据
  for (int i = 0; i < list.size(); i++) {

   BlackListItem blackListItem = list.get(i);
   HSSFRow dataRow = sheet.createRow(i + 1);
   // 創建2個單元格
   HSSFCell[] data = new HSSFCell[1];

   for (int j = 0; j < data.length; j++) {
    data[j] = dataRow.createCell(j);
   }
   data[0].setCellValue(blackListItem.getPassId());
   // data[1].setCellValue(blackListItem.getName());
   // data[2].setCellValue(blackListItem.getCompanyName());
  }
  try {
   // 设置reponse参数
   String fileName = Writer
     .setFileDownloadHeader(request, "人员信息表.xls");
   //
   response.setHeader("Content-Disposition", "inline; filename="
     + fileName);

   // 确保发送的当前文本格式
   response.setContentType("application/vnd.ms-excel");

   ServletOutputStream outputStream = response.getOutputStream();
   // Write to the output stream
   workBook.write(outputStream);
   // 清除缓存
   outputStream.flush();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }

 

 

excel导入

 

public List<String> addBatchBlackList(MultipartFile file,
   HttpServletRequest request) {
  System.out.println("開始導入");
  /**
   * 用来记录添加数据库中不存在的人员的信息
   */
  ArrayList<String> pssidList = new ArrayList<String>();
  Workbook workbook = null;
  try {
   if (file.getOriginalFilename().toLowerCase().endsWith("xls")) {
    workbook = new HSSFWorkbook(file.getInputStream());
   } else if (file.getOriginalFilename().toLowerCase()
     .endsWith("xlsx")) {
    workbook = new XSSFWorkbook(file.getInputStream());
   } else {
    return null;
   }
  } catch (Exception e) {
   e.printStackTrace();
  }

  List<BlackListItem> list = new ArrayList<BlackListItem>();
  BlackListItem ppc = null;

  // Read the Sheet
  for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
   Sheet sheet = workbook.getSheetAt(numSheet);
   if (sheet == null) {
    continue;
   }
   // 读取excel
   for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
    Row row = sheet.getRow(rowNum);
    if (row != null) {
     ppc = new BlackListItem();
     Cell PASSID = row.getCell(0);
     String passId = getValue(PASSID);
     ppc.setPassId(passId);
     System.out.println(ppc.getPassId());
     list.add(ppc);
    }
   }
  }
  for (BlackListItem item : list) {
     pssidList.add(item.getPassId());

  }

  return pssidList;
 }

 

private String getValue(Cell row) {
  if (row.getCellType() == row.CELL_TYPE_BOOLEAN) {
   return String.valueOf(row.getBooleanCellValue());
  } else if (row.getCellType() == row.CELL_TYPE_NUMERIC) {
   String str = String.valueOf(row.getNumericCellValue());
   String[] abc = str.split("\\.");
   return abc[0];
  } else {
   return String.valueOf(row.getStringCellValue());
  }
 }