POI互換性読み出しExcel 2003/Excel 2007メソッド
30853 ワード
POIを使用してExcel 2003、2007の形式ファイルを操作する方法を記録します.
Jarパッケージ導入
ソースコード
Jarパッケージ導入
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poiartifactId>
<version>${poi.version}version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxmlartifactId>
<version>${poi.version}version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxml-schemasartifactId>
<version>${poi.version}version>
dependency>ソースコード
public class ExcelUtil {
/**
* Xls Excel
*
* @param path
* @throws Exception
*/
public static void printXls(String path) throws Exception {
try {
//
FileOutputStream out = new FileOutputStream(path);
// Workbook
HSSFWorkbook wb = new HSSFWorkbook();
// Sheet
HSSFSheet sheet = wb.createSheet();
// start
HSSFRow row = sheet.createRow(0); //
HSSFCell cell = row.createCell(0); //
HSSFRichTextString richText = new HSSFRichTextString(" ");
cell.setCellValue(richText);
//
HSSFCell enCell = row.createCell(3);
enCell.setCellValue(new HSSFRichTextString("English font test"));
//
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 4000);
// end
// PrintSetup
HSSFPrintSetup printSetup = sheet.getPrintSetup();
// A4
printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
wb.write(out);
out.close();
} catch (Exception e) {
throw e;
}
}
/**
* Xlsx Excel
*
* @param path
* @throws Exception
*/
public static void printXlsx(String path) throws Exception {
try {
//
FileOutputStream out = new FileOutputStream(path);
// Workbook
XSSFWorkbook wb = new XSSFWorkbook();
// Sheet
XSSFSheet sheet = wb.createSheet();
// start
XSSFRow row = sheet.createRow(0); //
XSSFCell cell = row.createCell(0); //
XSSFRichTextString richText = new XSSFRichTextString(" ");
cell.setCellValue(richText);
//
XSSFCell enCell = row.createCell(3);
enCell.setCellValue(new XSSFRichTextString("English font test"));
//
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 4000);
// end
// PrintSetup
XSSFPrintSetup printSetup = sheet.getPrintSetup();
// A4
printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
wb.write(out);
out.close();
} catch (Exception e) {
throw e;
}
}
/***
* Xls
*
* @param path
* @throws Exception
*/
public static void writeXls(String path) throws Exception {
HSSFCell cell = null;
HSSFCellStyle cellstyle = null;
//
OutputStream os = new FileOutputStream(path);
//
HSSFWorkbook work = new HSSFWorkbook();
//
HSSFSheet sheet = work.createSheet("sheet1");
// （ 0 ）
HSSFRow row = sheet.createRow(0);
// （ 0 ）
row.createCell(0).setCellValue(" ");
row.createCell(1).setCellValue(" ");
row.createCell(2).setCellValue(new Date());
// start 3
// workbook （cell style）
cellstyle = work.createCellStyle();
cellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell = row.createCell(3);
cell.setCellValue(new Date());
cell.setCellStyle(cellstyle);
cellstyle = work.createCellStyle();
// end 3
// start 4
cell = row.createCell(4);
HSSFFont cnFont = work.createFont(); //
cnFont.setFontHeightInPoints((short) 10); //
cnFont.setBoldweight(Font.BOLDWEIGHT_BOLD); //
cnFont.setFontName(" _GB2312"); //
cellstyle.setFont(cnFont);
cell.setCellValue(" ！");
cell.setCellStyle(cellstyle);
// end 4
//
HSSFSheet sh2 = work.createSheet("sheet2");
HSSFRow row2 = sh2.createRow((short) 0);
cell = row2.createCell(0);
cellstyle = work.createCellStyle();
cellstyle.setAlignment(CellStyle.ALIGN_RIGHT);
cell.setCellStyle(cellstyle);
cell.setCellValue(" , ");
cellstyle = work.createCellStyle();
cellstyle.setAlignment(CellStyle.ALIGN_FILL);
cell = row2.createCell(1);
cell.setCellStyle(cellstyle);
cell.setCellValue("==");
// 1 5
sh2.addMergedRegion(new CellRangeAddress(0, 0, 1, 5));
//
HSSFSheet sh3 = work.createSheet("sheet3");
HSSFPrintSetup ps = sh3.getPrintSetup();
sh3.setAutobreaks(true);
sh3.setSelected(true);
ps.setFitHeight((short) 1);
ps.setFitWidth((short) 1);
HSSFRow row3 = sh3.createRow(1);
// 1
cell = row3.createCell(1);
//
cellstyle = work.createCellStyle();
cellstyle.setBorderBottom(CellStyle.BORDER_THIN);
cellstyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellstyle.setBorderLeft(CellStyle.BORDER_THIN);
cellstyle.setLeftBorderColor(HSSFColor.GREEN.index);
cellstyle.setBorderRight(CellStyle.BORDER_THIN);
cellstyle.setRightBorderColor(HSSFColor.BLUE.index);
cellstyle.setBorderTop(CellStyle.BORDER_THIN);
cellstyle.setTopBorderColor(HSSFColor.BLACK.index);
cellstyle.setAlignment(CellStyle.ALIGN_FILL);
//
cell.setCellStyle(cellstyle);
// 2
HSSFRow row4 = sh3.createRow(2);
cell = row4.createCell(2);
cell.setCellValue("Use
with word wrap on to create a new line");
// ， wrap=true 。
cellstyle = work.createCellStyle();
cellstyle.setWrapText(true);
cell.setCellStyle(cellstyle);
//
row4.setHeightInPoints((2 * sh3.getDefaultRowHeightInPoints()));
sh3.autoSizeColumn((short) 2);
//
for (int i = 1; i < 11; i++) {
HSSFRow row5 = sh2.createRow(i);
cell = row5.createCell(0);
cellstyle = work.createCellStyle();
HSSFFont font = work.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellstyle.setFont(font);
cell.setCellStyle(cellstyle);
cell.setCellValue(" ");
}
cellstyle = work.createCellStyle();
HSSFFont font = work.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellstyle.setFont(font);
for (int i = 1; i < 21; i++) {
HSSFRow row5 = sheet.createRow(i);
cell = row5.createCell(0);
cell.setCellStyle(cellstyle);
cell.setCellValue(" ");
}
work.write(os);
os.close();
}
/***
* Xls
*
* @param path
* @throws Exception
*/
public static void writeXlsx(String path) throws Exception {
XSSFCell cell = null;
XSSFCellStyle cellstyle = null;
//
OutputStream os = new FileOutputStream(path);
//
XSSFWorkbook work = new XSSFWorkbook();
//
XSSFSheet sheet = work.createSheet("sheet1");
// （ 0 ）
XSSFRow row = sheet.createRow(0);
// （ 0 ）
row.createCell(0).setCellValue(" ");
row.createCell(1).setCellValue(" ");
row.createCell(2).setCellValue(new Date());
// start 3
// workbook （cell style）
cellstyle = work.createCellStyle();
cellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell = row.createCell(3);
cell.setCellValue(new Date());
cell.setCellStyle(cellstyle);
cellstyle = work.createCellStyle();
// end 3
// start 4
cell = row.createCell(4);
XSSFFont cnFont = work.createFont(); //
cnFont.setFontHeightInPoints((short) 10); //
cnFont.setBoldweight(Font.BOLDWEIGHT_BOLD); //
cnFont.setFontName(" _GB2312"); //
cellstyle.setFont(cnFont);
cell.setCellValue(" ！");
cell.setCellStyle(cellstyle);
// end 4
//
XSSFSheet sh2 = work.createSheet("sheet2");
XSSFRow row2 = sh2.createRow((short) 0);
cell = row2.createCell(0);
cellstyle = work.createCellStyle();
cellstyle.setAlignment(CellStyle.ALIGN_RIGHT);
cell.setCellStyle(cellstyle);
cell.setCellValue(" , ");
cellstyle = work.createCellStyle();
cellstyle.setAlignment(CellStyle.ALIGN_FILL);
cell = row2.createCell(1);
cell.setCellStyle(cellstyle);
cell.setCellValue("==");
// 1 5
sh2.addMergedRegion(new CellRangeAddress(0, 0, 1, 5));
//
XSSFSheet sh3 = work.createSheet("sheet3");
XSSFPrintSetup ps = sh3.getPrintSetup();
sh3.setAutobreaks(true);
sh3.setSelected(true);
ps.setFitHeight((short) 1);
ps.setFitWidth((short) 1);
XSSFRow row3 = sh3.createRow(1);
// 1
cell = row3.createCell(1);
//
cellstyle = work.createCellStyle();
cellstyle.setBorderBottom(CellStyle.BORDER_THIN);
cellstyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellstyle.setBorderLeft(CellStyle.BORDER_THIN);
cellstyle.setLeftBorderColor(HSSFColor.GREEN.index);
cellstyle.setBorderRight(CellStyle.BORDER_THIN);
cellstyle.setRightBorderColor(HSSFColor.BLUE.index);
cellstyle.setBorderTop(CellStyle.BORDER_THIN);
cellstyle.setTopBorderColor(HSSFColor.BLACK.index);
cellstyle.setAlignment(CellStyle.ALIGN_FILL);
//
cell.setCellStyle(cellstyle);
// 2
XSSFRow row4 = sh3.createRow(2);
cell = row4.createCell(2);
cell.setCellValue("Use
with word wrap on to create a new line");
// ， wrap=true 。
cellstyle = work.createCellStyle();
cellstyle.setWrapText(true);
cell.setCellStyle(cellstyle);
//
row4.setHeightInPoints((2 * sh3.getDefaultRowHeightInPoints()));
sh3.autoSizeColumn((short) 2);
//
for (int i = 1; i < 11; i++) {
XSSFRow row5 = sh2.createRow(i);
cell = row5.createCell(0);
cellstyle = work.createCellStyle();
XSSFFont font = work.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellstyle.setFont(font);
cell.setCellStyle(cellstyle);
cell.setCellValue(" ");
}
cellstyle = work.createCellStyle();
XSSFFont font = work.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellstyle.setFont(font);
for (int i = 1; i < 21; i++) {
XSSFRow row5 = sheet.createRow(i);
cell = row5.createCell(0);
cell.setCellStyle(cellstyle);
cell.setCellValue(" ");
}
work.write(os);
os.close();
}
/**
* Xls Excel
*
* @param path
* @return List
*/
public static List readXls(String path) throws Exception {
//
InputStream inp = new FileInputStream(path);
HSSFWorkbook workBook = new HSSFWorkbook(inp);
// Excel
List list = new ArrayList();
int sheetCount = workBook.getNumberOfSheets();
System.out.println(sheetCount);
for (int i = 0; i < sheetCount; i++) {
String shname = workBook.getSheetName(i);//
HSSFSheet sheet = workBook.getSheetAt(i);//
int rows = sheet.getPhysicalNumberOfRows(); //
System.out.println(rows);
if (rows > 0) {
for (int j = 0; j < rows; j++) {
HSSFRow row = sheet.getRow(j); //
if (row != null) {
int cells = row.getLastCellNum(); //
for (int k = 0; k < cells; k++) {
/** （ ） */
HSSFCell cell = row.getCell(k);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String content = cell.getStringCellValue();
if (StringUtils.isNotEmpty(content)) {
// System.out.println(" :"+content);
list.add(content);
}
}
}
}
}
}
}
}
return list;
}
/**
* Xlsx Excel
*
* @param path
* @return List
*/
public static List readXlsx(String path) throws Exception {
//
InputStream inp = new FileInputStream(path);
XSSFWorkbook workBook = new XSSFWorkbook(inp);
// Excel
List list = new ArrayList();
int sheetCount = workBook.getNumberOfSheets();
System.out.println(sheetCount);
for (int i = 0; i < sheetCount; i++) {
String shname = workBook.getSheetName(i);//
XSSFSheet sheet = workBook.getSheetAt(i);//
int rows = sheet.getPhysicalNumberOfRows(); //
System.out.println(rows);
if (rows > 0) {
for (int j = 0; j < rows; j++) {
XSSFRow row = sheet.getRow(j); //
if (row != null) {
int cells = row.getLastCellNum(); //
for (int k = 0; k < cells; k++) {
/** （ ） */
XSSFCell cell = row.getCell(k);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String content = cell.getStringCellValue();
if (StringUtils.isNotEmpty(content)) {
// System.out.println(" :"+content);
list.add(content);
}
}
}
}
}
}
}
}
return list;
}
/**
* @param args
*/
public static void main(String[] args) {
//
try {
List list = ExcelUtil.readXlsx("/Users/xianglingchuan/Downloads/test(1).xlsx");
for (String string : list) {
System.out.println(string);
}
list = ExcelUtil.readXls("/Users/xianglingchuan/Downloads/readxls.xls");
for (String string : list) {
System.out.println(string);
}
} catch (Exception e) {
e.printStackTrace();
}
//
try {
//ExcelUtil.printXls("/Users/xianglingchuan/Downloads/test_print.xls");
//ExcelUtil.printXlsx("/Users/xianglingchuan/Downloads/test_print.xlsx");
} catch (Exception e) {
e.printStackTrace();
}
try {
//ExcelUtil.writeXls("/Users/xianglingchuan/Downloads/test_write.xls");
//ExcelUtil.printXlsx("/Users/xianglingchuan/Downloads/test_print.xlsx");
} catch (Exception e) {
e.printStackTrace();
}
}
}