这一段时间搞报表，头都搞晕了，最后还是整理了一下
在javascript里面导出Excel
这个是基本方法
function toExcel(){
var docWin=window.open("","_blank","left=-1,top=-1,width=100,height=100,resizable=no");
var AllData=document.getElementById("AllData");//"alldata"为一个表格或一个层(最好)的名字
docWin.document.write(AllData.innerHTML);
docWin.document.execCommand("SelectAll",false);
docWin.document.execCommand("Copy",false);
docWin.close();
var objExcelApp=new ActiveXObject("Excel.Application");
if (objExcelApp==null){
alert("你的机器上没有安装Excel软件");
}else{
var erl="";
try{
objExcelApp.WindowState = 1;
objExcelApp.Visible = 1;
var objWorkBook=objExcelApp.Workbooks.Add();
erl="Create WorkBook";
var objSheet=objWorkBook.ActiveSheet;
erl="Get ActiveSheet";
objSheet.Columns("A").columnwidth="5";
objSheet.Columns("B").columnwidth="5";
objSheet.Columns("C").columnwidth="15";
objSheet.Columns("D").columnwidth="15";
objSheet.Columns("E").columnwidth="35";
objSheet.Columns("F").columnwidth="15";
objSheet.Columns("G").columnwidth="5";
objSheet.Columns("H").columnwidth="25";
objSheet.Paste();
erl="Copy";
// 保存表格。
//objSheet.SaveAs("C://TEST.XLS");
// 用 Application 对象用 Quit 方法关闭 Excel。
/objSheet.Application.Quit();
}catch(e){
alert("对Excel操作时出错了:"+erl);
}
}
}
objSheet.cells.rang['A3','B3'].merge('true');可以合并两列
objSheet.cells.rang['A3','B3'].merge(False);可以合并两行
下面这些可以对Excel对象的属性进行操作
1.创建一个新Excel表格
var XLObj = new ActiveXObject("Excel.Application");
var xlBook = XLObj.Workbooks.Add; //新增工作簿
var ExcelSheet = xlBook.Worksheets(1); //创建工作表
2.保存表格
ExcelSheet.SaveAs("C://TEST.XLS");
3.使 Excel 通过 Application 对象可见
ExcelSheet.Application.Visible = true;
4.打印
xlBook.PrintOut;
或者:
ExcelSheet.PrintOut;
5.关闭
xlBook.Close(savechanges=false);
或者:
ExcelSheet.Close(savechanges=false);
6.结束进程
ExcelSheet.Application.Quit();
或者:
XLObj.Quit();
XLObj=null;
7.页面设置
ExcelSheet.ActiveSheet.PageSetup.LeftMargin= 2/0.035; //页边距 左2厘米
ExcelSheet.ActiveSheet.PageSetup.RightMargin = 3/0.035; //页边距 右3厘米，
ExcelSheet.ActiveSheet.PageSetup.TopMargin = 4/0.035; //页边距 上4厘米，
ExcelSheet.ActiveSheet.PageSetup.BottomMargin = 5/0.035; //页边距 下5厘米
ExcelSheet.ActiveSheet.PageSetup.HeaderMargin = 1/0.035; //页边距 页眉1厘米
ExcelSheet.ActiveSheet.PageSetup.FooterMargin = 2/0.035; //页边距 页脚2厘米
ExcelSheet.ActiveSheet.PageSetup.CenterHeader = "页眉中部内容";
ExcelSheet.ActiveSheet.PageSetup.LeftHeader = "页眉左部内容";
ExcelSheet.ActiveSheet.PageSetup.RightHeader = "页眉右部内容";
ExcelSheet.ActiveSheet.PageSetup.CenterFooter = "页脚中部内容";
ExcelSheet.ActiveSheet.PageSetup.LeftFooter = "页脚左部内容";
ExcelSheet.ActiveSheet.PageSetup.RightFooter = "页脚右部内容";
8.对单元格操作，带*部分对于行，列，区域都有相应属性
ExcelSheet.ActiveSheet.Cells(row,col).Value = "内容"; //设置单元格内容
ExcelSheet.ActiveSheet.Cells(row,col).Borders.Weight = 1; //设置单元格边框*()
ExcelSheet.ActiveSheet.Cells(row,col).Interior.ColorIndex = 1; //设置单元格底色*(1-黑色，
2-白色，3-红色，4-绿色，5-蓝色，6-黄色，7-粉红色，8-天蓝色，9-酱土色..可以多做尝试)
ExcelSheet.ActiveSheet.Cells(row,col).Interior.Pattern = 1; //设置单元格背景样式*(1-无，
2-细网格，3-粗网格，4-斑点，5-横线，6-竖线..可以多做尝试)
ExcelSheet.ActiveSheet.Cells(row,col).Font.ColorIndex = 1; //设置字体颜色*(与上相同)
ExcelSheet.ActiveSheet.Cells(row,col).Font.Size = 10; //设置为10号字*
ExcelSheet.ActiveSheet.Cells(row,col).Font.Name = "黑体"; //设置为黑体*
ExcelSheet.ActiveSheet.Cells(row,col).Font.Italic = true; //设置为斜体*
ExcelSheet.ActiveSheet.Cells(row,col).Font.Bold = true; //设置为粗体*
ExcelSheet.ActiveSheet.Cells(row,col).ClearContents; //清除内容*
ExcelSheet.ActiveSheet.Cells(row,col).WrapText=true; //设置为自动换行*
ExcelSheet.ActiveSheet.Cells(row,col).HorizontalAlignment = 3; //水平对齐方式枚举* (1-常规，
2-靠左，3-居中，4-靠右，5-填充 6-两端对齐，7-跨列居中，8-分散对齐)
ExcelSheet.ActiveSheet.Cells(row,col).VerticalAlignment = 2; //垂直对齐方式枚举*(1-靠上，
2-居中，3-靠下，4-两端对齐，5-分散对齐)
//行，列有相应操作:
ExcelSheet.ActiveSheet.Rows(row).
ExcelSheet.ActiveSheet.Columns(col).
ExcelSheet.ActiveSheet.Rows(startrow+":"+endrow). //如Rows("1:5")即1到5行
ExcelSheet.ActiveSheet.Columns(startcol+":"+endcol). //如Columns("1:5")即1到5列
//区域有相应操作:
XLObj.Range(startcell+":"+endcell).Select;
//如Range("A2:H8")即A列第2格至H列第8格的整个区域
XLObj.Selection.
//合并单元格
XLObj.Range(startcell+":"+endcell).MergeCells = true;
//如Range("A2:H8")即将A列第2格至H列第8格的整个区域合并为一个单元格
或者:
XLObj.Range("A2",XLObj.Cells(8, 8)).MergeCells = true;
9.设置行高与列宽
ExcelSheet.ActiveSheet.Columns(startcol+":"+endcol).ColumnWidth = 22;
//设置从firstcol到stopcol列的宽度为22
ExcelSheet.ActiveSheet.Rows(startrow+":"+endrow).RowHeight = 22;
//设置从firstrow到stoprow行的宽度为22
这里有一个在网上搜索的小实例。
<HTML>
<BODY>
<SCRIPT LANGUAGE="VBScript">
Function CreateNamesArray()
' Create an array to set multiple values at once.
Dim saNames(5, 2)
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
CreateNamesArray = saNames
End Function
</SCRIPT>
<SCRIPT LANGUAGE="JScript">
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
oXL.Visible = true;
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
// Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name";
oSheet.Cells(1, 2).Value = "Last Name";
oSheet.Cells(1, 3).Value = "Full Name";
oSheet.Cells(1, 4).Value = "Salary";
// Format A1:D1 as bold, vertical alignment = center.
oSheet.Range("A1", "D1").Font.Bold = true;
oSheet.Range("A1", "D1").VerticalAlignment = -4108; //xlVAlignCenter
// Create an array to set multiple values at once.
// Fill A2:B6 with an array of values (from VBScript).
oSheet.Range("A2", "B6").Value = CreateNamesArray();
// Fill C2:C6 with a relative formula (=A2 & " " & B2).
var oRng = oSheet.Range("C2", "C6");
oRng.Formula = "=A2 & /" /" & B2";
// Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
// AutoFit columns A:D.
oRng = oSheet.Range("A1", "D1");
oRng.EntireColumn.AutoFit();
// Manipulate a variable number of columns for Quarterly Sales Data.
DispalyQuarterlySales(oSheet);
//保存到本地路径
oSheet.SaveAs("C://TEST.XLS");
// Make sure Excel is visible and give the user control
// of Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
function DispalyQuarterlySales(oWS)
{
var iNumQtrs, sMsg, iRet;
// Number of quarters to display data for.
iNumQtrs = 4;
// Starting at E1, fill headers for the number of columns selected.
var oResizeRange = oWS.Range("E1", "E1").Resize(1,iNumQtrs);
oResizeRange.Formula = "=/"Q/" & COLUMN()-4 & CHAR(10) & /"Sales/"";
// Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true;
// Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36;
// Fill the columns with a formula and apply a number format.
oResizeRange = oWS.Range("E2", "E6").Resize(5,iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00";
// Apply borders to the Sales data and headers.
oResizeRange = oWS.Range("E1", "E6").Resize(6,iNumQtrs);
oResizeRange.Borders.Weight = 2; // xlThin
// Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.Range("E8", "E8").Resize(1,iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
// 9 = xlEdgeBottom
oResizeRange.Borders(9).LineStyle = -4119; //xlDouble
oResizeRange.Borders(9).Weight = 4; //xlThick
// Add a Chart for the selected data.
oResizeRange = oWS.Range("E2:E6").Resize(5,iNumQtrs);
var oChart = oWS.Parent.Charts.Add();
oChart.ChartWizard(oResizeRange, -4100, null, 2); // -4100 = xl3dColumn
oChart.SeriesCollection(1).XValues = oWS.Range("A2", "A6");
for (iRet = 1; iRet <= iNumQtrs; iRet++) {
oChart.SeriesCollection(iRet).Name = "=/"Q" + iRet + "/"";
}
oChart.Location(2, oWS.Name); // 2 = xlLocationAsObject
// Move the chart so as not to cover your data.
oWS.Shapes("Chart 1").Top = oWS.Rows(10).Top;
oWS.Shapes("Chart 1").Left = oWS.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="创建Excel表格和柱状图" οnclick="AutomateExcel"></P>
</BODY>
</HTML>
这里是在java类里面的方法
public void generateXls()
{
try
{
/** *//** **********创建工作簿************ */
WritableWorkbook workbook = Workbook.createWorkbook(new File("d:/test.xls"));
/** *//** **********创建工作表************ */
WritableSheet sheet = workbook.createSheet("工作表名称", 0);
/** *//** *********设置列宽**************** */
sheet.setColumnView(0, 15); // 第1列
sheet.setColumnView(1, 18); // 第2列
sheet.setColumnView(2, 13);
sheet.setColumnView(3, 13);
sheet.setColumnView(4, 15);
sheet.setColumnView(5, 15);
//设置行高
sheet.setRowView(0, 600, false);
sheet.setRowView(1, 400, false);
sheet.setRowView(7, 400, false);
//设置页边距
sheet.getSettings().setRightMargin(0.5);
//设置页脚
sheet.setFooter("", "", "测试页脚");
/** *//** ************设置单元格字体************** */
//字体
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 14,
WritableFont.BOLD);
WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.NO_BOLD);
WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD);
/** *//** ************以下设置几种格式的单元格************ */
// 用于标题
WritableCellFormat wcf_title = new WritableCellFormat(BoldFont);
wcf_title.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_title.setWrap(true); // 是否换行
// 用于表格标题
WritableCellFormat wcf_tabletitle = new WritableCellFormat(
tableFont);
wcf_tabletitle.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_tabletitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_tabletitle.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_tabletitle.setWrap(true); // 是否换行
// 用于正文左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_left.setAlignment(Alignment.LEFT);
wcf_left.setWrap(true); // 是否换行
// 用于正文左
WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_center.setAlignment(Alignment.CENTRE);
wcf_center.setWrap(true); // 是否换行
// 用于正文右
WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);
wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_right.setAlignment(Alignment.RIGHT);
wcf_right.setWrap(false); // 是否换行
// 用于跨行
WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont);
wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐
wcf_merge.setAlignment(Alignment.LEFT);
wcf_merge.setWrap(true); // 是否换行
WritableCellFormat wcf_table = new WritableCellFormat(NormalFont);
wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_table.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_table.setAlignment(Alignment.CENTRE);
wcf_table.setBackground(Colour.GRAY_25);
wcf_table.setWrap(true); // 是否换行
/** *//** ************单元格格式设置完成****************** */
//合并单元格,注意mergeCells(col0,row0,col1,row1) --列从0开始,col1为你要合并到第几列,行也一样
sheet.mergeCells(0, 0, 5, 0);
sheet.addCell(new Label(0, 0, "这里是大标题,自定义格式",
wcf_title));
sheet.mergeCells(0, 1, 1, 1);
sheet.mergeCells(2, 1, 5, 1);
sheet.addCell(new Label(0, 1, "", wcf_center));
sheet.addCell(new Label(2, 1, "姓名:" + "supercrsky",
wcf_center));
sheet.mergeCells(0, 2, 1, 2);
sheet.mergeCells(2, 2, 3, 2);
sheet.addCell(new Label(0, 2, "单位:", wcf_center));
sheet.addCell(new Label(2, 2, "ChinaLong", wcf_center));
sheet.addCell(new Label(4, 2, "薪水", wcf_center));
sheet.addCell(new Label(5, 2, "5000", wcf_center));
sheet.mergeCells(0, 3, 1, 3);
sheet.mergeCells(2, 3, 3, 3);
sheet.addCell(new Label(0, 3, "性别:", wcf_center));
sheet.addCell(new Label(2, 3, "男", wcf_center));
sheet.addCell(new Label(4, 3, "婚否:", wcf_center));
sheet.addCell(new Label(5, 3, "否", wcf_center));
sheet.mergeCells(0, 4, 1, 4);
sheet.mergeCells(2, 4, 3, 4);
sheet.addCell(new Label(0, 4, "是否在职:", wcf_center));
sheet.addCell(new Label(2, 4,"是",
wcf_center));
sheet.addCell(new Label(4, 4,"工作经验:", wcf_center));
sheet.addCell(new Label(5, 4, "4",wcf_center));
sheet.mergeCells(0, 5, 1, 5);
sheet.mergeCells(2, 5, 3, 5);
sheet.addCell(new Label(0, 5, "保险费:", wcf_center));
sheet.addCell(new Label(2, 5,"50",
wcf_center));
sheet.addCell(new Label(4, 5, "保险金额:", wcf_center));
sheet.addCell(new Label(5, 5, "50000",
wcf_center));
sheet.mergeCells(0, 6, 1, 6);
sheet.mergeCells(2, 6, 3, 6);
sheet.addCell(new Label(0, 6, "工作地点:", wcf_center));
sheet.addCell(new Label(2, 6, "北京", wcf_center));
sheet.addCell(new Label(4, 6, "开心度：", wcf_center));
sheet.addCell(new Label(5, 6, "一般", wcf_center));
// 另起一table
sheet.mergeCells(0, 7, 5, 7);
sheet.addCell(new Label(0, 7, "详细数据", wcf_tabletitle));
// table标题
sheet.addCell(new Label(0, 8, "序号", wcf_table));
sheet.addCell(new Label(1, 8, "姓名", wcf_table));
sheet.addCell(new Label(2, 8, "年龄", wcf_table));
sheet.addCell(new Label(3, 8, "性别", wcf_table));
sheet.addCell(new Label(4, 8, "婚否", wcf_table));
sheet.addCell(new Label(5, 8, "在职", wcf_table));
// table内容
//这里用你的dao
TestDAO dao = new TestDAO();
List list = dao.findBy(user.getUserId());
System.out.println("此保单拥有防疫码数量:" + list.size());
for (int i = 0; i < list.size(); i++)
{
//对应你的vo类
User data = (User) list.get(i);
sheet.addCell(new Label(0, 9 + i, String.valueOf(i + 1),
wcf_center));
sheet.addCell(new Label(1, 9 + i, data.getDlEPCode(),
wcf_center));
sheet
.addCell(new Label(2, 9 + i, data.getDlType(),
wcf_center));
sheet.addCell(new Label(3, 9 + i, String.valueOf(data
.getDlPigAge()), wcf_center));
sheet.addCell(new Label(4, 9 + i, "", wcf_center));
sheet.addCell(new Label(5, 9 + i, "", wcf_center));
}
/** *//** **********以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中******** */
workbook.write();
/** *//** *********关闭文件************* */
workbook.close();
System.out.println("导出成功");
// 存放url地址
} catch (Exception e)
{
System.out.println("在输出到EXCEL的过程中出现错误，错误原因：" + e.toString());
}
}
扫一扫
2017
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
