Java将一个excel拆分为多个excel
package hotelPro;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
public class WriteExcel {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static void writeExcel(List<List<String>> dataList, int cloumnCount, String finalXlsxPath) {
OutputStream out = null;
try {
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = (0);
/**
* 删除原有数据，除了属性列
*/
int rowNumber = (); // 第一行从0开始算
("原始数据总行数，除属性列：" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = (i);
(row);
}
// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
(out);
/**
* 往Excel中写新数据
*/
for (int j = 0; j < (); j++) {
// 创建一行：从第二行开始，跳过属性列
Row row = (j + 1);
// 得到要插入的每一条记录
List<String> dataMap = (j);
String cell1 = (0).toString();
String cell2 = (1).toString();
String cell3 = (2).toString();
String cell4 = (3).toString();
String cell5 = (4).toString();
String cell6 = (5).toString();
String cell7 = (6).toString();
// for (int k = 0; k <= columnNumCount; k++) {
// 在一行内循环
Cell first = (0);
(cell1);
Cell second = (1);
(cell2);
Cell third = (2);
(cell3);
Cell four = (3);
(cell4);
Cell five = (4);
(cell5);
Cell six = (5);
(cell6);
Cell seven = (6);
(cell7);
// }
}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
(out);
} catch (Exception e) {
();
} finally {
try {
if (out != null) {
();
();
}
} catch (IOException e) {
();
}
}
("数据导出成功");
}
/**
* 判断Excel的版本,获取Workbook
*
* @param in
* @param filename
* @return
* @throws IOException
*/
public static Workbook getWorkbok(File file) throws IOException {
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if (().endsWith(EXCEL_XLS)) { // Excel 2003
wb = new HSSFWorkbook(in);
} else if (().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
/**
* 根据fileType不同读取excel文件
*
* @param path
* @param path
* @throws IOException
*/
@SuppressWarnings({ "resource", "deprecation" })
public static List<List<String>> readExcel(String path) {
String fileType = ((".") + 1);
// return a list contains many list
List<List<String>> lists = new ArrayList<List<String>>();
// 读取excel文件
InputStream is = null;
try {
is = new FileInputStream(path);
// 获取工作薄
Workbook wb = null;
if (("xls")) {
wb = new HSSFWorkbook(is);
} else if (("xlsx")) {
wb = new XSSFWorkbook(is);
} else {
return null;
}
// 读取第一个工作页sheet
Sheet sheet = (0);
// 第一行为标题
for (Row row : sheet) {
ArrayList<String> list = new ArrayList<String>();
for (Cell cell : row) {
// 根据不同类型转化成字符串
(Cell.CELL_TYPE_STRING);
(());
}
(list);
}
} catch (IOException e) {
();
} finally {
try {
if (is != null)
();
} catch (IOException e) {
();
}
}
return lists;
}
public static void main(String[] args) throws IOException {
String path = "F:\\Data\\";
List<List<String>> lists = readExcel(path);
int size = ();
//每个一万条
int a = size / 10000;
for (int i = 0; i < a + 1; i++) {
("---------------------------" + i);
List<List<String>> list;
if (i == a) {
list = (i * 1000, size+a);
} else {
list = (i * 1000, (i + 1) * 1000);
}
(0, (0));
String p = "split_" + i + ".xlsx";
(list, (), "F:\\Data\\" + p);
}
("-------------");
}
}