导包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
例子
demo1
package com.zz.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* @Description: java类作用描述
* @Author: Bsea
* @CreateDate: 2019/8/26$ 20:55$
*/
public class Demo1 {
public static void main(String[] args) {
// TODO Auto-generated method stub
//创建一个excel文件
HSSFWorkbook wb= new HSSFWorkbook();
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream("c:\\tmp1\\workbook2019.xls");
wb.write(fileOut);
fileOut.close();
} catch ( IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// FileOutputStream fileOut= new FileOutputStream("c:/workbook.xls");
}
}
demo2
package com.zz.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* @Description: java类作用描述
* @Author: Bsea
* @CreateDate: 2019/8/26$ 20:58$
*/
public class Demo2 {
public static void main(String[] args) {
// TODO Auto-generated method stub
HSSFWorkbook wb = new HSSFWorkbook(); //建立新HSSFWorkbook对象
HSSFSheet sheet = wb.createSheet("java10"); //建立新的sheet对象
HSSFRow row = sheet.createRow((short)0);
//在sheet里创建一行,参数为行号(第一行,此处可想象成数组)
HSSFCell cell = row.createCell((short)0);
//在row里建立新cell(单元格),参数为列号(第一列)
cell.setCellValue("2019");
//cell.set
//设置cell的整数类型的值
row.createCell((short)1).setCellValue(1.2); //设置cell浮点类型的值
row.createCell((short)2).setCellValue("test"); //设置cell字符类型的值
row.createCell((short)3).setCellValue(true); //设置cell布尔类型的值
HSSFCellStyle cellStyle = wb.createCellStyle(); //建立新的cell样式
cellStyle.setDataFormat(HSSFDataFormat. getBuiltinFormat("m/d/yy h:mm"));
//设置cell样式为定制的日期格式
HSSFCell dCell =row.createCell((short)4);
dCell.setCellValue(new Date()); //设置cell为日期类型的值
dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式
HSSFCell csCell =row.createCell((short)5);
//csCell.setEncoding(HSSFCell.ENCODING_UTF_16);
//设置cell编码解决中文高位字节截断
csCell.setCellValue("中文测试_Chinese Words Test"); //设置中西文结合字符串
row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);
//建立错误cell
try {
FileOutputStream fileOut = new FileOutputStream("c:\\tmp1\\workbook1.xls");
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
demo3
package com.zz.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* @Description: java类作用描述
* @Author: Bsea
* @CreateDate: 2019/8/26$ 21:29$
*/
public class Demoxlsx {
public static void main(String[] args) throws IOException, InvalidFormatException {
String xlsPath = "C:/tmp1/测试.xlsx";
// excel文档对象
XSSFWorkbook wk = new XSSFWorkbook();
// sheet对象
XSSFSheet sheet = wk.createSheet("测试");
// 字体样式
XSSFFont xssfFont = wk.createFont();
// 加粗
xssfFont.setBold(true);
// 字体名称
xssfFont.setFontName("楷体");
// 字体大小
xssfFont.setFontHeight(12);
// 表头样式
XSSFCellStyle headStyle = wk.createCellStyle();
// 设置字体css
headStyle.setFont(xssfFont);
// 竖向居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
headStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
// headStyle.setBorderBottom(Borde*rStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
// 内容字体样式
XSSFFont contFont = wk.createFont();
// 加粗
contFont.setBold(false);
// 字体名称
contFont.setFontName("楷体");
// 字体大小
contFont.setFontHeight(11);
// 内容样式
XSSFCellStyle contentStyle = wk.createCellStyle();
// 设置字体css
contentStyle.setFont(contFont);
// 竖向居中
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
//contentStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
contentStyle.setBorderTop(BorderStyle.THIN);
// 自动换行
contentStyle.setWrapText(true);
// 数字样式
XSSFCellStyle numStyle = wk.createCellStyle();
// 设置字体css
numStyle.setFont(contFont);
// 竖向居中
numStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
numStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
numStyle.setBorderBottom(BorderStyle.THIN);
numStyle.setBorderLeft(BorderStyle.THIN);
numStyle.setBorderRight(BorderStyle.THIN);
numStyle.setBorderTop(BorderStyle.THIN);
// 标题字体样式
XSSFFont titleFont = wk.createFont();
// 加粗
titleFont.setBold(false);
// 字体名称
titleFont.setFontName("宋体");
// 字体大小
titleFont.setFontHeight(16);
// 标题样式
XSSFCellStyle titleStyle = wk.createCellStyle();
titleStyle.setFont(titleFont);
// 竖向居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setBorderTop(BorderStyle.THIN);
// 合并单元格(第一行、标题)
CellRangeAddress cAddress = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(cAddress);
// 合并单元格(第一个分类)
CellRangeAddress cAddress2 = new CellRangeAddress(2, 3, 0, 0);
sheet.addMergedRegion(cAddress2);
// 创建第一行
XSSFRow row1 = sheet.createRow(0);
// 创建第一行第一列
XSSFCell row1Cell1 = row1.createCell(0);
row1Cell1.setCellValue("title");
row1Cell1.setCellStyle(titleStyle);
XSSFCell row1Cell2 = row1.createCell(1);
// 为了保证合并的单元格能有效追加外框、被合并的单元格、内容要设置为空
row1Cell2.setCellValue("");
row1Cell2.setCellStyle(titleStyle);
XSSFCell row1Cell3 = row1.createCell(2);
row1Cell3.setCellValue("");
row1Cell3.setCellStyle(titleStyle);
XSSFCell row1Cell4 = row1.createCell(3);
row1Cell4.setCellValue("");
row1Cell4.setCellStyle(titleStyle);
// 创建第二行
XSSFRow row2 = sheet.createRow(1);
// 创建第二行第一列
XSSFCell row2Cell1 = row2.createCell(0);
row2Cell1.setCellValue("分类");
row2Cell1.setCellStyle(headStyle);
// 列宽
sheet.setColumnWidth(row2Cell1.getColumnIndex(), 60 * 50);
// 创建第二行第二列
XSSFCell row2Cell2 = row2.createCell(1);
row2Cell2.setCellValue("内容");
row2Cell2.setCellStyle(headStyle);
// 列宽
sheet.setColumnWidth(row2Cell2.getColumnIndex(), 356 * 50);
// 创建第二行第三列
XSSFCell row2Cell3 = row2.createCell(2);
row2Cell3.setCellValue("标准");
row2Cell3.setCellStyle(headStyle);
// 列宽
sheet.setColumnWidth(row2Cell3.getColumnIndex(), 70 * 50);
// 创建第二行第四列
XSSFCell row2Cell4 = row2.createCell(3);
row2Cell4.setCellValue("备注");
row2Cell4.setCellStyle(headStyle);
// 列宽
sheet.setColumnWidth(row2Cell4.getColumnIndex(), 70 * 50);
// 创建第三行
XSSFRow row3 = sheet.createRow(2);
// 创建第三行第一列
XSSFCell row3Cell1 = row3.createCell(0);
row3Cell1.setCellValue("分类1");
row3Cell1.setCellStyle(contentStyle);
// 创建第三行第二列
XSSFCell row3Cell2 = row3.createCell(1);
row3Cell2.setCellValue("AAAAAAAAAAAAAAAAAAAAAA");
row3Cell2.setCellStyle(contentStyle);
// 创建第三行第三列
XSSFCell row3Cell3 = row3.createCell(2);
row3Cell3.setCellValue(10);
row3Cell3.setCellStyle(numStyle);
// 创建第三行第四列
XSSFCell row3Cell4 = row3.createCell(3);
row3Cell4.setCellValue(6);
row3Cell4.setCellStyle(numStyle);
// 创建第四行
XSSFRow row4 = sheet.createRow(3);
// 创建第四行第一列
XSSFCell row4Cell1 = row4.createCell(0);
row4Cell1.setCellValue("");
row4Cell1.setCellStyle(contentStyle);
// 创建第四行第二列
XSSFCell row4Cell2 = row4.createCell(1);
row4Cell2.setCellValue("BBBBBBBBBBBBBBBBBBBBBBBBBBBB");
row4Cell2.setCellStyle(contentStyle);
// 创建第四行第三列
XSSFCell row4Cell3 = row4.createCell(2);
row4Cell3.setCellValue(10);
row4Cell3.setCellStyle(numStyle);
// 创建第四行第四列
XSSFCell row4Cell4 = row4.createCell(3);
row4Cell4.setCellValue(6);
row4Cell4.setCellStyle(numStyle);
// 创建第五行
XSSFRow row5 = sheet.createRow(4);
// 创建第五行第一列
XSSFCell row5Cell1 = row5.createCell(0);
row5Cell1.setCellValue("分类2");
row5Cell1.setCellStyle(contentStyle);
// 创建第五行第二列
XSSFCell row5Cell2 = row5.createCell(1);
row5Cell2.setCellValue("CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC");
row5Cell2.setCellStyle(contentStyle);
// 创建第五行第三列
XSSFCell row5Cell3 = row5.createCell(2);
row5Cell3.setCellValue(10);
row5Cell3.setCellStyle(numStyle);
// 创建第五行第四列
XSSFCell row5Cell4 = row5.createCell(3);
row5Cell4.setCellValue(6);
row5Cell4.setCellStyle(numStyle);
FileOutputStream outputStream = new FileOutputStream(xlsPath);
wk.write(outputStream);
outputStream.flush();
}
}
工具类 ExcelUtil
package com.zz.util;
/**
* Created by Bsea
* 2017-06-11 19:12
*/
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.*;
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;
public class ExcelUtil {
public static void readXls() throws IOException {
// InputStream is = new FileInputStream("D:\\excel\\xls_test2.xls");
InputStream is = new FileInputStream("C:\\tmp\\党员资料.xls");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 循环列Cell
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if (hssfCell == null) {
continue;
}
System.out.print(" " + getValue(hssfCell));
}
System.out.println();
}
}
}
public static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
System.out.print("日期处理" );
Date date = hssfCell.getDateCellValue();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");
return sdf.format(date);
}
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
//xlsx
public static void readXlsx() throws IOException{
String fileName = "D:\\excel\\xlsx_test.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook( fileName);
// 循环工作表Sheet
for(int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++){
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt( numSheet);
if(xssfSheet == null){
continue;
}
// 循环行Row
for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++ ){
XSSFRow xssfRow = xssfSheet.getRow( rowNum);
if(xssfRow == null){
continue;
}
// 循环列Cell
for(int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++){
XSSFCell xssfCell = xssfRow.getCell( cellNum);
if(xssfCell == null){
continue;
}
System.out.print(" "+getsValue(xssfCell));
}
System.out.println();
}
}
}
public static String getsValue(XSSFCell xssfCell){
if(xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN){
return String.valueOf( xssfCell.getBooleanCellValue());
}else if(xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC){
return String.valueOf( xssfCell.getNumericCellValue());
}else{
return String.valueOf( xssfCell.getStringCellValue());
}
}
public static void main(String[] args) throws IOException {
readXls();
}
}