首先导入poi jar包
我这里用的是 poi-3.11和poi-excelant-3.11后者主要用于支持xlsx
好了进入正题:创建excel操作
package jxlTest;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
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.ss.util.CellRangeAddress;
public class Test {
public static void main(String[] args) throws IOException {
//创建excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个工作表sheet
HSSFSheet sheet = workbook.createSheet();
//创建第一行
HSSFRow row1 = sheet.createRow(0);//参数为行数,为行号-1
//创建单元格
HSSFCell cell_1_1 = row1.createCell(0);//参数为列数,从0开始
cell_1_1.setCellValue("id");
HSSFCell cell_1_2 = row1.createCell(1);
cell_1_2.setCellValue("name");
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3));//开始行,结束行,开始列,结束列
//设置单元格样式为居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置单元格字体
HSSFFont font = workbook.createFont();
font.setFontName("仿宋_GB2312");
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字号
font.setFontHeight((short)12);
//提交样式
cellStyle.setFont(font);
cell_1_1.setCellStyle(cellStyle);
//保存为xls文件
File file = new File("d:/test.xls");
if(!file.exists()){
file.createNewFile();
}
FileOutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);//HSSFWorkbook自带写出文件的功能
outputStream.close();
}
}
读取操作
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
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.ss.usermodel.Cell;
public class ReadTest {
public static void main(String[] args) throws FileNotFoundException, IOException {
// TODO Auto-generated method stub
//需要解析的excel文件
File file = new File("d:/test.xls");
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
//通过顺序获取一个工作表,也可用过表名获取
HSSFSheet sheet = workbook.getSheetAt(0);
//获取本表行数-1
int lastRowNum = sheet.getLastRowNum();
System.out.println("最后一行是"+lastRowNum);//如果只有一行的话返回为0,两行返回1
//开始遍历读取
for (int i = 0; i <= lastRowNum; i++) {//注意这里的条件是小于等于
//获取行对象
HSSFRow row = sheet.getRow(i);
//获取本行单元格的数量
int lastCellNum = row.getLastCellNum();
//遍历单元格的值
for (int j = 0; j < lastCellNum; j++) {//注意这里是小于号
HSSFCell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.print("单元格的值是"+value);
}
System.out.println();//打印换行
}
}
}