创建excel
public class PoiWriter{
public static void main(String[] args) throws Exception{
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("第一页");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("hello poi...");
OutputStream os = new FileOutputStream("e:/poiDemo1.xls");
workbook.write(os);
os.close();
}
private static void createDateCell(Workbook workbook,Row row,short column){
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper helper = workbook.getCreationHelper();
cellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));
Cell cell = row.createCell(column);
cell.setCellStyle(cellStyle);
cell.setCellValue(new Date());
}
private static void setAutoWrap(Workbook workbook,Row row,short column){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);
Cell cell = row.createCell(column);
cell.setCellStyle(cellStyle);
cell.setCellValue("测试数据第一行 \n 测试数据第二行");
}
private static void createAlignCell(Workbook workbook,Row row,short column,short halign,short valign){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
Cell cell=row.createCell(column);
cell.setCellStyle(cellStyle);
cell.setCellValue("测试数据");
}
private static void setBorder(Workbook workbook,Row row,short column){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
Cell cell = row.createCell(column);
cell.setCellStyle(cellStyle);
cell.setCellValue("测试数据");
}
private static void setGroundColor(Workbook workbook,Row row,short column){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Cell cell = row.createCell(column);
cell.setCellStyle(cellStyle);
cell.setCellValue("测试数据");
}
private static void mergeRegion(Sheet sheet,
int firstRow, int lastRow, int firstCol, int lastCol){
sheet.addMergedRegion(new CellRangeAddress(
firstRow,
lastRow,
firstCol,
lastCol
));
}
private static void setFont(Workbook workbook,Row row,short column){
Font font = workbook.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("宋体");
font.setItalic(true);
font.setStrikeout(true);
CellStyle style = workbook.createCellStyle();
style.setFont(font);
Cell cell = row.createCell(column);
cell.setCellStyle(style);
}
private static void createNumCell(Workbook workbook,Row row,short column){
CellStyle style = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("0.0"));
Cell cell = row.createCell(column);
cell.setCellStyle(style);
cell.setCellValue(9999.01);
}
}
读取excel
1.遍历每一行每一列读取excel
public class PoiReader1{
public static void main(String[] args) throws Exception{
InputStream is = new FileInputStream("e:/poiDemo1.xls");
POIFSFileSystem system = new POIFSFileSystem(is);
HSSFWorkbook workbook = new HSSFWorkbook(system);
HSSFSheet sheet = workbook.getSheetAt(0);
if(sheet==null){
return;
}
int rowNum = sheet.getLastRowNum();
for( int i = 0 ; i < rowNum ; i++){
HSSFRow row = sheet.getRow(i);
if(row==null){
continue;
}
short cellNum = row.getLastCellNum();
for(int m = 0; m<cellNum ;m++){
HSSFCell cell = row.getCell(m);
if(cell==null){
continue;
}
System.out.print(" " + getValue(cell));
}
System.out.println();
}
}
private static String getValue(HSSFCell cell){
int cellType = cell.getCellType();
if(cellType==HSSFCell.CELL_TYPE_BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cellType == HSSFCell.CELL_TYPE_NUMERIC){
return String.valueOf(cell.getNumericCellValue());
}else{
return String.valueOf(cell.getStringCellValue());
}
}
}
2.直接抽取excel的全部文本信息
public class PoiReader1{
public static void main(String[] args) throws Exception{
InputStream is = new FileInputStream("e:/poiDemo1.xls");
POIFSFileSystem system = new POIFSFileSystem(is);
HSSFWorkbook workbook = new HSSFWorkbook(system);
ExcelExtractor extractor = new ExcelExtractor(workbook);
String text = extractor.getText();
System.out.println(text);
}
}
3.读取excel后修改excel
public static void main(String[] args) throws Exception{
InputStream is =new FileInputStream("e:/poiDemo1.xls");
POIFSFileSystem system = new POIFSFileSystem(is);
Workbook workbook = new HSSFWorkbook(system);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
if(cell==null){
cell=row.createCell(3);
}
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("测试单元格");
FileOutputStream fs = new FileOutputStream("e:/poiDemo1.xls");
workbook.write(fs);
fs.close();
}
}
Apache Poi坐标
Apache POI是Apache提供的使用Java程序对Microsoft Office格式文档操作的API
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>