Public Function BuildReport(ByVal strXmlData As String, ByVal strOutputFileName As String) As String Dim objXmlDoc As New DOMDocument
Dim objXmlNode_Main As IXMLDOMNode '表头
Dim objXmlNodeList_Cashier As IXMLDOMNodeList '收银员 Dim objXmlNode_Detail As IXMLDOMNode '详细信息
Dim objResultWB As Workbook, objResultSheet As Worksheet Dim strMsg As String
Set objResultWB = Workbooks.Add() Set objResultSheet = objResultWB.Worksheets(1)
objXmlDoc.async = False If objXmlDoc.loadXML(strXmlData) = False Then strMsg = "Sorry, Load Xml Content Failed." objResultSheet.Cells(1, 1).Value = strMsg 'objResultWB.SaveAs strOutputFileName 'objResultWB.Close BuildReport = strMsg 'Exit Function Else
Set objXmlNode_Main = objXmlDoc.selectSingleNode("RS_DATA/HEAD/RS_DATA/RS_ROW") '表头
Set objXmlNodeList_Cashier = objXmlDoc.selectNodes("RS_DATA/CASHIER/RS_DATA//RS_ROW") '收银员 Set objXmlNode_Detail = objXmlDoc.selectSingleNode("RS_DATA/CONTENT") '详细信息
End FunctionFunction FillSheet(ByVal objsht As Worksheet, ByVal objXmlNode_Main As IXMLDOMNode, ByVal objXmlNodeList_Cashier As IXMLDOMNodeList, ByVal objXmlNode_Detail As IXMLDOMNode) Dim intStartRow As Integer, intStartCol As Integer
Dim intCahierLength As Integer, intCahierDetailLength As Integer, i As Integer
Dim strCahierId As String, strFilter As String
Dim intStuffCount As Integer, iRow As Integer, jRow As Integer
Dim objRowData As IXMLDOMNode, objXmlDetail As IXMLDOMNode Dim objXmlDetailList As IXMLDOMNodeList
如果是2007的话,建议用open xml
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range range;
Excel.Range range2; range.EntireColumn.AutoFit();
//设置顶部説明
//range = worksheet.get_Range(xlApp.Cells[1, colCount], xlApp.Cells[1, colCount]);
//range.EntireRow.Hidden = true;
//range.MergeCells = true;
//range.RowHeight = 38;
//range.Font.Bold = true;
//range.Font.Size = 14;
//range.Font.ColorIndex = 10;//字体颜色
// xlApp.ActiveCell.FormulaR1C1 = DateTime.Now.ToString("yyyy-MM-dd"); xlApp.Cells.HorizontalAlignment = Excel.Constants.xlLeft; //全局 range = worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.ColumnWidth = "8.38";
range.WrapText = true;
range = worksheet.get_Range(xlApp.Cells[1, 56], xlApp.Cells[1, 56]);
range.ColumnWidth = "20.25";
range.WrapText = true;
range = worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount + 1, colCount]);
range.Font.Name = "Arial Unicode MS";
range.Font.Size = "10";
// range.WrapText = true;
//特殊数字格式
Dim objXmlNode_Main As IXMLDOMNode '表头
Dim objXmlNodeList_Cashier As IXMLDOMNodeList '收银员
Dim objXmlNode_Detail As IXMLDOMNode '详细信息
Dim objResultWB As Workbook, objResultSheet As Worksheet
Dim strMsg As String
Set objResultWB = Workbooks.Add()
Set objResultSheet = objResultWB.Worksheets(1)
objXmlDoc.async = False
If objXmlDoc.loadXML(strXmlData) = False Then
strMsg = "Sorry, Load Xml Content Failed."
objResultSheet.Cells(1, 1).Value = strMsg
'objResultWB.SaveAs strOutputFileName
'objResultWB.Close
BuildReport = strMsg
'Exit Function
Else
Set objXmlNode_Main = objXmlDoc.selectSingleNode("RS_DATA/HEAD/RS_DATA/RS_ROW") '表头
Set objXmlNodeList_Cashier = objXmlDoc.selectNodes("RS_DATA/CASHIER/RS_DATA//RS_ROW") '收银员
Set objXmlNode_Detail = objXmlDoc.selectSingleNode("RS_DATA/CONTENT") '详细信息
Call FillSheet(objResultSheet, objXmlNode_Main, objXmlNodeList_Cashier, objXmlNode_Detail)
End If
If Workbooks.Application.Visible = False Then
Workbooks.Application.Visible = True
End If
'objResultWB.SaveAs strOutputFileName
'objResultWB.Close
End FunctionFunction FillSheet(ByVal objsht As Worksheet, ByVal objXmlNode_Main As IXMLDOMNode, ByVal objXmlNodeList_Cashier As IXMLDOMNodeList, ByVal objXmlNode_Detail As IXMLDOMNode) Dim intStartRow As Integer, intStartCol As Integer
Dim intCahierLength As Integer, intCahierDetailLength As Integer, i As Integer
Dim strCahierId As String, strFilter As String
Dim intStuffCount As Integer, iRow As Integer, jRow As Integer
Dim objRowData As IXMLDOMNode, objXmlDetail As IXMLDOMNode
Dim objXmlDetailList As IXMLDOMNodeList
'On Error GoTo ErrHandle intStartRow = 6: intStartCol = 1
objsht.Name = "Shift Report"
objsht.Range(objsht.Cells(intStartRow - 5, intStartCol), objsht.Cells(intStartRow - 5, intStartCol + 3)).Merge
objsht.Cells(intStartRow - 5, intStartCol).Value = "Shift Report"
objsht.Range(objsht.Cells(intStartRow - 4, intStartCol), objsht.Cells(intStartRow - 4, intStartCol + 1)).Merge
objsht.Cells(intStartRow - 4, intStartCol).Value = "店名:" + objXmlNode_Main.selectSingleNode("@COMPANY_NAME").Text
objsht.Range(objsht.Cells(intStartRow - 3, intStartCol), objsht.Cells(intStartRow - 3, intStartCol + 1)).Merge
objsht.Cells(intStartRow - 3, intStartCol).Value = "店号:" + objXmlNode_Main.selectSingleNode("@COMPANY_CODE").Text
objsht.Range(objsht.Cells(intStartRow - 3, intStartCol + 2), objsht.Cells(intStartRow - 3, intStartCol + 3)).Merge
objsht.Cells(intStartRow - 3, intStartCol + 2).Value = "报表日期:" + objXmlNode_Main.selectSingleNode("@BEGIN_DATE").Text
objsht.Cells(intStartRow - 3, intStartCol + 2).HorizontalAlignment = xlRight
i = 0
'填收银员信息
intCahierLength = objXmlNodeList_Cashier.Length
For iRow = 0 To intCahierLength - 1
Set objRowData = objXmlNodeList_Cashier.Item(iRow)
objsht.Cells(intStartRow - 1 + i, intStartCol).Value = "收银员: " + objRowData.selectSingleNode("@OPERATOR_NAME").Text
objsht.Range(objsht.Cells(intStartRow + i, intStartCol), objsht.Cells(intStartRow + i, intStartCol + 3)).HorizontalAlignment = xlCenter objsht.Cells(intStartRow + i, intStartCol).Value = "项目"
objsht.Cells(intStartRow + i, intStartCol + 1).Value = "名称"
objsht.Cells(intStartRow + i, intStartCol + 2).Value = "数量"
objsht.Cells(intStartRow + i, intStartCol + 3).Value = "金额"
objsht.Cells(intStartRow + i + 1, intStartCol).Value = "'====================================================="
strCahierId = objRowData.selectSingleNode("@OPERATOR_ID").Text
strFilter = "[@CASHIERID='" + strCahierId + "']"
Set objXmlDetailList = objXmlNode_Detail.selectNodes(".//RS_ROW" & strFilter)
intCahierDetailLength = objXmlDetailList.Length
jRow = 0
If intCahierDetailLength > 0 Then
For jRow = 0 To intCahierDetailLength - 1
Set objXmlDetail = objXmlDetailList.Item(jRow)
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol).HorizontalAlignment = xlCenter
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol + 1).HorizontalAlignment = xlLeft
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol).Value = objXmlDetail.selectSingleNode("@ITEMKEY").Text
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol + 1).Value = objXmlDetail.selectSingleNode("@NAMECHINESE").Text
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol + 2).Value = objXmlDetail.selectSingleNode("@TOTALCOUNT").Text
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol + 3).Value = objXmlDetail.selectSingleNode("@TOTALAMOUNT").Text
Next jRow
End If
objsht.Cells(intStartRow + i + 2 + jRow, intStartCol).Value = "'====================================================="
objsht.Cells(intStartRow + i + 3 + jRow, intStartCol).Value = "合计"
If intCahierDetailLength > 0 Then
objsht.Cells(intStartRow + i + 3 + jRow, intStartCol + 2).Value = "=SUM(R[-2]C:R[" & -(intCahierDetailLength + 1) & "]C)"
objsht.Cells(intStartRow + i + 3 + jRow, intStartCol + 3).Value = "=SUM(R[-2]C:R[" & -(intCahierDetailLength + 1) & "]C)"
End If
objsht.Cells(intStartRow + i + 4 + jRow, intStartCol).HorizontalAlignment = xlLeft
objsht.Cells(intStartRow + i + 4 + jRow, intStartCol).Value = Date
objsht.Cells(intStartRow + i + 4 + jRow, intStartCol + 3).NumberFormatLocal = "h:mm:ss"
objsht.Cells(intStartRow + i + 4 + jRow, intStartCol + 3).Value = Time
i = i + intCahierDetailLength + 7
Next iRow
'去掉网格
objsht.Activate
ActiveWindow.DisplayGridlines = False objsht.Cells.Font.Size = 9
objsht.Range(objsht.Cells(intStartRow - 5, intStartCol), objsht.Cells(intStartRow - 5, intStartCol + 3)).HorizontalAlignment = xlCenter
objsht.Range(objsht.Cells(intStartRow - 5, intStartCol), objsht.Cells(intStartRow - 5, intStartCol + 3)).Font.Bold = True
objsht.Range(objsht.Cells(intStartRow - 5, intStartCol), objsht.Cells(intStartRow - 5, intStartCol + 3)).Font.Size = 12
objsht.Rows(intStartRow - 5).RowHeight = 26
objsht.Columns(intStartCol + 1).ColumnWidth = 15
objsht.Columns(intStartCol + 2).ColumnWidth = 6
objsht.Columns(intStartCol + 3).ColumnWidth = 8
Exit Function
ErrHandle:
objsht.Cells(intStartRow + iRow, intStartCol + 9).Value = Err.DescriptionEnd Function把vba里的代码弄过来了,可以用c#写差不多的代码,比如,上面的代码在宏中定义的样式,这步就不需要写了。然后在宏里面添充数据,这步就可以用代码实现,主要是麻烦,你得自己去看excel的样式,数格子,然后一格格的填,如果能用循环的话就好说,但是如果不能用循环的,你就只有慢慢的数格子填了~
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "information.xls");
}
/// <summary>
/// 定义导出Excel的函数
/// </summary>
/// <param name="FileType"></param>
/// <param name="FileName"></param>
private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}在页面的page中加入EnableEventValidation="false"
if (dtData != null)
{
string filePath = Server.MapPath("~/" + Guid.NewGuid().ToString() + ".xls");
File.Copy(Server.MapPath("~/demo.xls"), filePath);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;");
using (conn)
{
conn.Open(); for (int i = 0; i < dtData.Rows.Count; i++)
{
OleDbCommand cmd = new OleDbCommand("insert into [Sheet1$]([手机号],[字段一],[字段二],[字段三],[字段四],[字段五],[字段六],[字段七]) values(@phone,@one,@two,@three,@four,@five,@six,@seven)", conn);//注明这个字段一 二 三就是你建的模板的excel标题
cmd.Parameters.AddWithValue("@phone", dtData.Rows[i]["c_phone"]);
cmd.Parameters.AddWithValue("@one", dtData.Rows[i]["con"]);
cmd.ExecuteNonQuery();
}
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + ccc + ".xls");
conns.Close();
GC.Collect();
Response.BinaryWrite(File.ReadAllBytes(xls));
File.Delete(filePath);
File.Delete(xls);
当然了 还可以动态添加标题