Poi基础

①：向excel输出东西

package poi;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

public class Demo1 implements Runnable{

    public static void main(String[] args) throws Exception {
        Demo1 demo1=new Demo1();
        Thread thread=new Thread(demo1);
        thread.start();
        
    }

    @Override
    public void run() {
        Workbook wb=new HSSFWorkbook();
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream("E://poi.xls");  //文件位置
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Sheet sheet=wb.createSheet();  //创建一页
        Row row=sheet.createRow(0);   //创建一行（第一行）
        Cell cell1=row.createCell(0);  //创建一列（第一列）
        Cell cell2=row.createCell(1);
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        cell1.setCellValue(format.format(new Date()));
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        cell2.setCellValue(format.format(new Date()));
        System.out.println("!!!!");
        try {
            wb.write(outputStream);  //向工作簿写东西
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                outputStream.close();  // 关闭流
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

②：poi处理日期：

package poi;

import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Demo2 {

    
    public static void main(String[] args) throws Exception {
        Workbook wb=new HSSFWorkbook();
        FileOutputStream outputStream=new FileOutputStream("E://poi2.xls");
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(0);
        Cell cell=row.createCell(0);
        cell.setCellValue(new Date());
        
        //第一种方法
        CreationHelper helper=wb.getCreationHelper();
        CellStyle cellStyle=wb.createCellStyle();
        cellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        cell=row.createCell(1);
        cell.setCellValue(new Date());
        cell.setCellStyle(cellStyle);
        
        //第二种方法
        cell=row.createCell(2);
        cell.setCellValue(Calendar.getInstance());
        cell.setCellStyle(cellStyle);
        
        wb.write(outputStream);
        outputStream.close();
        
        
    }
}

③：利用for循环遍历

package poi;

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;

public class Demo3 {

    public static void main(String[] args) throws Exception {
        FileInputStream inputStream=new FileInputStream("E://poi.xls");
        POIFSFileSystem fs=new POIFSFileSystem(inputStream);
        HSSFWorkbook wb=new HSSFWorkbook(fs);  //获取工作簿
        HSSFSheet sheet=wb.getSheetAt(0);  //获取第一页
        if(sheet==null){
            return;
        }
        //循环遍历所有的行
        for(int rowNum=0;rowNum<=sheet.getLastRowNum();rowNum++){
            HSSFRow row=sheet.getRow(rowNum);
            //如果行为空就跳过
            if(row==null){
                continue;
            }
            //循环遍历所有的列
            for(int cellNum=0;cellNum<=row.getLastCellNum();cellNum++){
                HSSFCell cell=row.getCell(cellNum);
                //如果行为列就跳过
                if(cell==null){
                    continue;
                }
                System.out.print(" "+getValue(cell));
            }
            System.out.println();
        }
    }
    
    //获取并处理单元格的值
    private static String getValue(HSSFCell hssfCell){
        if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
            return String.valueOf(hssfCell.getBooleanCellValue()); //boolean类型
        }else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
            return String.valueOf(hssfCell.getNumericCellValue());  //数字型
        }else{
            return String.valueOf(hssfCell.getStringCellValue());  //字符串型
        }
    }
}

④：不用for循环遍历

package poi;

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.extractor.ExcelExtractor;

public class Demo4 {

    public static void main(String[] args) throws Exception {
        FileInputStream inputStream=new FileInputStream("E://poi.xls");
        POIFSFileSystem fs=new POIFSFileSystem(inputStream);
        HSSFWorkbook wb=new HSSFWorkbook(fs);
        
        
        ExcelExtractor extractor=new org.apache.poi.hssf.extractor.ExcelExtractor(wb);
        System.out.println(extractor.getText());
    }
}

 ⑤：单元格的文字对齐方式的设置

package poi;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Demo5 {

    
    public static void main(String[] args) throws Exception {
        FileOutputStream outputStream=new FileOutputStream("E://poi3.xls");
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(0);
        
        
        createCell(wb,row,(short)0,HSSFCellStyle.ALIGN_LEFT,HSSFCellStyle.VERTICAL_BOTTOM);
        createCell(wb,row,(short)1,HSSFCellStyle.ALIGN_CENTER,HSSFCellStyle.VERTICAL_CENTER);
        createCell(wb,row,(short)2,HSSFCellStyle.ALIGN_RIGHT,HSSFCellStyle.VERTICAL_JUSTIFY);
        createCell(wb,row,(short)3,HSSFCellStyle.ALIGN_CENTER_SELECTION,HSSFCellStyle.VERTICAL_JUSTIFY);
        wb.write(outputStream);
        outputStream.close();
    }
    
    private static void createCell(Workbook wb,Row row,short column,short halign,short valign){
        Cell cell=row.createCell(column);  //创建单元格
        cell.setCellValue(new HSSFRichTextString("hehe")); //设置单元格值
        CellStyle cellStyle=wb.createCellStyle();  //创建单元格样式
        cellStyle.setAlignment(halign);  // 设置单元格水平对齐方式
        cellStyle.setVerticalAlignment(valign); //设置单元格垂直对其方式
        cell.setCellStyle(cellStyle); //设置单元格样式
    }
}

⑥：单元格边框的设置方式

package poi;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Demo6 {

    
    public static void main(String[] args) throws Exception {
        FileOutputStream outputStream=new FileOutputStream("E://poi4.xls");
        Workbook wb=new HSSFWorkbook();
        CellStyle cellStyle=wb.createCellStyle();
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(1);
        Cell cell=row.createCell(5);
        
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.SKY_BLUE.getIndex());
        
        cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM_DASH_DOT);
        cellStyle.setTopBorderColor(IndexedColors.DARK_RED.getIndex());
        
        cellStyle.setBorderLeft(CellStyle.BORDER_SLANTED_DASH_DOT);
        cellStyle.setLeftBorderColor(IndexedColors.DARK_YELLOW.getIndex());
        
        cellStyle.setBorderRight(CellStyle.BORDER_THICK);
        cellStyle.setRightBorderColor(IndexedColors.GREEN.getIndex());
        
        cell.setCellStyle(cellStyle);
        
        wb.write(outputStream);
        outputStream.close();
    }
}

⑦：单元格背景颜色及图案设置方式

package poi;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Demo7 {

    
    public static void main(String[] args) throws Exception {
        FileOutputStream outputStream=new FileOutputStream("E://poi5.xls");
        Workbook wb=new HSSFWorkbook();
        CellStyle cellStyle=wb.createCellStyle();
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(1);
        
        Cell cell=row.createCell(5);
        cell.setCellValue("yyyy");
        cellStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
        cellStyle.setFillPattern(CellStyle.DIAMONDS);
        cell.setCellStyle(cellStyle);
        
        CellStyle cellStyle2=wb.createCellStyle();
        Cell cell2=row.createCell(6);
        cell2.setCellValue("tttt");
        cellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillPattern(CellStyle.THICK_BACKWARD_DIAG);
        cell2.setCellStyle(cellStyle2);
        
        wb.write(outputStream);
        outputStream.close();
    }
}

⑧：单元格合并的方式

package poi;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.ss.util.CellRangeAddress;

public class Demo8 {

    public static void main(String[] args) throws Exception {
        FileOutputStream outputStream=new FileOutputStream("E://poi6.xls");
        Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(0);
        Cell cell=row.createCell(5);
        
        cell.setCellValue("合并单元格测试");
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 6));
        
        wb.write(outputStream);
        outputStream.close();
    }
}