导出
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
package com.zyf.mall.tiny.test1;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
public class CreateExcel {
public static void main(String[] args) throws IOException {
generateExcel();
}
public static void generateExcel() throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = genSheet(workbook,"testExcel");
XSSFCellStyle titleStyle = genTitleStyle(workbook);
XSSFCellStyle contextStyle = genContextStyle(workbook);
genExcel(sheet,titleStyle,contextStyle);
FileOutputStream out = new FileOutputStream(new File("D://zhangyunfei/test.xlsx"));
workbook.write(out);
out.close();
}
public static void genExcel(XSSFSheet sheet,XSSFCellStyle titleStyle,XSSFCellStyle contextStyle) {
for (int i = 0; i < 10; i++) {
if (i == 0) {
sheet.setColumnWidth(i, 1500);
} else if (i == 2 || i == 3 || i == 4) {
sheet.setColumnWidth(i, 4000);
} else if (i == 9) {
sheet.setColumnWidth(i, 3000);
} else {
sheet.setColumnWidth(i, 2000);
}
}
sheet.addMergedRegion(new CellRangeAddress(
0,
0,
0,
11
));
XSSFRow row = sheet.createRow(0);
XSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("xxx 幼儿园一年级二班学生信息");
cell.setCellStyle(titleStyle);
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("姓名");
cell.setCellStyle(contextStyle);
cell = row.createCell(1);
cell.setCellValue("性别");
cell.setCellStyle(contextStyle);
cell = row.createCell(2);
cell.setCellValue("学号");
cell.setCellStyle(contextStyle);
cell = row.createCell(3);
cell.setCellValue("出生年月");
cell.setCellStyle(contextStyle);
cell = row.createCell(4);
cell.setCellValue("家庭住址");
cell.setCellStyle(contextStyle);
cell = row.createCell(5);
cell.setCellValue("语文");
cell.setCellStyle(contextStyle);
cell = row.createCell(6);
cell.setCellValue("英语");
cell.setCellStyle(contextStyle);
cell = row.createCell(7);
cell.setCellValue("数学");
cell.setCellStyle(contextStyle);
cell = row.createCell(8);
cell.setCellValue("自然");
cell.setCellStyle(contextStyle);
cell = row.createCell(9);
cell.setCellValue("科学");
cell.setCellStyle(contextStyle);
cell = row.createCell(10);
cell.setCellValue("生物");
cell.setCellStyle(contextStyle);
cell = row.createCell(11);
cell.setCellValue("生物1");
cell.setCellStyle(contextStyle);
for(int i = 2 ; i<10;i++){
row=sheet.createRow(i);
cell=row.createCell(0);
cell.setCellValue("小明");
cell.setCellStyle(contextStyle);
cell=row.createCell(1);
cell.setCellValue("男");
cell.setCellStyle(contextStyle);
cell=row.createCell(2);
cell.setCellValue("NO20180901"+i);
cell.setCellStyle(contextStyle);
cell=row.createCell(3);
cell.setCellValue("2000/02/01");
cell.setCellStyle(contextStyle);
cell=row.createCell(4);
cell.setCellValue("成都市天府广场");
cell.setCellStyle(contextStyle);
cell=row.createCell(5);
cell.setCellValue("90");
cell.setCellStyle(contextStyle);
cell=row.createCell(6);
cell.setCellValue("90");
cell.setCellStyle(contextStyle);
cell=row.createCell(7);
cell.setCellValue("90");
cell.setCellStyle(contextStyle);
cell=row.createCell(8);
cell.setCellValue("98");
cell.setCellStyle(contextStyle);
cell=row.createCell(9);
cell.setCellValue("100");
cell.setCellStyle(contextStyle);
cell=row.createCell(10);
cell.setCellValue("100");
cell.setCellStyle(contextStyle);
cell=row.createCell(11);
cell.setCellValue("100");
cell.setCellStyle(contextStyle);
}
}
public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName){
XSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setHorizontallyCenter(true);
sheet.setFitToPage(false);
Footer footer = sheet.getFooter();
footer.setRight( "Page " + HeaderFooter.numPages()+ " Of "+ HeaderFooter.page());
Header header = sheet.getHeader();
header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time());
XSSFPrintSetup ps = sheet.getPrintSetup();
ps.setLandscape(true);
ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
return sheet;
}
public static XSSFCellStyle genContextStyle(XSSFWorkbook workbook){
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(new XSSFColor(Color.BLACK));
style.setBottomBorderColor(new XSSFColor(Color.BLACK));
style.setLeftBorderColor(new XSSFColor(Color.BLACK));
style.setRightBorderColor(new XSSFColor(Color.BLACK));
return style;
}
public static XSSFCellStyle genTitleStyle(XSSFWorkbook workbook){
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);
XSSFFont titleFont = workbook.createFont();
titleFont.setBold(true);
titleFont.setFontHeight((short)15);
titleFont.setFontHeightInPoints((short)15);
style.setFont(titleFont);
return style;
}
}
导入
package com.zyf.mall.tiny.test1;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
public class TestExcel1 {
public static void main(String[] args) {
try {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\zhangyunfei\\test.xlsx"));
System.out.println("xssfWorkbook对象:" + xssfWorkbook);
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
System.out.println("sheet对象:" + sheet);
int maxRow = sheet.getLastRowNum();
System.out.println("总行数为:" + maxRow);
for (int row = 0; row < maxRow; row++) {
XSSFRow row1 = sheet.getRow(row);
short lastCellNum = 0;
if(row1!=null){
lastCellNum = row1.getLastCellNum();
}else continue;
System.out.println("--------第" + row + "行的数据如下--------");
for (int rol = 0; rol < lastCellNum; rol++){
System.out.print(sheet.getRow(row).getCell(rol) + " ");
}
System.out.println();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}