POI读取03和07版本

package cn.itcast.test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;


public class TestPOI2Excel {
@Test
public void testWrite03Excel(){
try {
//1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建工作表
HSSFSheet sheet = workbook.createSheet("hello world");
//3.创建行
HSSFRow row = sheet.createRow(3);
//4.创建单元格:(3.3)
HSSFCell cell = row.createCell(3);
cell.setCellValue("hello world");
//输出到硬盘
FileOutputStream outputStream = new FileOutputStream("E:\\itcast\\测试.xls");
workbook.write(outputStream);
workbook.close();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}

@Test
public void testRead03Excel(){
try {
FileInputStream inputStream = new FileInputStream("E:\\itcast\\测试.xls");
//1.读取工作簿
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//2.读取工作表
HSSFSheet sheet = workbook.getSheetAt(0);
//3.读取行
HSSFRow row = sheet.getRow(3);
//4.读取单元格:(3.3)
HSSFCell cell = row.getCell(3);
System.out.println(cell.getStringCellValue());
workbook.close();
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* xlsx 版本读取
*/
@Test
public void testRead07Excel(){
try {
FileInputStream inputStream = new FileInputStream("E:\\itcast\\测试.xls");
//1.读取工作簿
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//2.读取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//3.读取行
XSSFRow row = sheet.getRow(3);
//4.读取单元格:(3.3)
XSSFCell cell = row.getCell(3);
System.out.println(cell.getStringCellValue());
workbook.close();
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 利用文件名
* 自动判断 xls   xlsx版本进行读取
*/
@Test
public void testRead03And07Excel(){
try {
String fileName = "E:\\itcast\\测试.xls";
if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");

FileInputStream inputStream = new FileInputStream(fileName);
//1.读取工作簿
Workbook workbook = is03Excel?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);
//2.读取工作表
Sheet sheet = workbook.getSheetAt(0);
//3.读取行
Row row = sheet.getRow(3);
//4.读取单元格:(3.3)
Cell cell = row.getCell(3);
System.out.println(cell.getStringCellValue());
workbook.close();
inputStream.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* 设置样式
*/
@Test
public void testExcelstyle(){
try {
//1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//1.1创建合并单元格对象
CellRangeAddress cellRangeAddress = new CellRangeAddress(2,2,2,4);
//1.2创建单元格样式
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//1.3创建字体(加载于样式应用于单元格)
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

//font.setFontHeight((short)16);//设置字体大小(自己设置的高度/20)
font.setFontHeightInPoints((short) 16);
//加载字体到样式
style.setFont(font);

//单元格背景,需要设置背景填充模式(钻石模式:DIAMONDS     HSSFCellStyle.SOLID_FOREGROUND(以前景色为准))
style.setFillPattern(HSSFCellStyle.DIAMONDS);
style.setFillBackgroundColor(HSSFColor.YELLOW.index);
style.setFillForegroundColor(HSSFColor.RED.index);
//2.创建工作表
HSSFSheet sheet = workbook.createSheet("hello world");
//2.1加载合并单元格对象
sheet.addMergedRegion(cellRangeAddress);
//3.创建行
HSSFRow row = sheet.createRow(2);
//4.创建单元格:(3.3)
HSSFCell cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue("hello world");

//输出到硬盘
FileOutputStream outputStream = new FileOutputStream("E:\\itcast\\测试.xls");
workbook.write(outputStream);
workbook.close();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}