/**
* @param src 源文件
* @param dst 目标位置
*/
private static void copy(File src, File dst) {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(src), BUFFER_SIZE);
out = new BufferedOutputStream(new FileOutputStream(dst), BUFFER_SIZE);
byte[] buffer = new byte[BUFFER_SIZE];
int len = 0;
while ((len = in.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != out) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
@Test
public void test_writeFile() {
String path = "D:\\test.xlsx";
// String path = "D:\\test.xls";
// String path = "D:/";
// String path = "D:\\";
long startTime = System.currentTimeMillis();
try {
ExcelUtil.writeFile(path);
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("cost :" + (System.currentTimeMillis() - startTime));
}
/**
* 读取97-2003格式
*
* @param filePath 文件路径
* @throws java.io.IOException
*/
@SuppressWarnings("rawtypes")
public static List<Map> readExcel2003(String filePath) throws IOException {
// 返回结果集
List<Map> valueList = new ArrayList<Map>();
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用
HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中，第一张工作表的缺省索引是0
int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数
Map<Integer, String> keys = new HashMap<Integer, String>();
int cells = 0;
// 遍历行（第1行 表头） 准备Map里的key
HSSFRow firstRow = sheet.getRow(0);
if (firstRow != null) {
// 获取到Excel文件中的所有的列
cells = firstRow.getPhysicalNumberOfCells();
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
try {
HSSFCell cell = firstRow.getCell(j);
String cellValue = getCellValue(cell);
keys.put(j, cellValue);
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 遍历行（从第二行开始）
for (int i = 1; i < rows; i++) {
// 读取左上端单元格(从第二行开始)
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
// 准备当前行 所储存值的map
Map<String, Object> val = new HashMap<String, Object>();
boolean isValidRow = false;
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
try {
HSSFCell cell = row.getCell(j);
String cellValue = getCellValue(cell);
val.put(keys.get(j), cellValue);
if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
isValidRow = true;
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 第I行所有的列数据读取完毕，放入valuelist
if (isValidRow) {
valueList.add(val);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
fis.close();
}
return valueList;
}
/**
* 读取2007-2013格式
*
* @param filePath 文件路径
* @return
* @throws java.io.IOException
*/
@SuppressWarnings("rawtypes")
public static List<Map> readExcel2007(String filePath) throws IOException {
List<Map> valueList = new ArrayList<Map>();
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFSheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
// 定义 row、cell
XSSFRow row;
// 循环输出表格中的第一行内容 表头
Map<Integer, String> keys = new HashMap<Integer, String>();
row = sheet.getRow(0);
if (row != null) {
// System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
// System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
// 通过 row.getCell(j).toString() 获取单元格内容，
if (row.getCell(j) != null) {
if (!row.getCell(j).toString().isEmpty()) {
keys.put(j, row.getCell(j).toString());
}
} else {
keys.put(j, "K-R1C" + j + "E");
}
}
}
// 循环输出表格中的从第二行开始内容
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row != null) {
boolean isValidRow = false;
Map<String, Object> val = new HashMap<String, Object>();
for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
XSSFCell cell = row.getCell(j);
if (cell != null) {
String cellValue = null;
if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
cellValue =
new DataFormatter()
.formatRawCellContents(
cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
} else {
cellValue = String.valueOf(cell.getNumericCellValue());
}
} else {
cellValue = cell.toString();
}
if (cellValue != null && cellValue.trim().length() <= 0) {
cellValue = null;
}
val.put(keys.get(j), cellValue);
if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
isValidRow = true;
}
}
}
// 第I行所有的列数据读取完毕，放入valuelist
if (isValidRow) {
valueList.add(val);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
fis.close();
}
return valueList;
}