Analysis of Java Importing and Exporting Excel with POI

1. Exception java. lang. NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException

Solution:

Use the poi related jar package 1 version 1 must be the same! ! ! ! !

2. The jar package used by maven. If maven is not used, just use poi-3. 9. jar and poi-ooxml-3. 9. jar (this is mainly used for Excel after 2007). ()

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

3. Introducing java into Excel

Upload Excel first

// Upload Excel
    @RequestMapping("/uploadExcel")    public boolean uploadExcel(@RequestParam MultipartFile file,HttpServletRequest request) throws IOException {        if(!file.isEmpty()){
            String filePath = file.getOriginalFilename();            //windows
            String savePath = request.getSession().getServletContext().getRealPath(filePath);            //linux            //String savePath = "/home/odcuser/webapps/file";
            File targetFile = new File(savePath);            if(!targetFile.exists()){
                targetFile.mkdirs();
            }

            file.transferTo(targetFile);            return true;
        }        return false;
    }

Reading the contents of Excel

 public static void readExcel() throws Exception{


        InputStream is = new FileInputStream(new File(fileName));
        Workbook hssfWorkbook = null;        if (fileName.endsWith("xlsx")){
           hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
        }else if (fileName.endsWith("xls")){
            hssfWorkbook = new HSSFWorkbook(is);//Excel 2003
        }       // HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);       // XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
        User student = null;
        List<User> list = new ArrayList<User>();        //  Circulating worksheet Sheet
        for (int numSheet = 0; numSheet <hssfWorkbook.getNumberOfSheets(); numSheet++) {            //HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);            if (hssfSheet == null) {                continue;
            }            //  Circular row Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {                //HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                Row hssfRow = hssfSheet.getRow(rowNum);                if (hssfRow != null) {
                    student = new User();                    //HSSFCell name = hssfRow.getCell(0);                    //HSSFCell pwd = hssfRow.getCell(1);
                    Cell name = hssfRow.getCell(0);
                    Cell pwd = hssfRow.getCell(1);// Here is your own logic                     student.setUserName(name.toString());
                    student.setPassword(pwd.toString());

                    list.add(student);
                }
            }
        }


    }

4. Export Excel

// Create Excel
    @RequestMapping("/createExcel")    public String createExcel(HttpServletResponse response) throws IOException {        // Create HSSFWorkbook Object (excel Document object of )
        HSSFWorkbook wb = new HSSFWorkbook();// Create a new sheet Object ( excel Form of) 
        HSSFSheet sheet=wb.createSheet(" Achievement sheet ");// In sheet Create the number in the 1 Row, with row index as the parameter (excel The line of ) , can be 0 ~ 65535 Between any 1 A 
        HSSFRow row1=sheet.createRow(0);// Create a cell ( excel The parameter is the column index, which can be the cell of 0 ~ 255 Between any 1 A 
        HSSFCell cell=row1.createCell(0);        // Set cell contents 
        cell.setCellValue(" Student test scores 1 Table ");// Merge cells CellRangeAddress The construction parameters represent the starting row, the ending row, the starting column,   Up to column 
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));// In sheet Create the number in the 2 Row 
        HSSFRow row2=sheet.createRow(1);        // Create cells and set cell contents 
        row2.createCell(0).setCellValue(" Name ");
        row2.createCell(1).setCellValue(" Class ");
        row2.createCell(2).setCellValue(" Written test result ");
        row2.createCell(3).setCellValue(" Computer test results ");        // In sheet Create the number in the 3 Row 
        HSSFRow row3=sheet.createRow(2);
        row3.createCell(0).setCellValue(" Li Ming ");
        row3.createCell(1).setCellValue("As178");
        row3.createCell(2).setCellValue(87);
        row3.createCell(3).setCellValue(78);        //..... Omit part of the code // Output Excel Documents 
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();        return null;
    }

Supplementary explanation of garbled code problem

1. File name garbled (I found that as long as the file name garbled is solved, other garbled codes will be solved) response. setHeader ("Content-disposition", "attachment; filename = Chinese. xls ");

This method can be used as a public method, and can be called if there is any garbled code in the future

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("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(); 
}

When called, response. setHeader ("Content-disposition", "attachment; filename= "+ toUtf8String (" Chinese. xls "));

When I look it up on the Internet, it means

What I want to say today is that when creating a worksheet, there will be a garbled problem when using Chinese as the file name and worksheet name. First, we will use Chinese as the worksheet name. The code for creating a worksheet is as follows:

HSSFWorkbook workbook = new HSSFWorkbook (); //Create an EXCEL file

HSSFSheet sheet = workbook. createSheet (sheetName); //Create a worksheet

In this way, it is no problem to use English name as worksheet name, but if sheetName is a Chinese character, there will be garbled code. The solution is as follows:

HSSFSheet sheet= workbook.createSheet();

workbook. setSheetName (0, sheetName, (short) 1); //Here (short) 1 is the key to solve the Chinese garbled code; The first parameter is the index number of the worksheet.

But I found that there is no such method, only need to change the file name garbled code, other garbled code will be solved naturally! ! !

The above is Java with POI import export Excel example analysis details, more about how Java import export Excel information please pay attention to other related articles on this site!