对excel的读写
关于java实现对excel的读写,大多数情况通过使用POI包中的方法来进行操作,本文将记录一些遇到的问题及想法。
代码
关于java对excel的操作,网上已是屡见不鲜,大致代码如下,这部分代码已经可以实现对excel的读取,并输出内容。
package Office;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class Excel {
public static void main(String[] args) {
//我就是随便起个名字
String filePath = "C:\\Users\\windows.???\\Desktop\\all测试.xlsx";
String columns[] = {"姓名", "学号", "班级"};
inputExcel(filePath,columns);
}
/**
* @param filePath Excel文件地址
* @param columns Excel文件第一行的列名
*/
public static void inputExcel(String filePath, String columns[]) {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
String cellData = null;
wb = readExcel(filePath);
if (wb != null) {
//获取第一个sheet
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
} else {
break;
}
list.add(map);
}
}
//遍历解析出来的list
for (Map<String, String> map : list) {
for (Map.Entry<String, String> entry : map.entrySet()) {
System.out.print(entry.getKey() + ":" + entry.getValue() + ",");
}
System.out.println();
System.out.println();
}
}
//读取excel
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
CellType cellTypeEnum = cell.getCellTypeEnum();
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
public static void outputExcel() throws IOException {
// 创建工作薄 xlsx
XSSFWorkbook xssWorkbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = xssWorkbook.createSheet("sheet1");
for (int row = 0; row < 10; row++) {
XSSFRow rows = sheet.createRow(row);
for (int col = 0; col < 10; col++) {
// 向工作表中添加数据
rows.createCell(col).setCellValue("data" + row + col);
}
}
//
File xlsFile = new File("poi.xlsx");
FileOutputStream xlsStream = null;
try {
xlsStream = new FileOutputStream(xlsFile);
xssWorkbook.write(xlsStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}
Q&A
上述代码是如何去除第一行的列名的呢?
若需要读取一份第一行没有列名的excel 改如何改动代码呢?
欢迎大家咏月评论
(我的一篇文章就这样是不是有点草率)