Java操作Excel（ApachePoi详解）1.导⼊依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
2.Excel表单元格控制
2.1 最基础的⾏列控制
public class Test00 {
public static void main(String[] args)throws IOException {
//⽂件路径
baiku
String filePath ="C:\\Urs\\masiyi\\IdeaProjects\\demo\\src\\main\\resources\\test.xls";
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
//创建⼯作表(Sheet)
HSSFSheet sheet = ateSheet("Test");
// 创建⾏，从0开始
HSSFRow row = ateRow(0);
暧昧的英文
// 创建⾏的单元格，也是从0开始
rack
HSSFCell cell = ateCell(0);
// 设置单元格内容
cell.tCellValue("CsdnerM");
/
/ 设置单元格内容，重载
// 设置单元格内容，重载
// 设置单元格内容，重载
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.2 设置格式
public class Test00 {
public static void main(String[] args)throws IOException {
//⽂件路径
String filePath ="C:\\Urs\\masiyi\\IdeaProjects\\demo\\src\\main\\resources\\test.xls";
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow row = ateRow(0);
/
/设置⽇期格式--使⽤Excel内嵌的格式
HSSFCell cell = ateCell(0);
cell.tCellValue(new Date());
HSSFCellStyle style = ateCellStyle();
style.BuiltinFormat("m/d/yy h:mm"));
cell.tCellStyle(style);
//设置保留2位⼩数--使⽤Excel内嵌的格式
cell = ateCell(1);
cell.tCellValue(12.3456789);
style = ateCellStyle();
style.BuiltinFormat("0.00"));
cell.tCellStyle(style);
//设置货币格式--使⽤⾃定义的格式
cell = ateCell(2);
cell.tCellValue(12345.6789);
style = ateCellStyle();
style.ateDataFormat().getFormat("￥#,##0"));
cell.tCellStyle(style);
//设置百分⽐格式--使⽤⾃定义的格式
cell = ateCell(3);
cell.tCellValue(0.123456789);
style = ateCellStyle();
style.ateDataFormat().getFormat("0.00%"));
cell.tCellStyle(style);
//设置中⽂⼤写格式--使⽤⾃定义的格式
cell = ateCell(4);
cell.tCellValue(12345);
代表月亮消灭你日语style = ateCellStyle();
style.ateDataFormat().getFormat("[DbNum2][$-804]0")); cell.tCellStyle(style);
//设置科学计数法格式--使⽤⾃定义的格式
cell = ateCell(5);
cell.tCellValue(12345);
style = ateCellStyle();
style.ateDataFormat().getFormat("0.00E+00"));
cell.tCellStyle(style);
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.3 合并单元格
//创建Excel⽂件(Workbook)
time change
HSSFWorkbook workbook =new HSSFWorkbook();
installHSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow ateRow(0);
//合并列
HSSFCell ateCell(0);
cell.tCellValue("合并列");
CellRangeAddress region=new CellRangeAddress(0,0,0,5);
sheet.addMergedRegion(region);
//合并⾏
ateCell(6);
cell.tCellValue("合并⾏");
region=new CellRangeAddress(0,5,7,7);
sheet.addMergedRegion(region);
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
lloutworkbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.4 单元格对齐
public class Test00 {
public static void main(String[] args)throws IOException {
//⽂件路径
String filePath ="C:\\Urs\\masiyi\\IdeaProjects\\demo\\src\\main\\resources\\test.xls";
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow ateRow(0);
HSSFCell ateCell(0);
cell.tCellValue("单元格对齐");
HSSFCellStyle ateCellStyle();
style.tAlignment(HSSFCellStyle.ALIGN_CENTER);//⽔平居中
皮肤黑粗糙style.tVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.tWrapText(true);//⾃动换⾏
wd style.tIndention((short)5);//缩进
style.tRotation((short)0);//⽂本旋转，这⾥的取值是从-90到90，⽽不是0-180度。
cell.tCellStyle(style);
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.5 设置边框
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow ateRow(0);
HSSFCell ateCell(1);
cell.tCellValue("设置边框");
HSSFCellStyle ateCellStyle();
style.tBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框
style.tBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框
style.tBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框
style.tBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框
style.tTopBorderColor(HSSFColor.RED.index);//上边框颜⾊
style.tBottomBorderColor(HSSFColor.BLUE.index);//下边框颜⾊
style.tLeftBorderColor(HSSFColor.GREEN.index);//左边框颜⾊
style.tRightBorderColor(HSSFColor.PINK.index);//右边框颜⾊
cell.tCellStyle(style);
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.6 设置字体
public class Test00 {
public static void main(String[] args)throws IOException {
//⽂件路径
String filePath ="C:\\Urs\\masiyi\\IdeaProjects\\demo\\src\\main\\resources\\test.xls";
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow ateRow(0);
HSSFCell cell = ateCell(1);
cell.tCellValue("设置字体");
HSSFCellStyle style = ateCellStyle();
HSSFFont font = ateFont();
font.tFontName("华⽂⾏楷");//设置字体名称
font.tFontHeightInPoints((short)28);//设置字号
font.tColor(HSSFColor.RED.index);//设置字体颜⾊
font.tUnderline(FontFormatting.U_SINGLE);//设置下划线
font.tTypeOfft(FontFormatting.SS_SUPER);//设置上标下标
font.tStrikeout(true);//设置删除线
style.tFont(font);
cell.tCellStyle(style);
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.7 设置图案样式
public class Test00 {
public static void main(String[] args)throws IOException {
//⽂件路径
String filePath ="C:\\Urs\\masiyi\\IdeaProjects\\demo\\src\\main\\resources\\test.xls";
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow ateRow(0);
HSSFCell cell = ateCell(1);
HSSFCellStyle style = ateCellStyle();
style.tFillForegroundColor(HSSFColor.GREEN.index);//设置图案颜⾊
style.tFillBackgroundColor(HSSFColor.RED.index);//设置图案背景⾊
style.tFillPattern(HSSFCellStyle.SQUARES);//设置图案样式
cell.tCellStyle(style);
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
2.8 设置宽度和⾼度
public class Test00 {
public static void main(String[] args)throws IOException {
//⽂件路径
gmat是什么String filePath ="C:\\Urs\\masiyi\\IdeaProjects\\demo\\src\\main\\resources\\test.xls";
//创建Excel⽂件(Workbook)
HSSFWorkbook workbook =new HSSFWorkbook();
HSSFSheet sheet = ateSheet("Test");// 创建⼯作表(Sheet)
HSSFRow row = ateRow(1);
HSSFCell cell = ateCell(1);
cell.tCellValue("123456789012345678901234567890");
sheet.tColumnWidth(1,31*256);//设置第⼀列的宽度是31个字符宽度
row.tHeightInPoints(50);//设置⾏的⾼度是50个点
FileOutputStream out =new FileOutputStream(filePath);
//保存Excel⽂件
workbook.write(out);
out.clo();//关闭⽂件流
System.out.println("OK!");
}
}
3.使⽤Excel公式
3.1 基本计算
