//将数据集导到excel 以 XLS 格式里
private void DataSetToExcelXls(DataSet ds)
{
#region 用例说明
/*
【输入】
sender:
e:
【输出】
无
【流程定义】
1、将生成的DataSet的数据导到指定的Execl文件里,并显示出来
【可选流程】
【问题】
*/
#endregion
Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+txtBeginTime.Text.Trim()+txtFinishTime.Text.Trim()+rd.Next(999999).ToString()+ ".XLS";
//Excel.Application excel= new Excel.ApplicationClass();//Excel.Application();
Excel.Application excel ;
//excel = new Excel.Application();
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] =lblReportTime.Text; //合并项
oSheet.Cells[2, 1] = lblReportCount.Text;//合并项
oSheet.Cells[4, 1] = "话机号码" ;//合并项
oSheet.Cells[4, 2] = "月份" ;//合并项
oSheet.Cells[3, 3] = "通话总次数(次)";
oSheet.Cells[3, 7] = "通话总时长(分)";
oSheet.Cells[3,11] = "通话总金额(元)";
oSheet.Cells[4, 3] = "国际";
oSheet.Cells[4, 4] = "国内";
oSheet.Cells[4, 5] = "港澳台";
oSheet.Cells[4, 6] = "合计";
oSheet.Cells[4, 7] = "国际";
oSheet.Cells[4, 8] = "国内";
oSheet.Cells[4, 9] = "港澳台";
oSheet.Cells[4,10] = "合计";
oSheet.Cells[4,11] = "国际";
oSheet.Cells[4,12] = "国内";
oSheet.Cells[4,13] = "港澳台";
oSheet.Cells[4,14] = "合计"; oSheet.get_Range("A1","N4").Font.Size= 9 ;
oSheet.get_Range("A1","N4").Font.Bold = true;
oSheet.get_Range("A1","N4").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1","N4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//该范围内设置外面一层边框
oSheet.get_Range("A1","N4").BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin ,Excel.XlColorIndex.xlColorIndexAutomatic,Color.Black.ToArgb()) ; //该范围内设置里面所有单元格的边框
oSheet.get_Range("A1","N4").Borders.Weight = Excel.XlBorderWeight.xlThin ;
oSheet.get_Range("A1","N4").Borders.Color = Color.Black.ToArgb() ;
//范围内单元格添加背景颜色
oSheet.get_Range("A1","N2").Cells.Interior.Color =Color.FromArgb(153,255,204).ToArgb();
oSheet.get_Range("A1","N2").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;
oSheet.get_Range("A3","N4").Cells.Interior.Color = Color.LemonChiffon.ToArgb();
oSheet.get_Range("A3","N4").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;
DataTable dt =ds.Tables[0];
DataRow dr;
int count=dt.Rows.Count+5;
string strExcelLength = "N"+count.ToString().Trim(); int I,J;
for(I = 5;I< count;I++)
{
dr=dt.Rows[I-5];
for(J = 1;J<(dt.Columns.Count +1) ;J++)
{
oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;
}
} oSheet.get_Range("A5",strExcelLength).Font.Size= 9 ;
oSheet.get_Range("A5",strExcelLength).Font.Bold = false;
oSheet.get_Range("A5",strExcelLength).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A5",strExcelLength).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//该范围内设置外面一层边框
oSheet.get_Range("A5",strExcelLength).BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin ,Excel.XlColorIndex.xlColorIndexAutomatic,Color.Black.ToArgb()) ; //该范围内设置里面所有单元格的边框
oSheet.get_Range("A5",strExcelLength).Borders.Weight = Excel.XlBorderWeight.xlThin ;
oSheet.get_Range("A5",strExcelLength).Borders.Color = Color.Black.ToArgb() ;
//范围内单元格添加背景颜色
oSheet.get_Range("A5",strExcelLength).Cells.Interior.Color = Color.PaleTurquoise.ToArgb();
oSheet.get_Range("A5",strExcelLength).Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;
oSheet.get_Range("A1","N1").Merge(true);
oSheet.get_Range("A2","N2").Merge(true); oSheet.get_Range("A3","A4").Merge(true);
oSheet.get_Range("B3","B4").Merge(true);
oSheet.get_Range("C3","F3").Merge(true);
oSheet.get_Range("G3","J3").Merge(true);
oSheet.get_Range("K3","N3").Merge(true);
string path=Server.MapPath(".") + "\\..\\..\\excel-file\\" + strFileName;
oWB.SaveAs(path,Missing.Value,"","",true,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,Missing.Value,Missing.Value);
//oWB.SaveCopyAs(path);
oWB.Close(false,null,null);
excel.Workbooks.Close();
excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet); oSheet = null;
oWB= null;
excel = null; GC.Collect();
string test="../../excel-file/" + strFileName;
Response.Redirect(test);
}
private void DataSetToExcelXls(DataSet ds)
{
#region 用例说明
/*
【输入】
sender:
e:
【输出】
无
【流程定义】
1、将生成的DataSet的数据导到指定的Execl文件里,并显示出来
【可选流程】
【问题】
*/
#endregion
Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+txtBeginTime.Text.Trim()+txtFinishTime.Text.Trim()+rd.Next(999999).ToString()+ ".XLS";
//Excel.Application excel= new Excel.ApplicationClass();//Excel.Application();
Excel.Application excel ;
//excel = new Excel.Application();
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] =lblReportTime.Text; //合并项
oSheet.Cells[2, 1] = lblReportCount.Text;//合并项
oSheet.Cells[4, 1] = "话机号码" ;//合并项
oSheet.Cells[4, 2] = "月份" ;//合并项
oSheet.Cells[3, 3] = "通话总次数(次)";
oSheet.Cells[3, 7] = "通话总时长(分)";
oSheet.Cells[3,11] = "通话总金额(元)";
oSheet.Cells[4, 3] = "国际";
oSheet.Cells[4, 4] = "国内";
oSheet.Cells[4, 5] = "港澳台";
oSheet.Cells[4, 6] = "合计";
oSheet.Cells[4, 7] = "国际";
oSheet.Cells[4, 8] = "国内";
oSheet.Cells[4, 9] = "港澳台";
oSheet.Cells[4,10] = "合计";
oSheet.Cells[4,11] = "国际";
oSheet.Cells[4,12] = "国内";
oSheet.Cells[4,13] = "港澳台";
oSheet.Cells[4,14] = "合计"; oSheet.get_Range("A1","N4").Font.Size= 9 ;
oSheet.get_Range("A1","N4").Font.Bold = true;
oSheet.get_Range("A1","N4").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1","N4").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//该范围内设置外面一层边框
oSheet.get_Range("A1","N4").BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin ,Excel.XlColorIndex.xlColorIndexAutomatic,Color.Black.ToArgb()) ; //该范围内设置里面所有单元格的边框
oSheet.get_Range("A1","N4").Borders.Weight = Excel.XlBorderWeight.xlThin ;
oSheet.get_Range("A1","N4").Borders.Color = Color.Black.ToArgb() ;
//范围内单元格添加背景颜色
oSheet.get_Range("A1","N2").Cells.Interior.Color =Color.FromArgb(153,255,204).ToArgb();
oSheet.get_Range("A1","N2").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;
oSheet.get_Range("A3","N4").Cells.Interior.Color = Color.LemonChiffon.ToArgb();
oSheet.get_Range("A3","N4").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;
DataTable dt =ds.Tables[0];
DataRow dr;
int count=dt.Rows.Count+5;
string strExcelLength = "N"+count.ToString().Trim(); int I,J;
for(I = 5;I< count;I++)
{
dr=dt.Rows[I-5];
for(J = 1;J<(dt.Columns.Count +1) ;J++)
{
oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;
}
} oSheet.get_Range("A5",strExcelLength).Font.Size= 9 ;
oSheet.get_Range("A5",strExcelLength).Font.Bold = false;
oSheet.get_Range("A5",strExcelLength).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A5",strExcelLength).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//该范围内设置外面一层边框
oSheet.get_Range("A5",strExcelLength).BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin ,Excel.XlColorIndex.xlColorIndexAutomatic,Color.Black.ToArgb()) ; //该范围内设置里面所有单元格的边框
oSheet.get_Range("A5",strExcelLength).Borders.Weight = Excel.XlBorderWeight.xlThin ;
oSheet.get_Range("A5",strExcelLength).Borders.Color = Color.Black.ToArgb() ;
//范围内单元格添加背景颜色
oSheet.get_Range("A5",strExcelLength).Cells.Interior.Color = Color.PaleTurquoise.ToArgb();
oSheet.get_Range("A5",strExcelLength).Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic ;
oSheet.get_Range("A1","N1").Merge(true);
oSheet.get_Range("A2","N2").Merge(true); oSheet.get_Range("A3","A4").Merge(true);
oSheet.get_Range("B3","B4").Merge(true);
oSheet.get_Range("C3","F3").Merge(true);
oSheet.get_Range("G3","J3").Merge(true);
oSheet.get_Range("K3","N3").Merge(true);
string path=Server.MapPath(".") + "\\..\\..\\excel-file\\" + strFileName;
oWB.SaveAs(path,Missing.Value,"","",true,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,Missing.Value,Missing.Value);
//oWB.SaveCopyAs(path);
oWB.Close(false,null,null);
excel.Workbooks.Close();
excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet); oSheet = null;
oWB= null;
excel = null; GC.Collect();
string test="../../excel-file/" + strFileName;
Response.Redirect(test);
}
解决方案 »
- 怎么判断一个5位数中是否有4和7这两个数字?
- c#中的“接口”问题,代码摆出。请帮我解答一下。100分奉上
- XPO如何查询多个表中的字段
- 关于System.Configuration引用
- 一句用控制台输出语句的含义?Console.WriteLine("{0,-35}","A");
- 关于类似“编程思想”、“设计模式”等理论的重要性的问题,与各位探讨,欢迎踊跃发言!
- 菜鸟想问DataTable和DataSet有什么区别???请理解的高手讲解一下:
- sqlserver2005设置权限
- 在win窗口中放入一个datagrid怎样在datagrid里面加入一个点选按钮啊,
- 请问Debug目录下和 Release目录下生成的EXE文件有何实质性区别
- 如果给一个窗体添加图片等资源
- datagrid中的删除问题?
Response.Write("<script language='javascript'>window.open(FilePath,'Excel','top=100,left=100,toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=no,WIDTH=200,HEIGHT=300');
</script>")
oWB.SaveAs(path,Missing.Value,"","",true,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,Missing.Value,Missing.Value);
其中Missing.Value是什么,在什么地方引用了?谢谢,我很菜
ToExcel1(tblResults)
End Sub Sub ToExcel1(ByVal ctl As System.Web.UI.Control)
Response.Charset = "GB2312"
Response.AppendHeader("Content-Disposition", "attachment;filename=totalreport.xls")
Response.ContentEncoding = System.Text.Encoding.UTF8
Response.ContentType = "application/ms-excel" ctl.Page.EnableViewState = False
Dim tw As System.IO.StringWriter = New System.IO.StringWriter()
Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(tw) ctl.RenderControl(hw) Response.Write(tw.ToString())
Response.End()
End Sub
protected void toExcel_Click(object sender,EventArgs e)
{
ToExcel(parentList);
} private void ToExcel(System.Web.UI.Control ctl)
{
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/ms-excel"; ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(tw); ctl.RenderControl(hw); Response.Write(tw.ToString());
Response.End();
}
excel = new Excel.ApplicationClass();
时页面报“拒绝访问”错误!