C#操作Excel
各位老师朋友帮帮小弟,公司想用C#做这么一个程序:把Dataset数据导入到一个已有模板的Excel中,如何导入?
目前我的困惑时,模板占用了一定的行数,但是我不知道占用几行,所以我从Dataset往里面导数据的时候,无法确定起始导入行?这个问题该如何解决啊!请大侠们赐教!小弟在线等....急啊! --------------------编程问答--------------------
--------------------编程问答-------------------- anbin0814 我用的是Microsoft.Office.Interop.Excel.Application这个com组件,导出是没有问题的,问题的关键是我要把数据(Dataset里面的数据)导入到以用模板的Excel中,如果没有模板我可以从第一行导入,但是现在有模板了,占用行数不去定,可以是3,4行等,我如何确定我要从哪一行开始导入Dataset里面的数据! --------------------编程问答-------------------- 我想确定我导入数据的起始行 --------------------编程问答-------------------- public void INSERT_Excel(DataView dvs,string strPath,string Name)
/// <summary>
/// 导出Excel表格
/// </summary>
/// <param name="data">DataTable</param>
/// <param name="page">Page</param>
/// <param name="columns">导出的列名中英文</param>
/// <param name="title">表格的标题</param>
/// <param name="fileName">导出的文件名</param>
public void ExportExcel(DataTable data, Page page, string[] columns, string title, string fileName)
{
StringBuilder sb = new StringBuilder();
string[] chColumns = new string[] { };//中文标题
string[] enColumns = new string[] { };//对象属性
if (!string.IsNullOrEmpty(columns[0]))
{
chColumns = columns[0].Split(';');
enColumns = columns[1].Split(';');
}
sb.Append("<meta HTTP-EQUIV=\"content-type\" CONTENT=\"text/html; charset=utf-8\"><table border=\"1\" cellSpacing=\"0\" cellPadding=\"0\" width=\"100%\" >");
sb.Append("<tr><td colspan=\"" + enColumns.Length + "\" align=\"center\" style=\"font-size:24px; font-weight:bold;\">" + title + "</td></tr>");
sb.Append("<tr>");
//创建表头
if (chColumns.Length > 0)
{
for (int i = 0; i < chColumns.Length; i++)
{
sb.Append("<td align=\"center\" style=\"font-weight:bold;\">" + chColumns[i] + "</td>");
}
}
sb.Append("</tr>");
//添加内容
for (int i = 0; i < data.Rows.Count; i++)
{
sb.Append("<tr>");
for (int j = 0; j < enColumns.Length; j++)
{
sb.Append("<td align=\"center\">" + typeof(object).GetProperty(enColumns[j]).GetValue(data.Rows[i][j], null) + "</td>"); //获取list对象中的属性值
}
sb.Append("</tr>");
}
sb.Append("</table>");
WriteFile(sb,page,fileName);
}
/// <summary>
/// 将StringBuilder写入流
/// </summary>
/// <param name="sb">StringBuilder对象</param>
/// <param name="page">Page</param>
/// <param name="fileName">导出文件名</param>
private void WriteFile(StringBuilder sb,Page page,string fileName)
{
page.Response.Buffer = true;
page.Response.Clear();
page.Response.ClearContent();
page.Response.ClearHeaders();
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8));
page.Response.ContentType = "application/ms-excel";
page.Response.Charset = "UTF-8";
page.Response.HeaderEncoding = System.Text.Encoding.UTF8;
page.Response.ContentEncoding = System.Text.Encoding.UTF8;
System.IO.StringWriter writer = new System.IO.StringWriter(sb);
System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
//关闭流
htmlWriter.Close();
writer.Close();
//输出
page.Response.Write(writer);
page.Response.End();
}
{
Excel.Application app=new Application();
Excel._Workbook book;
Excel._Worksheet sheet;
book=(Excel._Workbook)app.Workbooks.Open(strPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
sheet=(Excel._Worksheet)book.Sheets[1];
int j=dvs.Count;
Excel.Range ran1=app.ActiveCell;
ran1=sheet.get_Range(sheet.Cells[1,1],sheet.Cells[1,9]);
ran1.Value2=Name;
for(int i=0;i <dvs.Count;i++)
{
try
{
sheet.Cells[i+4,"A"]=""
sheet.Cells[i+4,"B"]="";
}
catch(Exception ex)
{
HttpContext.Current.Response.Write(" <script language='javascript'>alert('"+ex.Message+"') </script>");
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
HttpContext.Current.Response.Write(" <script language='javascript'>alert('导出失败!') </script>");
return;
}
}
book.Save();
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
GC.Collect();
GC.Collect();
HttpContext.Current.Response.Write(" <script language='javascript'>alert('导出成功!') </script>");
HttpContext.Current.Response.Write(" <script language='javascript'>window.open('../Template_temp/A.xls','_bank') </script>");
}
--------------------编程问答-------------------- --------------------编程问答-------------------- Excel.Range ran1=app.ActiveCell;
ran1=sheet.get_Range(sheet.Cells[1,1],sheet.Cells[1,9]);
ran1.Value2=Name;
for(int i=0;i <dvs.Count;i++)
{
try
{
sheet.Cells[i+4,"A"]=""
sheet.Cells[i+4,"B"]=""; 这段代码是什么意思为什么要获取这个矩形呢? sheet.Cells[1,1],sheet.Cells[1,9]
--------------------编程问答-------------------- 现在有很多开源的dll,可以引用open office,Flex,NPOI等等。只是你得自己看它们的API,简单的导出写入肯定很轻松搞定
-------------------------------------------------------------------------------
http://www.jiemengwu.com/ 解梦屋 http://www.phpzy.com/php/ 绿色php资源http://www.shopfw.com网店服务 --------------------编程问答-------------------- 谢谢了wuyq11
--------------------编程问答-------------------- 问题还没解决!呜呜.... --------------------编程问答-------------------- 模版不知道怎么用,那个高手给个实例看看 --------------------编程问答-------------------- --------------------编程问答-------------------- 用OpenExcel.dll,封装的OpenXML,不过需要Office2007以上版本,操作很简单,性能也比2003的好 --------------------编程问答--------------------
jixingchao001
能给出代码吗?
补充:.NET技术 , C#