基于poi和jxl的excel导入和导出的java实现

导入依赖

<dependency>
  <groupId>net.sourceforge.jexcelapi</groupId>
  <artifactId>jxl</artifactId>
  <version>2.6.12</version>
</dependency>
<!--5.0的功能很多导致传递依赖过多，可减低版本-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.0.0</version>
</dependency>

全程的
CstCustomerMapper mapper = sqlsession.getMapper( CstCustomerMapper.class );
List customers = mapper.selectByExample( null );
为数据库得到的List

poi的excel导出

@Test
public void tExcelOutputPOI() throws IOException {
    CstCustomerMapper mapper = sqlsession.getMapper( CstCustomerMapper.class );
    List<CstCustomer> customers = mapper.selectByExample( null );
    FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\94727\\Desktop\\2.xlsx");
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment( HorizontalAlignment.CENTER); // 居中
    // 3.根据excel文件创建一个sheet对象
    Sheet sheet = wb.createSheet("users");// 创建一张表
    // 4.为sheet添加第1行t数据
    Row topRow = sheet.createRow(0);// 创建第一行，起始为0
    Cell cell1 = topRow.createCell(0);
    cell1.setCellStyle(cellStyle);
    cell1.setCellValue("序号");
    //合并单元格
    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
    sheet.addMergedRegion(region);
    //sheet.setHorizontallyCenter(true);
    Row titleRow = sheet.createRow(1);// 创建第一行，起始为0
    titleRow.createCell(0).setCellValue("编号");
    titleRow.createCell(1).setCellValue("姓名");// 第一列
    titleRow.createCell(2).setCellValue("地址");
   int row=2;
    for (CstCustomer customer : customers) {
        Row currentRow = sheet.createRow(row );
        currentRow.createCell(0).setCellValue(customer.getCustNo());
        currentRow.createCell(1).setCellValue(customer.getCustName());// 第一列
        currentRow.createCell(2).setCellValue(customer.getCustAddr());
        row++;
    }
    wb.write( fileOutputStream );
    wb.close();
    fileOutputStream.close();
}

jxl的excel导出

@Test
public void tExcelOutputByJXL() throws IOException, WriteException {
    CstCustomerMapper mapper = sqlsession.getMapper( CstCustomerMapper.class );
    List<CstCustomer> customers = mapper.selectByExample( null );
    FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\94727\\Desktop\\1.xlsx");
    WritableWorkbook wb = Workbook.createWorkbook( fileOutputStream );
    WritableSheet sheet1 = wb.createSheet( "sheet1", 0 );
    //设置单元格长度
    sheet1.setColumnView(0, 25);
    sheet1.setColumnView(1, 25);
    sheet1.setColumnView(2, 25);
    sheet1.addCell(new Label(0, 0, "编号"));
    sheet1.addCell(new Label(1, 0, "名称"));
    sheet1.addCell(new Label(2, 0, "地址"));
    int i=1;
    for (CstCustomer customer : customers) {
        sheet1.addCell( new Label( 0,i,customer.getCustNo() ) );
        sheet1.addCell( new Label( 1,i,customer.getCustName() ) );
        sheet1.addCell( new Label( 2,i,customer.getCustAddr() ) );
   i++;
    }
    wb.write();
    wb.close();
    fileOutputStream.close();
}

基本web工程的实现

excel导出

@Controller
@RequestMapping("/rept")
public class ReptController {
    @Autowired
    CstCustomerService cstCustomerService;
    @RequestMapping("/custDownload")
    public void custDownload(HttpServletResponse resp) throws IOException {
        String fileName = "custData.xlsx";
        resp.setContentType( "application/vnd.ms-excel" );
        resp.addHeader( "Content-Disposition", "attachment;fileName=" + fileName );
        ServletOutputStream outputStream = resp.getOutputStream();
        List<CstCustomer> customers = cstCustomerService.list( 1, 5, new CstCustomer() );
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment( HorizontalAlignment.CENTER); // 居中
        // 3.根据excel文件创建一个sheet对象
        Sheet sheet = wb.createSheet("users");// 创建一张表
        // 4.为sheet添加第1行t数据
        Row topRow = sheet.createRow(0);// 创建第一行，起始为0
        Cell cell1 = topRow.createCell(0);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue("序号");
        //合并单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
        sheet.addMergedRegion(region);
        //sheet.setHorizontallyCenter(true);
        Row titleRow = sheet.createRow(1);// 创建第一行，起始为0
        titleRow.createCell(0).setCellValue("编号");
        titleRow.createCell(1).setCellValue("姓名");// 第一列
        titleRow.createCell(2).setCellValue("地址");
        int row=2;
        for (CstCustomer customer : customers) {
            Row currentRow = sheet.createRow(row );
            currentRow.createCell(0).setCellValue(customer.getCustNo());
            currentRow.createCell(1).setCellValue(customer.getCustName());// 第一列
            currentRow.createCell(2).setCellValue(customer.getCustAddr());
            row++;
        }
        wb.write( outputStream );
        wb.close();
        outputStream.close();
    }

excel导入

导入依赖

<dependency>
  <groupId>commons-fileupload</groupId>
  <artifactId>commons-fileupload</artifactId>
  <version>1.3.1</version>
</dependency>

在bean.xml中配置

<bean id="multipartResolver"
      class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 设置上传文件的最大尺寸为5MB -->
<property name="maxUploadSize">
    <value>5242880</value>
</property>
</bean>

实现

@RequestMapping("/upload")
@ResponseBody
public String upload(MultipartFile multipartFile) throws IOException {
    //1.获取导入execl文件io流
    InputStream is = multipartFile.getInputStream();
    try{
        //2.根据io流构建 execl文档 对象
        Workbook workBook =WorkbookFactory.create( is );
        //3.获取第1个 sheet
        Sheet sheet = workBook.getSheetAt(0);
        if (sheet != null){
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++){
                //4.获取电子表格的每一行
                Row row = sheet.getRow(i);
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++){
                    //5.获取每一列
                    Cell cell = row.getCell(j);
                    //6.获取每个单元格的值
                    String cellStr = cell.toString();
                    System.out.print("【"+cellStr+"】 ");
                }
                System.out.println();
            }
        }
    }catch (Exception e){
        e.printStackTrace();
    }
    return "success";
}