導(dǎo)入poi所需的jar包
poi所需jar
HSSF對(duì)xls后綴名的Excel進(jìn)行讀取內(nèi)容:
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;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
/**
* Created by M_WBCG on 2017/7/14.
*/
public class ReadExcelForHSSF {
public void read() {
File file = new File("././POI/POI1.xls");
if (!file.exists())
System.out.println("文件不存在");
try {
//1.讀取Excel的對(duì)象
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
//2.Excel工作薄對(duì)象
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
//3.Excel工作表對(duì)象
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
//獲取sheet數(shù)量
int sheetNumber = hssfWorkbook.getNumberOfSheets();
//總行數(shù)
int rowLength = hssfSheet.getLastRowNum()+1;
//4.得到Excel工作表的行
HSSFRow hssfRow = hssfSheet.getRow(0);
//總列數(shù)
int colLength = hssfRow.getLastCellNum();
//得到Excel指定單元格中的內(nèi)容
HSSFCell hssfCell = hssfRow.getCell(0);
//得到單元格樣式
CellStyle cellStyle = hssfCell.getCellStyle();
for (int i = 0; i < rowLength; i++) {
//獲取Excel工作表的行
HSSFRow hssfRow1 = hssfSheet.getRow(i);
for (int j = 0; j < colLength; j++) {
//獲取指定單元格
HSSFCell hssfCell1 = hssfRow1.getCell(j)；
//單元格有數(shù)據(jù)就取出來，沒有數(shù)據(jù)也不讓其為空，因?yàn)闆]數(shù)據(jù)是空，那對(duì)其不能操作
Row.MissingCellPolicy RETURN_NULL_AND_BLANK//如果單元格為null，則為null
Row.MissingCellPolicy RETURN_BLANK_AS_NULL//如果單元格為null，則為null
Row.MissingCellPolicy CREATE_NULL_AS_BLANK//如果單元格為null，則轉(zhuǎn)為""
//Excel數(shù)據(jù)Cell有不同的類型，當(dāng)我們?cè)噲D從一個(gè)數(shù)字類型的Cell讀取出一個(gè)字符串時(shí)就有可能報(bào)異常：
//Cannot get a STRING value from a NUMERIC cell
//將所有的需要讀的Cell表格設(shè)置為String格式
if (hssfCell1 != null) {
hssfCell1.setCellType(Cell.CELL_TYPE_STRING);
}
//獲取每一列中的值
System.out.print(hssfCell1.getStringCellValue() + "\t");
}
System.out.println();
}
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new ReadExcelForHSSF().read();
}
}
XSSF對(duì)xlsx后綴名的Excel進(jìn)行讀取內(nèi)容：
import org.apache.poi.ss.usermodel.*;
import java.io.*;
/**
* Created by M_WBCG on 2017/7/14.
*/
public class ReadExcelForXSSF {
public void read() {
File file = new File("././POI/POI2.xlsx");
InputStream inputStream = null;
Workbook workbook = null;
try {
inputStream = new FileInputStream(file);
workbook = WorkbookFactory.create(inputStream);
inputStream.close();
//工作表對(duì)象
Sheet sheet = workbook.getSheetAt(0);
//總行數(shù)
int rowLength = sheet.getLastRowNum()+1;
//工作表的列
Row row = sheet.getRow(0);
//總列數(shù)
int colLength = row.getLastCellNum();
//得到指定的單元格
Cell cell = row.getCell(0);
//得到單元格樣式
CellStyle cellStyle = cell.getCellStyle();
System.out.println("行數(shù)：" + rowLength + ",列數(shù)：" + colLength);
for (int i = 0; i < rowLength; i++) {
row = sheet.getRow(i);
for (int j = 0; j < colLength; j++) {
cell = row.getCell(j);
//Excel數(shù)據(jù)Cell有不同的類型，當(dāng)我們?cè)噲D從一個(gè)數(shù)字類型的Cell讀取出一個(gè)字符串時(shí)就有可能報(bào)異常：
//Cannot get a STRING value from a NUMERIC cell
//將所有的需要讀的Cell表格設(shè)置為String格式
if (cell != null)
cell.setCellType(Cell.CELL_TYPE_STRING);
//對(duì)Excel進(jìn)行修改
if (i > 0 && j == 1)
cell.setCellValue("1000");
System.out.print(cell.getStringCellValue() + "\t");
}
System.out.println();
}
//將修改好的數(shù)據(jù)保存
OutputStream out = new FileOutputStream(file);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new ReadExcelForXSSF().read();
}
}
Excel寫入內(nèi)容使用的XSSF
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
/**
* Created by M_WBCG on 2017/7/14.
*/
public class WriteExcelForXSSF {
public void write() {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("0");
Row row = sheet.createRow(0);
CellStyle cellStyle = workbook.createCellStyle();
// 設(shè)置這些樣式
cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
row.createCell(0).setCellStyle(cellStyle);
row.createCell(0).setCellValue("姓名");
row.createCell(1).setCellStyle(cellStyle);
row.createCell(1).setCellValue("年齡");
workbook.setSheetName(0, "信息");
try {
File file = new File("././POI/POI3.xlsx");
FileOutputStream fileoutputStream = new FileOutputStream(file);
workbook.write(fileoutputStream);
fileoutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new WriteExcelForXSSF().write();
}
}
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。
