Let's operate Excel with Java! !!
Excel
First, let's prepare an Excel file!
Java
javaExcel
import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.util.Date;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
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;
/**
*Get the value of Excel cell
*
*/
public class ExcelC {
public static final String SAMPLE_XLSX_FILE_PATH = "D:\\filesample\\sampleBook.xlsx";
public static void main(String[] args) throws IOException, InvalidFormatException {
// Creating a Workbook from an Excel file (.xls or .xlsx)
Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));
Sheet sheet = workbook.getSheetAt(0);
Row head = sheet.getRow(1);
ExcelC aaa = new ExcelC();
DataFormatter dataFormatter = new DataFormatter();
//cellrow
int bb = 3;
//cellY coordinates
for(int y = 2 ; y < 12 ; y++) {
Row row = sheet.getRow(y);
System.out.print("Row" + bb + "\r\n");
bb ++;
//cellX coordinates
for (int i = 0 ; i < 11 ; i++){
Cell headc = head.getCell(i);
Cell cell = row.getCell(i);
String cellValue = dataFormatter.formatCellValue(headc);
System.out.print(cellValue + ": ");
aaa.printCellValue(cell);
System.out.print("\r\n");
}
System.out.print("\r\n");
}
// Closing the workbook
workbook.close();
}
private static void printCellValue(Cell cell) {
DateFormat fd = DateFormat.getDateInstance(DateFormat.FULL);
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case STRING:
System.out.print(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date kkk = cell.getDateCellValue();
System.out.print(fd.format(kkk));
} else {
System.out.print(cell.getNumericCellValue());
}
break;
case FORMULA:
System.out.print(cell.getCellFormula());
break;
case BLANK:
System.out.print("");
break;
default:
System.out.print("");
}
System.out.print("\t");
}
}
Execution result
Row3
ida: a1
ida: a2
id: a3
id: a4
id: a5
id: a6
id: a7
id: a8
id: a9
id: a10
ida: a11
Row4
ida: b1
ida: b2
id: b3
id: b4
id: b5
id: b6
id: b7
id: b8
id: b9
id: b10
ida: b11
Row5
ida: c1
ida: c2
id: c3
id: c4
id: c5
id: c6
id: c7
id: c8
id: c9
id: c10
ida: c11
Row6
ida: d1
ida: d2
id: d3
id: d4
id: d5
id: d6
id: d7
id: d8
id: d9
id: d10
ida: d11
Row7
ida: e1
ida: e2
id: e3
id: e4
id: e5
id: e6
id: e7
id: e8
id: e9
id: e10
ida: e11
Row8
ida: f1
ida: f2
id: f3
id: f4
id: f5
id: f6
id: f7
id: f8
id: f9
id: f10
ida: f11
Row9
ida: g1
ida: g2
id: g3
id: g4
id: g5
id: g6
id: g7
id: g8
id: g9
id: g10
ida: g11
Row10
ida: h1
ida: h2
id: h3
id: h4
id: h5
id: h6
id: h7
id: h8
id: h9
id: h10
ida: h11
Row11
ida: i1
ida: i2
id: i3
id: i4
id: i5
id: i6
id: i7
id: i8
id: i9
id: i10
ida: i11
Row12
ida: j1
ida: j2
id: j3
id: j4
id: j5
id: j6
id: j7
id: j8
id: j9
id: j10
ida: j11
Java (implemented in Iterator)
javaExcelIterator
import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
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;
/**
*Get the value of Excel cell(Iterator)
*
*/
public class GetOutExcel {
public static final String SAMPLE_XLSX_FILE_PATH = "D:\\filesample\\sampleBook.xlsx";
public static void main(String[] args) throws IOException, InvalidFormatException {
// Creating a Workbook from an Excel file (.xls or .xlsx)
Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));
// Retrieving the number of sheets in the Workbook
System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");
// 1. You can obtain a sheetIterator and iterate over it
Iterator<Sheet> sheetIterator = workbook.sheetIterator();
System.out.println("Retrieving Sheets using Iterator");
while (sheetIterator.hasNext()) {
Sheet sheet = sheetIterator.next();
System.out.println("=> " + sheet.getSheetName());
}
// Getting the Sheet at index zero
Sheet sheet = workbook.getSheetAt(0);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
//besides String
GetOutExcel aaa = new GetOutExcel();
// 1. You can obtain a rowIterator and columnIterator and iterate over them
System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
Iterator<Row> rowIterator = sheet.rowIterator();
int num = 0;
int num2 = 0;
String head;
while (rowIterator.hasNext()) {
num ++;
Row row = rowIterator.next();
// Now let's iterate over the columns of the current row
Iterator<Cell> cellIterator = row.cellIterator();
System.out.print("column"+ num +": ");
while (cellIterator.hasNext()) {
if(num2 >= 10) {
num2 -= 10;
}
num2 ++;
Cell cell = cellIterator.next();
// String cellValue = dataFormatter.formatCellValue(cell);
// System.out.print(cellValue + "\t");
// System.out.print("column" + num2 + ": " + cellValue + "\r\n");
//besides String
aaa.printCellValue(cell);
}
System.out.println();
}
// Closing the workbook
workbook.close();
}
//besides String
private static void printCellValue(Cell cell) {
DateFormat fd = DateFormat.getDateInstance(DateFormat.FULL);
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case STRING:
System.out.print(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date kkk = cell.getDateCellValue();
System.out.print(fd.format(kkk));
} else {
System.out.print(cell.getNumericCellValue());
}
break;
case FORMULA:
System.out.print(cell.getCellFormula());
break;
case BLANK:
System.out.print("");
break;
default:
System.out.print("");
}
System.out.print("\t");
}
}
Execution result (Iterator)
Workbook has 1 Sheets :
Retrieving Sheets using Iterator
=> Sheet1
Iterating over Rows and Columns using Iterator
column1: xx xx xx xx xx xx xx xx xx xx xx xx xx
column2: ida ida id id id id id id id id ida ida ida
column3: a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13
column4: b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 b11 b12 b13
column5: c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13
column6: d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 d11 d12 d13
column7: e1 e2 e3 e4 e5 e6 e7 e8 e9 e10 e11 e12 e13
column8: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13
column9: g1 g2 g3 g4 g5 g6 g7 g8 g9 g10 g11 g12 g13
column10: h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13
column11: i1 i2 i3 i4 i5 i6 i7 i8 i9 i10 i11 i12 i13
column12: j1 j2 j3 j4 j5 j6 j7 j8 j9 j10 j11 j12 j13
column13: k1 k2 k3 k4 k5 k6 k7 k8 k9 k10 k11 k12 k13
column14: l1 l2 l3 l4 l5 l6 l7 l8 l9 l10 l11 l12 l13
column15: m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12 m13
column16: n1 n2 n3 n4 n5 n6 n7 n8 n9 n10 n11 n12 n13
Recommended Posts