用前须知
1. 导包
- JXL:只能解析xls
- POI:可以解析xlsx和office文档
//JXL按行读取xls文件
package basc;
import java.io.File;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class Read_Row {
public static void main(String[] args) throws Exception{
Workbook wb = Workbook.getWorkbook(new File("test.xls"));
Sheet sheet = wb.getSheet(0);
int columns = sheet.getRows();
for(int i=0;i<columns;i++){
Cell[] cells = sheet.getRow(i);
for(Cell cell:cells){
System.out.print(cell.getContents()+"\t");
}
System.out.println("");
}
}
}
//JXL按列读取xls文件
package basc;
import java.io.File;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class Read_Column {
public static void main(String[] args)throws Exception {
Workbook wb = Workbook.getWorkbook(new File("test.xls"));
Sheet sheet = wb.getSheet(0);
int columns = sheet.getColumns();
for(int i=0;i<columns;i++){
Cell[] cells = sheet.getColumn(i);
for(Cell cell:cells){
System.out.print(cell.getContents()+"\t");
}
System.out.println("");
}
}
}
//JXL新建Excel并写入数据
package basc;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class Write {
public static void main(String[] args) throws IOException, WriteException {
//1.构造workbook对象
WritableWorkbook wwb = Workbook.createWorkbook(new File("employee.xls"));
//2.添加sheet
WritableSheet ws = wwb.createSheet("employee", 0);
//3.构造单元格
Label id = new Label(0,0,"id");
Label name = new Label(1,0,"name");
Label sal = new Label(2,0,"sal");
Label vid = new Label(0,1,"001");
Label vname = new Label(1,1,"王凯");
Label vsal = new Label(2,1,"2000");
//4.添加
ws.addCell(id);
ws.addCell(name);
ws.addCell(sal);
ws.addCell(vid);
ws.addCell(vname);
ws.addCell(vsal);
//5.写出到文件
wwb.write();
wwb.close();
}
}
//POI读取
package basc;
import java.io.File;
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;
public class ReadExcelPoI {
public static void main(String[] args)throws Exception {
//1.构造方法
Workbook wb = new XSSFWorkbook(new File("test.xlsx"));
//2.获得sheet
Sheet sheet = wb.getSheetAt(1);
//3.获得范围
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
for(int i = firstRow;i<=lastRow;i++){
//4.获得某一行
Row row = sheet.getRow(i);
int firstCell = row.getFirstCellNum();
int lastCell = row.getLastCellNum();
for(int k=firstCell;k<lastCell;k++){
//6.得到单元格
Cell cell = row.getCell(k);
if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
System.out.print(cell.getNumericCellValue()+"\t");
}else{
System.out.print(cell.getStringCellValue()+"\t");
}
}
System.out.println("");
}
wb.close();
}
}
//POI写入
package basc;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
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 WriteExcelPOI {
public static void main(String[] args) throws Exception{
//1.构造workbook对象
Workbook wb = new XSSFWorkbook();
//2.添加sheet
Sheet sheet = wb.createSheet("employee");
//3.创建行
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("id");
row.createCell(1).setCellValue("name");
row.createCell(2).setCellValue("isal");
Row row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("001");
row1.createCell(1).setCellValue("张三");
row1.createCell(2).setCellValue("123.321");
OutputStream os = new FileOutputStream(new File("emp.xlsx"));
wb.write(os);
wb.close();
}
}
夏目漱石:今晚月色很美
王家卫:那一刻,我很暖
归有光:挺有枇杷树,吾妻死之年所手植矣,今已亭亭如盖矣
李宗盛:春风再美也比不上你的美,没见过你的人不会明了
冯唐:春水初生,春林初盛,春风十里,不如你
王小波:你好哇,李银河,见到你真高兴