如何把页面上的table数据导入到Excel中

Question

如题所述

Answer 1

var elTable = document.getElementByIdx_x_x("tb");
//tb为页面上table的id
var oRangeRef = document.body.createTextRange();
oRangeRef.moveToElementText( elTable );
oRangeRef.execCommand( "Copy" );
var appExcel = new ActiveXObject( "Excel.Application" );
appExcel.Visible = true;
appExcel.Workbooks.Add().Worksheets.Item(1).Paste();
appExcel = null;
将以上JS代码放到一个function中，利用一个按钮触发，运行时可能会出现“Automation
服务器不能创建对象”错误，解决方法为：打开打开Internet Explorer
工具->选项->安全->自定义级别，将第三项“对没有标记为安全的activex控件进行初始化和脚本运行”设置成“启用”即可。

我是这样做的：

File file = new File("C:\\Documents and
Settings\\Administrator\\桌面\\物流报表");
if(!file.exists()){//如果不存在该文件夹
file.mkdir();//新建一个
}
String fileName = "C:/Documents
and Settings/Administrator/桌面/物流报表/报表" + time + ".xls";
// String fileName =
"C://apache-tomcat-6.0.29";
WritableWorkbook book =
Workbook.createWorkbook(new
File(fileName));
WritableSheet sheet =
book.createSheet("报表", 0);
//设置Excel标题字体
jxl.write.WritableFont wfc1 =
new jxl.write.WritableFont(WritableFont.ARIAL, 20,
WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLUE);
jxl.write.WritableCellFormat
wcfFC1 = new
jxl.write.WritableCellFormat(wfc1);

jxl.write.Label labelB10 = new
jxl.write.Label(2, 0, "物流" + datas[1] + "度报表", wcfFC1);
//设置Excel列标题字体
jxl.write.WritableFont wfc2 =
new jxl.write.WritableFont(WritableFont.ARIAL, 13,
WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
jxl.write.WritableCellFormat
wcfFC2 = new jxl.write.WritableCellFormat(wfc2);
jxl.write.Label labelB1 = new
jxl.write.Label(0, 1, "配送点", wcfFC2);
jxl.write.Label labelB2 = new
jxl.write.Label(1, 1, "报表时间", wcfFC2);
jxl.write.Label labelB3 = new
jxl.write.Label(2, 1, "报表生成时间", wcfFC2);
jxl.write.Label labelB4 = new
jxl.write.Label(3, 1, "发货数量", wcfFC2);
jxl.write.Label labelB5 = new
jxl.write.Label(4, 1, "送货数量", wcfFC2);
jxl.write.Label labelB6 = new
jxl.write.Label(5, 1, "总数量", wcfFC2);
jxl.write.Label labelB7 = new
jxl.write.Label(6, 1, "发货收入", wcfFC2);
jxl.write.Label labelB8 = new
jxl.write.Label(7, 1, "送货收入", wcfFC2);
jxl.write.Label labelB9 = new
jxl.write.Label(8, 1, "本年度总收入", wcfFC2);
//设置表格里的数据字体
jxl.write.WritableFont wfc3 =
new jxl.write.WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat
wcfFC3 = new jxl.write.WritableCellFormat(wfc3);
jxl.write.Label label =
null;
try {
sheet.addCell(labelB1);
sheet.addCell(labelB2);
sheet.addCell(labelB3);
sheet.addCell(labelB4);
sheet.addCell(labelB5);
sheet.addCell(labelB6);
sheet.addCell(labelB7);
sheet.addCell(labelB8);
sheet.addCell(labelB9);
sheet.addCell(labelB10);
int j =
0;//控制行数
for(int i =
0;i < datas.length;i++){
if(i%9
== 0){
j++;
}
label
= new jxl.write.Label(i%9,j+1,datas[i],wcfFC3);
sheet.addCell(label);
}

book.write();
book.close();
}catch (Exception e) {
e.printStackTrace();
}

Answer 2

DataTable.ImportSheet(FileName,SheetSource,SheetDest)
DataTable.ExportSheet(FileName,DTSheet)
用上述的方法，在脚本中导入导出excel中的数据。对应的是文件名，文件路径，还有excel的sheet名。