POI操作Excel的简单案例

2022-05-10 17:03:07

1、poi所需jar包，没有标有“必须”的可以没有

commons-codec-1.5.jar

commons-logging-1.1.jar

dom4j-1.6.1.jar

junit-4.11.jar

log4j-1.2.13.jar

poi-3.10-FINAL-20140208.jar（必须）

poi-excelant-3.10-FINAL-20140208.jar（必须）

poi-ooxml-3.10-FINAL-20140208.jar

poi-ooxml-schemas-3.10-FINAL-20140208.jar

poi-scratchpad-3.10-FINAL-20140208.jar

stax-api-1.0.1.jar

xmlbeans-2.3.0.jar

创建第一个Excel：

package cn.com.css.poi.excel;

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class NewWorkbook {

public static void main(String[] args) throws Exception {
  Workbook wb = new HSSFWorkbook();

  //CreationHelper creationHelper = wb.getCreationHelper();
  //New Sheet
  Sheet sheet1 = wb.createSheet("产品管理");
  //Create a row and put some cells in it. Rows are 0 based.
  Row row = sheet1.createRow((short)0);
  //Create a cell and put a value in it.
  Cell cell = row.createCell(0);
  cell.setCellValue("编码");
  //在一行实现的方式
  row.createCell(1).setCellValue("名称");

//  Sheet sheet2 = wb.createSheet("属性配置");
//  //下面是创建一个Date Cells
//  Row row2 = sheet2.createRow(0);
//  Cell cell2 = row2.createCell(0);
//  cell2.setCellValue(new Date());
//
//  CellStyle cellStyle = wb.createCellStyle();
//  cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
//  row2.createCell(arg0)

  //String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
     //Sheet sheet3 = wb.createSheet(safeName);

  FileOutputStream fileOut = new FileOutputStream("F:" + File.separator
    + "Excel" + File.separator + "workbook.xls");
  wb.write(fileOut);
  fileOut.close();

}
}

2、创建带有（date）日志类型的Excel文件

package cn.com.css.poi.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class CreateDateCells {

public static void main(String[] args) throws Exception {
  Workbook wb = new HSSFWorkbook();
  CreationHelper createHelper = wb.getCreationHelper();
  Sheet sheet = wb.createSheet("时间类型的cell");

  //创建第一行
  Row row = sheet.createRow(0);
  //Create a cell and put a date value in it,The first cell is not styled as a date.

//也就是是（0，0）这个单元格
  Cell cell = row.createCell(0);
  //为这个单元格设置值
  cell.setCellValue(new Date());

  // we style the second cell as a date (and time). It is important to
     // create a new cell style from the workbook otherwise you can end up
     // modifying the built in style and effecting not only this cell but other cells.
  CellStyle cellStyle = wb.createCellStyle();

  //创建显示的日志格式
  cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
  cell = row.createCell(1);
  cell.setCellValue(new Date());
  cell.setCellStyle(cellStyle);

  //you can also set date as java.util.Calendar
  cell = row.createCell(2);
  cell.setCellValue(Calendar.getInstance());
  cell.setCellStyle(cellStyle);

  //Excel的输出位置，注意的是这里的Excel目录得存在。如果不存在，可以用程序判断
  FileOutputStream fileOut = new FileOutputStream("F:" + File.separator
    + "Excel" + File.separator + "CreateDateCells.xls");
  wb.write(fileOut);
  fileOut.close();
}

}

3、自定义带颜色的excel

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
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.hssf.util.HSSFColor;

public class CustomColor {

/**
* \brief 自定义颜色

*
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws IOException
* @date 2014-5-24
* @note begin modify by 修改人修改时间修改内容摘要说明
*/
public static void main(String[] args) throws IOException {

  //这里的是.xls格式的excel文件
  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFSheet sheet = wb.createSheet();
  HSSFRow row = sheet.createRow((short) 0);
  HSSFCell cell = row.createCell((short) 0);

//设置单元格的内容
cell.setCellValue("Default Palette");

  // apply some colors from the standard palette,
  // as in the previous examples.
  // we'll use red text on a lime background

  HSSFCellStyle style = wb.createCellStyle();
  style.setFillForegroundColor(HSSFColor.LIME.index);
  style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

  HSSFFont font = wb.createFont();
  font.setColor(HSSFColor.BLACK.index);
  style.setFont(font);

cell.setCellStyle(style);

  // save with the default palette
  FileOutputStream out = new FileOutputStream("default_palette.xls");
  wb.write(out);
  out.close();

  // now, let's replace RED and LIME in the palette
  // with a more attractive combination
  // (lovingly borrowed from freebsd.org)

cell.setCellValue("Modified Palette");

// creating a custom palette for the workbook
HSSFPalette palette = wb.getCustomPalette();

  // replacing the standard red with freebsd.org red
  palette.setColorAtIndex(HSSFColor.BLACK.index, (byte) 153, // RGB red
                 // (0-255)
    (byte) 0, // RGB green
    (byte) 0 // RGB blue
    );
  // replacing lime with freebsd.org gold
  palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204,
    (byte) 102);

  // save with the modified palette
  // note that wherever we have previously used RED or LIME, the
  // new colors magically appear
  out = new FileOutputStream("modified_palette.xls");
  wb.write(out);
  out.close();
}

}

4、控制Date（时间）的格式

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class DataFormats {

/**
* \brief 方法名与方法功能概述
*
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws IOException
* @date 2014-5-24
* @note begin modify by 修改人修改时间修改内容摘要说明
*/
public static void main(String[] args) throws IOException {
  Workbook wb = new HSSFWorkbook();
     Sheet sheet = wb.createSheet("format sheet");
     CellStyle style;
     DataFormat format = wb.createDataFormat();
     Row row;
     Cell cell;
     short rowNum = 0;
     short colNum = 0;

     row = sheet.createRow(rowNum++);
     cell = row.createCell(colNum);
     cell.setCellValue(11111.25);
     style = wb.createCellStyle();
     style.setDataFormat(format.getFormat("0.0"));
     cell.setCellStyle(style);

     FileOutputStream fileOut = new FileOutputStream("workbook.xls");
     wb.write(fileOut);
     fileOut.close();
}

}

5、DemonstratesVariousAlignmentOptions

package cn.com.css.poi.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class DemonstratesVariousAlignmentOptions {

/**
* \brief 方法名与方法功能概述
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws FileNotFoundException
* @date 2014-5-24
* @note begin modify by 修改人修改时间   修改内容摘要说明
*/
public static void main(String[] args) throws Exception {
  //此种创建的是xlsx后缀的Excel文件
  Workbook wb = new HSSFWorkbook();
  Sheet sheet = wb.createSheet();
  Row row = sheet.createRow((short)2);
  row.setHeightInPoints(30);

  createCell(wb, row, (short)0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
  createCell(wb, row, (short)1, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);
  createCell(wb, row, (short)2, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
  createCell(wb, row, (short)3, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
  createCell(wb, row, (short)4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
  createCell(wb, row, (short)5, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
  createCell(wb, row, (short)6, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);

  FileOutputStream fileOut = new FileOutputStream("xssf-align.xls");
  wb.write(fileOut);
  fileOut.close();
}

/**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb     the workbook
     * @param row    the row to create the cell in
     * @param column the column number to create the cell in
     * @param halign the horizontal alignment for the cell.
     */
private static void createCell(Workbook wb, Row row, short column, short halign, short valign){
  Cell cell = row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
}
}
6、填充和颜色

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FilsAndColors {

}

7、FitSheetToOnePage

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class FitSheetToOnePage {

/**
* \brief 方法名与方法功能概述
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws Exception
* @date 2014-5-24
* @note begin modify by 修改人修改时间   修改内容摘要说明
*/
public static void main(String[] args) throws Exception {
  Workbook wb = new HSSFWorkbook();
  Sheet sheet = wb.createSheet("format sheet");
  PrintSetup ps = sheet.getPrintSetup();

  sheet.setAutobreaks(true);

  ps.setFitHeight((short)1);
  ps.setFitWidth((short)1);

  FileOutputStream fileOut = new FileOutputStream("workbook.xls");
  wb.write(fileOut);
  fileOut.close();
}

}
8、HeaderAndFooter

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class HeaderAndFooter {

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

public class MergingCells {

}

10、读写Excel

package cn.com.css.poi.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadingAndRewritingWorkbooks {

/**
* \brief 方法名与方法功能概述
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws IOException
* @throws InvalidFormatException
* @date 2014-5-24
* @note begin modify by 修改人修改时间   修改内容摘要说明
*/
public static void main(String[] args) throws Exception {
  InputStream inp = new FileInputStream("workbook.xls");
     //InputStream inp = new FileInputStream("workbook.xlsx");

     Workbook wb = WorkbookFactory.create(inp);
     Sheet sheet = wb.getSheetAt(0);
     Row row = sheet.getRow(2);
     Cell cell = row.getCell(0);
     if (cell == null)
         cell = row.createCell(0);
     cell.setCellType(Cell.CELL_TYPE_STRING);
     cell.setCellValue("测试");

     // Write the output to a file
     FileOutputStream fileOut = new FileOutputStream("workbook.xls");
     wb.write(fileOut);
     fileOut.close();
}

}

11、UsingNewLinesInCells

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class UsingNewLinesInCells {

     Row row = sheet.createRow(2);
     Cell cell = row.createCell(2);
     cell.setCellValue("Use \n with word wrap on to create a new line");

     //to enable newlines you need set a cell styles with wrap=true
     CellStyle cs = wb.createCellStyle();
     cs.setWrapText(true);
     cell.setCellStyle(cs);

//increase row height to accomodate two lines of text
row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));

//adjust column width to fit the content
sheet.autoSizeColumn((short)2);

     FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
     wb.write(fileOut);
     fileOut.close();
}
}

12、WorkingWithBorders

package cn.com.css.poi.excel;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
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.Workbook;

public class WorkingWithBorders {

/**
* \brief 方法名与方法功能概述
*
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws Exception
* @date 2014-5-24
* @note begin modify by 修改人修改时间修改内容摘要说明
*/
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new Sheet");

  // Create a row and put some cells in it. Rows are 0 based.
  Row row = sheet.createRow(1);
  // Create a cell and put a value in it
  Cell cell = row.createCell(1);
  cell.setCellValue("这里是内容");

  CellStyle style = wb.createCellStyle();
  style.setBorderBottom(CellStyle.BORDER_THIN);
  style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
  style.setBorderLeft(CellStyle.BORDER_THIN);
  style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
  style.setBorderRight(CellStyle.BORDER_THIN);
  style.setRightBorderColor(IndexedColors.BLUE.getIndex());
  style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
  style.setTopBorderColor(IndexedColors.BLACK.getIndex());
  cell.setCellStyle(style);

  FileOutputStream fileOutputStream = new FileOutputStream("border.xls");
  wb.write(fileOutputStream);
  fileOutputStream.close();

  for (Row row2 : sheet) {
   for (Cell cell2 : row2) {
    CellReference cellRef = new CellReference(row2.getRowNum(), cell2.getColumnIndex());
    switch(cell.getCellType()){
    case Cell.CELL_TYPE_STRING:
     System.out.println(cell.getRichStringCellValue().getString());
     break;
    case Cell.CELL_TYPE_NUMERIC:
     if (DateUtil.isCellDateFormatted(cell)) {
      System.out.println(cell.getDateCellValue());
     } else {
      System.out.println(cell.getNumericCellValue());
     }
     break;
    case Cell.CELL_TYPE_BOOLEAN:
     System.out.println(cell.getBooleanCellValue());
     break;
    case Cell.CELL_TYPE_FORMULA:
     System.out.println(cell.getCellFormula());
     break;
    default:
     System.out.println();
    }
   }
  }

System.out.println("-----------------------");

}

}
13、WorkingWithDifferentTypesOfCells

package cn.com.css.poi.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;

public class WorkingWithDifferentTypesOfCells {

/**
* @param args
*/
public static void main(String[] args) throws Exception {
  Workbook wb = new HSSFWorkbook();
  Sheet sheet = wb.createSheet("new sheet");
  Row row = sheet.createRow((short)2);
  row.createCell(0).setCellValue(1.1);
  row.createCell(1).setCellValue(new Date());
  row.createCell(2).setCellValue(Calendar.getInstance());
  row.createCell(3).setCellValue("一个字符串");
  row.createCell(4).setCellValue(true);
  row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);

  //Write the output to file
  FileOutputStream fileOut = new FileOutputStream("F:" + File.separator
    + "Excel" + File.separator + "操作不同的单元格.xls");
  wb.write(fileOut);
  fileOut.close();
}

}

14、WorkingWithFonts

package cn.com.css.poi.excel;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class WorkingWithFonts {

/**
* \brief 方法名与方法功能概述
* @param args
* @attention 方法的使用注意事项
* @author Administrator
* @throws IOException
* @date 2014-5-24
* @note begin modify by 修改人修改时间   修改内容摘要说明
*/
public static void main(String[] args) throws IOException {
  Workbook wb = new HSSFWorkbook();
  Sheet sheet = wb.createSheet("new sheet");
  //Create a row and put some cells in it.Rows are 0 based.
  Row row = sheet.createRow(1);

  //Create a new Font and alter it
  Font font = wb.createFont();
  font.setFontHeightInPoints((short)24);
  font.setFontName("Courier New");
  font.setItalic(true);
  font.setStrikeout(true);
  font.setBoldweight(Font.BOLDWEIGHT_BOLD);

  CellStyle style = wb.createCellStyle();
  style.setFont(font);

  Cell cell = row.createCell(1);
  cell.setCellValue(1);
  cell.setCellValue("这里测试Font");
  cell.setCellStyle(style);

  //不建议使用下面的方式
//  for (int i = 0; i < 100; i++) {
//   Row row2 = sheet.createRow(i);
//   Cell cell2 = row2.createCell((short)0);
//
//   CellStyle style2 = wb.createCellStyle();
//   Font font2 = wb.createFont();
//   font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
//   style2.setFont(font2);
//   cell2.setCellStyle(style2);
//   cell2.setCellValue(i);
//  }


  for (int i = 3; i < 1000; i++) {
   Row row2 = sheet.createRow(1);
   Cell cell2 = row2.createCell((short)0);
   cell2.setCellStyle(style);
   cell2.setCellValue("这里是测试POI中的Font的地方");
  }

  FileOutputStream fileOutputStream = new FileOutputStream("workbook.xls");
  wb.write(fileOutputStream);
  fileOutputStream.close();
}

}

码农公寓

相关文章