67,513
社区成员
发帖
与我相关
我的任务
分享
读取文件:
/**
* 读取xls文件到set
*
* @throws Exception
*/
public static Set<String> readXlsfile(File filePath) {
Set<String> set = new HashSet<String>();
try {
// long l = System.currentTimeMillis();
InputStream is = new FileInputStream(filePath);
Workbook rwb = Workbook.getWorkbook(is);
Sheet[] sheets = rwb.getSheets();// 获得当前Excel表共有几个sheet
int p = sheets.length;
// System.out.println("此表共有几个sheet------->" + p);
for (int w = 0; w < p; w++) {
// 将每个sheet中的内容全部读取出来
Sheet rs = rwb.getSheet(w);
int rows = rs.getRows();
for (int d = 1; d < rows; d++) {
// 行循环,Excel的行列是从(0,0)开始的
Cell b00 = rs.getCell(0, d);
String strb = b00.getContents();
set.add(strb);
}
}
// System.out.println("mapping执行之间:" + (System.currentTimeMillis() - l) + "毫秒");
System.out.println("长度" + set.size());
} catch (Exception e) {
System.out.println(e.getMessage());
}
return set;
}
写入文件:
/**
* 将数据写入xls文件中,并下载到本地
*/
public static void writeXlsFile(List<String> set, File file, String fileName, HttpServletResponse
response) throws Exception {
if (Utils.isNull(set))
return;
OutputStream ot = new FileOutputStream(file);
// 创建excel文件
WritableWorkbook wwb = Workbook.createWorkbook(ot);
// 设置表头字体
WritableFont wtitlefont = new WritableFont(WritableFont.TIMES, 11,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
WritableCellFormat titleformat = new WritableCellFormat(wtitlefont);
// WritableSheet sheet = wwb.createSheet("测试大数据量", 0);
String[] titles = new String[] { "电话号码" };
int setIndex = 0;
int cellNumber = 60000;
int setSize = set.size();
int setNum = setSize / 60000;
if (setSize % 60000 != 0)
setNum = setNum + 1;
for (int num = 0; num < setNum; num++) {
String sheetName = "收信人" + String.valueOf(num + 1);
WritableSheet sheet = wwb.createSheet(sheetName, 0);
// 设置excel表头
int row = 0;
int column = 0;
for (String title : titles) {
Label label0 = new Label(column, row, title, titleformat);
sheet.addCell(label0);
column++;
}
// 写入正文
if (num == (setNum - 1)) {
cellNumber = setSize - (num * 60000);
}
SetSheetCell(sheet, set, cellNumber, setIndex);
setIndex = setIndex + cellNumber;
}
// 提交写入数据并关闭
wwb.write();
wwb.close();
ot.close();
// 下载文件
try {
fileName = "new_noblack_" + fileName;
download(file, fileName, response);
} catch (Exception e) {
// TODO Auto-generated catch block
// e.printStackTrace();
System.out.println("下载文件异常" + e.getMessage());
}
}