导入
package poi;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class JavaOpExcel {
public String getCellValue(XSSFCell cell){
if(cell.getCellType()==XSSFCell.CELL_TYPE_STRING)
return cell.getStringCellValue();
if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC)
return String.valueOf((int)cell.getNumericCellValue());
if(cell.getCellType()==XSSFCell.CELL_TYPE_BLANK)
return "";
return "";
}
//@Test
//"C:\\Users\\zjj/Desktop/workbook.xlsx"
public ArrayList<Map<String,Object>> readExcel(String path) {
try {
XSSFWorkbook workbook = new XSSFWorkbook(path);
XSSFCell cell;
ArrayList<Map<String,Object>> aList=new ArrayList<Map<String,Object>>();
//得到sheets的数量
int num = workbook.getNumberOfSheets();
System.out.println("sheets的数量是:"+num);
for (int i = 0; i < num; i++) {
//实例化XSSFSheet
XSSFSheet sheet = workbook.getSheetAt(i);
//得到第一行和最后一行
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
System.out.println("第一行:"+firstRowNum+1);
System.out.println("最后一行:"+lastRowNum+1);
//遍历行数
for (int j = firstRowNum+1; j <= lastRowNum; j++) {
Map<String,Object> map=new HashMap<String,Object>();
XSSFRow row = sheet.getRow(j);
//System.out.println("第"+row+"行");
//获取单元格
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
System.out.println("第一个单元格:"+firstCellNum);
System.out.println("最后一个单元格:"+lastCellNum);
//遍历一行,遍历每个单元格
for (int k = firstCellNum; k < lastCellNum; k++) {
cell = row.getCell(k);
System.out.println("cell:"+cell);
String str= getCellValue(cell);
System.out.println("单元格值是:"+str);
map.put("cell"+k, str);
System.out.print(str+"\t");
}
aList.add(map);
System.out.println();
}
}
System.out.println(aList);
return aList;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public void writeExcel() {
}
}
导出
package poi;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
public class JavaExportExcel {
@Test
public void writeExcel(String sheetName, String[] filedName, List<Map> list, String filePath) {
try {
// 创建一个文件输出流
FileOutputStream fileOut = new FileOutputStream(filePath);
Set set = list.get(0).keySet();
Workbook workbook = null;
// System.out.println(filePath.substring(filePath.lastIndexOf(".")));
// 判断文件类型
if (filePath.substring(filePath.lastIndexOf(".")).equals(".xls"))
workbook = new HSSFWorkbook();
else if (filePath.substring(filePath.lastIndexOf(".")).equals(".xlsx"))
workbook = new XSSFWorkbook();
else {
System.out.println("文件类型不匹配");
return;
}
//创建工作表单sheet
Sheet sheet = workbook.createSheet(sheetName);
//创建第一行(表头)
Row row = sheet.createRow(0);
//单元格(列)
Cell cell;
for (int i = 0; i < filedName.length; i++) {
cell = row.createCell(i);
cell.setCellValue(filedName[i]);
}
//创建行
for (int i = 1; i <= list.size(); i++) {
row = sheet.createRow(i);
//迭代集合
Iterator iterator = set.iterator();
int j = 0;
while (iterator.hasNext()) {
String key = (String) iterator.next();
cell = row.createCell(j++);
if (list.get(i - 1).get(key) != null)
cell.setCellValue(list.get(i - 1).get(key).toString());
else
cell.setCellValue("");
}
}
workbook.write(fileOut);
workbook.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}