通过 poi 导入 Excel代码

最新推荐文章于 2022-07-21 16:48:00 发布

转载最新推荐文章于 2022-07-21 16:48:00 发布 · 78 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/he0xff/p/6038455.html

文章标签：

#java

本文介绍了一个Java程序如何读取和写入Excel文件的具体实现。利用Apache POI库，该程序能够处理不同格式的数据，并实现了对.xls和.xlsx文件的兼容。此外，还提供了设置单元格样式和格式化的代码示例。

（1）操作excel的几种开源框架

　　Apache POI

　　OpenXls

　　JEXCEL

　　JXLS

　　xlSQL

　　JCOM




package com.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class UploadExcel {
    
    public static void main(String[] args) throws IOException {
        
        String excelFilePath = "F://项目信息表格.XLSX";
        UploadExcel.readExcel(excelFilePath);
        
        //UploadExcel.writeExcel();
    }
    
    //对日期格式//数字类型的格式化
    public static String getFormatKey(short formatNumber, Date date)
    {
        String format = "" ;
        
        SimpleDateFormat sdf = null;
        
        if(formatNumber == HSSFDataFormat.getBuiltinFormat("h:mm"))
        {
            sdf = new SimpleDateFormat("HH:mm");
        }
        else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy"))
        {
            sdf = new SimpleDateFormat("yyyy-MM-dd");
        }
        else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))
        {
            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        }
        else
        {
            sdf = new SimpleDateFormat("MM-dd");
        }
        format = sdf.format(date);
        
        return format;
    }
    
    public static String getCellValue(Cell cell)
    {
        String cellValue = "";
        
        if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
        {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
        {
             short format = cell.getCellStyle().getDataFormat(); //获取类型值
             String printString = "";
             if( format == 14 || format == 20 || format == 22
                     || format == 31 || format == 32 || format == 57 || format == 58)
             {
                 printString  =  UploadExcel.getFormatKey(format, cell.getDateCellValue());
             }
             else
             {
                DecimalFormat df = new DecimalFormat("#.#");
                printString = df.format(cell.getNumericCellValue()); 
             }
             cellValue = printString;
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_STRING)
        {
            cellValue = cell.getStringCellValue();
        }
        else
        {
            cellValue = cell.getStringCellValue();
        }
        return cellValue;
    }
    //读excel
    public static void readExcel(String excelFilePath) throws IOException{
        
            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
            
            //读写xls和xlsx格式时，HSSFWorkbook针对xls，XSSFWorkbook针对xlsx
            
            Workbook workbook = null; 
            
            int index = excelFilePath.lastIndexOf(".");
            String suffix = excelFilePath.substring(index + 1).toLowerCase();
            
            if ("xls".equals(suffix))
            {  
                workbook = new HSSFWorkbook(inputStream); 
                
            }
            else if("xlsx".equals(suffix))
            {  
                workbook = new XSSFWorkbook(inputStream);  
            }  
            else
            {
                System.err.println("错误");
            }
            Sheet firstSheet = workbook.getSheetAt(0);
            
            int columns = firstSheet.getRow(0).getPhysicalNumberOfCells();
            int rows = firstSheet.getPhysicalNumberOfRows();
            
            for(int i = 0 ; i < rows; i++)
            {
                Row row = firstSheet.getRow(i);
                
                for(int j = 0; j < columns; j++)
                {
                    Cell cell = row.getCell(j);
                    
                    System.out.println(UploadExcel.getCellValue(cell));
                    
                }
                System.out.println("###");
            }
            
            /**
            Iterator<Row> iterator = firstSheet.iterator();
            
            while (iterator.hasNext()) {
                
                Row nextRow = iterator.next();
                
                int lastCell = nextRow.getLastCellNum();//一行的单元格个数，从1开始
                
                Iterator<Cell> cellIterator = nextRow.cellIterator();
                
                while (cellIterator.hasNext()) {
                    
                    Cell cell = cellIterator.next();
                    
                    switch (cell.getCellType()) {
                    
                        case Cell.CELL_TYPE_BOOLEAN:
                            {
                                System.out.print(cell.getBooleanCellValue());
                                break;
                            }
                        case Cell.CELL_TYPE_NUMERIC:
                            {
                                 short format = cell.getCellStyle().getDataFormat(); //获取类型值
                                
                                 String printString = "";
                                 
                                 if( format == 14 || format == 20 || format == 22
                                         || format == 31 || format == 32 || format == 57 || format == 58)
                                 {
                                     printString  =  UploadExcel.getFormatKey(format, cell.getDateCellValue());
                                 }
                                 else
                                 {
                                    DecimalFormat df = new DecimalFormat("#.#");
                                    printString = df.format(cell.getNumericCellValue()); 
                                 }
                                 
                                 System.out.print(printString);
                                 
                                 break;
                            }
                        case Cell.CELL_TYPE_STRING:
                            {
                                System.out.print(cell.getStringCellValue());
                                break;
                            }
                        default : 
                            {
                                System.out.print(cell.getStringCellValue());
                            }
                            
                    }
                    System.out.print("|");
                }
                System.out.println("###");
            }
             **/
            workbook.close();
            inputStream.close();
    }
    //写excel
    public static void writeExcel(){
        try {
            FileOutputStream fileOut = new FileOutputStream("F://poi-test.xls");
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

            // index from 0,0... cell A1 is cell(0,0)
            HSSFRow row1 = worksheet.createRow((short) 0);

            HSSFCell cellA1 = row1.createCell((short) 0);
            cellA1.setCellValue("Hello");
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellA1.setCellStyle(cellStyle);

            HSSFCell cellB1 = row1.createCell((short) 1);
            cellB1.setCellValue("Goodbye");
            cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellB1.setCellStyle(cellStyle);

            HSSFCell cellC1 = row1.createCell((short) 2);
            cellC1.setCellValue(true);

            HSSFCell cellD1 = row1.createCell((short) 3);
            cellD1.setCellValue(new Date());
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            cellD1.setCellStyle(cellStyle);

            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
            System.out.println("读取结束");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

导出excel名称乱码转化

response.setHeader("Content-disposition", "attachment;filename=" + new String("销售管理导出订单".getBytes( "gb2312" ), "ISO8859-1" ) + ".xls");


//跨行、跨列

Integer rowRange = rowNumber + rowspanNumber - 1;
sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowRange, 0,0));
sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowRange, 1,1));

转载于:https://www.cnblogs.com/he0xff/p/6038455.html