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