03版Excel 导出
package com.ning;
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;
import org.joda.time.DateTime;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest03 {
public void test03() throws IOException {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("学员统计表");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell4 = row.createCell(2);
cell.setCellValue("姓名");
cell1.setCellValue("年龄");
cell4.setCellValue("入学时间");
Row row1 = sheet.createRow(1);
Cell cell2 = row1.createCell(0);
Cell cell3 = row1.createCell(1);
Cell cell5 = row1.createCell(2);
cell2.setCellValue("杨先生");
cell3.setCellValue("18");
String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell5.setCellValue(s);
FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "学员统计表03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
}
public void ExcelWriteTest03BigData() throws IOException {
long begin = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("big");
for (int i = 0; i < 65536; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "big03.xls");
workbook.write(fileOutputStream);
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);
}
public static void main(String[] args) throws IOException {
new ExcelWriteTest03().ExcelWriteTest03BigData();
}
}
07版Excel 写出
package com.ning;
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.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest07 {
public void test03() throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("学员统计表");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell4 = row.createCell(2);
cell.setCellValue("姓名");
cell1.setCellValue("年龄");
cell4.setCellValue("入学时间");
Row row1 = sheet.createRow(1);
Cell cell2 = row1.createCell(0);
Cell cell3 = row1.createCell(1);
Cell cell5 = row1.createCell(2);
cell2.setCellValue("杨先生");
cell3.setCellValue("18");
String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell5.setCellValue(s);
FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "学员统计表03.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
}
public void ExcelWriteTest07BigData() throws IOException {
long begin = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("big");
for (int i = 0; i < 65536; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "big03.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);
}
public static void main(String[] args) throws IOException {
new ExcelWriteTest07().ExcelWriteTest07BigData();
}
}
导入
public void ReaderTest07() throws IOException {
FileInputStream fileInputStream = new FileInputStream(path + "学员统计表03.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);
fileInputStream.close();
}
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;
public class ExcelReaderTypeTest {
private String path = "D:\\file\\";
@Test
public void ReaderCellType(FileInputStream fileInputStream) throws IOException {
fileInputStream = new FileInputStream(path + "学员统计表03.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
for (int i = 0; i < cells; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
int cellType = cell.getCellType();
String stringCellValue = cell.getStringCellValue();
System.out.print(stringCellValue + "|");
}
}
System.out.println();
}
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
Row row1 = sheet.getRow(i);
if (row1 != null) {
int cells = row1.getPhysicalNumberOfCells();
for (int j = 0; j < cells; j++) {
Cell cell = row1.getCell(j);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = null;
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
System.out.println("[字符串]");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println("[布尔]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
System.out.println("[空]");
break;
case HSSFCell.CELL_TYPE_NUMERIC://(日期,数字)
System.out.println("[数字]");
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.println("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
}
Excel 函数
public void test() throws IOException{
FileInputStream fileInputStream = new FileInputStream(path + "学员统计表03.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheetAt = workbook.getSheetAt(0);
Row row = sheetAt.getRow(0);
Cell cell = row.getCell(3);
FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA:
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
CellValue evaluate = FormulaEvaluator.evaluate(cell);
String s = evaluate.toString();
System.out.println(s);
break;
}
fileInputStream.close();
}