java 写入excel文件
import static org.apache.poi.hssf.usermodel.HeaderFooter.fontSize;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class Demo_Write {
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("第一个sheet页");
Sheet sheet2 = wb.createSheet("第二个sheet页");
Row row1 = sheet1.createRow(0);
Cell cell1 = row1.createCell(0);
Cell cell2 = row1.createCell(1);
Cell cell3 = row1.createCell(2);
Cell cell4 = row1.createCell(3);
Cell cell5 = row1.createCell(4);
Cell cell6 = row1.createCell(5);
Cell cell7 = row1.createCell(6);
cell1.setCellValue(1.2);
cell2.setCellValue(false);
cell3.setCellValue("这是一个字符串");
cell4.setCellValue(new Date());
cell5.setCellValue(Calendar.getInstance());
CreationHelper creationHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
cell6.setCellValue(new Date());
cell6.setCellStyle(cellStyle);
cell7.setCellValue(Calendar.getInstance());
cell7.setCellStyle(cellStyle);
Row row2 = sheet1.createRow(1);
row2.setHeightInPoints(30);
Cell cell2_1 = row2.createCell(0);
Cell cell2_2 = row2.createCell(1);
Cell cell2_3 = row2.createCell(2);
Cell cell2_5 = row2.createCell(4);
Cell cell2_6 = row2.createCell(5);
Cell cell2_7 = row2.createCell(6);
cell2_1.setCellValue("左上对齐");
CellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setAlignment(HorizontalAlignment.LEFT);
cellStyle2.setVerticalAlignment(VerticalAlignment.TOP);
cell2_1.setCellStyle(cellStyle2);
cellStyle2.setBorderBottom(BorderStyle.DASH_DOT);
cellStyle2.setBottomBorderColor(IndexedColors.GREEN.getIndex());
cell2_2.setCellValue("背景色");
CellStyle cellStyle3=wb.createCellStyle();
cellStyle3.setFillPattern(FillPatternType.SPARSE_DOTS);
cellStyle3.setFillForegroundColor(IndexedColors.GOLD.getIndex());
cell2_2.setCellStyle(cellStyle3);
cell2_3.setCellValue("合并单元格测试测试测试");
sheet1.addMergedRegion(new CellRangeAddress(
1,
1,
2,
3
));
Font font=wb.createFont();
font.setFontHeightInPoints((short) 30);
font.setFontName("黑体");
font.setItalic(true);
font.setBold(false);
font.setColor(IndexedColors.BLUE.getIndex());
font.setStrikeout(true);
CellStyle cellStyle4=wb.createCellStyle();
cellStyle4.setFont(font);
cell2_5.setCellValue("设置字体样式");
cell2_5.setCellStyle(cellStyle4);
CellStyle cellStyle5=wb.createCellStyle();
cellStyle5.setWrapText(true);
cell2_6.setCellValue("换行 \n 成功了!");
cell2_6.setCellStyle(cellStyle5);
CellStyle cellStyle6=wb.createCellStyle();
cellStyle6.setDataFormat(wb.createDataFormat().getFormat("#,##0.000"));
cell2_7.setCellValue(11111111.1);
cell2_7.setCellStyle(cellStyle6);
HSSFPrintSetup hps=(HSSFPrintSetup) sheet1.getPrintSetup();
hps.setPaperSize((short) 9);
hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
hps.setFitHeight((short)10);
hps.setFitWidth((short)9);
hps.setLandscape(true);
hps.setLeftToRight(true);
sheet1.setHorizontallyCenter(true);
sheet1.setVerticallyCenter(true);
HSSFHeader header=(HSSFHeader) sheet1.getHeader();
header.setCenter("居中页眉");
fontSize((short) 16);
HSSFFooter footer=(HSSFFooter) sheet1.getFooter();
footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
sheet1.setAutobreaks(true);
sheet1.setAutobreaks(false);
int i=sheet1.getLastRowNum();
if(i!=0 && i%1==0) {
sheet1.setRowBreak(i);
}
sheet1.setRepeatingRows(new CellRangeAddress(0,1,0,7));
sheet1.setZoom(75);
sheet1.setColumnWidth(7, 20);
sheet1.setDisplayGridlines(false);
FileOutputStream fileOutStream = new FileOutputStream("G:\\用POI搞出来的工作簿.xlsx");
wb.write(fileOutStream);
System.out.println("操作结束!");
if (null != fileOutStream) {
fileOutStream.close();
}
if (null != wb) {
wb.close();
}
}
}
读取
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
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.poifs.filesystem.POIFSFileSystem;
public class Demo_Read {
public static void main(String[] args) throws Exception {
InputStream is = new FileInputStream("G:\\用POI搞出来的工作簿.xlsx");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
HSSFSheet hssfSheet = wb.getSheetAt(sheetNum);
System.out.println(wb.getSheetName(sheetNum));
if (hssfSheet == null) {
continue;
}
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
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();
}
}
if (null != is) {
is.close();
}
if (null != wb) {
wb.close();
}
}
private static String getValue(HSSFCell cell) {
if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
|| "yyyy-MM-dd HH:mm:ss".equals(cell.getCellStyle().getDataFormatString())) {
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
} else {
switch (cell.getCellType()) {
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case NUMERIC:
return String.valueOf(cell.getNumericCellValue());
default:
return String.valueOf(cell.getStringCellValue());
}
}
}
}