asp.net 数据导出到指定格式的excel 急!公司有一个用excel制作的固定格式的员工资料表,现要求系统能够按照 其标准格式打印。我的想法是调用空白excel模板,然后就数据导入再打印。 本人对数据导入到excel指定单元格不熟悉.请高人指点整个操作过程,分数可再加。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 sql = @"select * into [Excel 8.0;database=" + Server.MapPath(".") + @"\模版.xls].[Sheet1] from Paging";http://hi.baidu.com/mezhoufeng/blog/item/bfad7fedb6fd63d7b31cb1c8.html 我没有找到需要的源码,请各位能否详细说明?必须是调用指定格式的excel 参考:http://topic.csdn.net/t/20061228/17/5262604.html 网上一搜一大堆,楼主可以自己去找下。http://www.cnblogs.com/ringwang/archive/2007/12/11/991220.html 我觉得你可以把需要的数据用GRIDVIEW显示出来,在把数据导出到EXCEL中,不知道这样可不可以 做个datatable直接导入到excel 自己打印 呵呵,这个我原来也做过要到指定的模版,结果硬是搞不定,后来我在程序里创建一个跟原来模板一摸一样的代码如下,分都给我吧public partial class manager_Shop_List_Down : System.Web.UI.Page{ private object strSampleFolder = "F:\\Excel\\Excel.xls"; //Excel对象的引用 private Excel.Application objExcel = null;//一个excel对象 private Excel.Workbooks objBooks = null; // private Excel._Workbook objBook = null; private Excel.Sheets objSheets = null; private Excel._Worksheet objSheet = null; private Excel.Range objRange = null; private Excel.Font objFont = null; //获取所需要的参数 private object objOpt = System.Reflection.Missing.Value; private object strSampleFolderCom = "F:\\Excel\\"; Mobile.Bll.ShopList bll = new Mobile.Bll.ShopList(); DataSet ds = new DataSet(); ds = bll.GetShopList(); objExcel = new Excel.Application(); objBooks = (Excel.Workbooks)objExcel.Workbooks; objBook = (Excel._Workbook)(objBooks.Add(objOpt)); objSheets = (Excel.Sheets)objBook.Worksheets; objSheet = (Excel._Worksheet)(objSheets.get_Item(1)); protected void ExportyhInfo_Click(object sender, EventArgs e) { #region 标题:中国移动12580业务登记表 //标题位置 Excel.Range title = objSheet.get_Range(objSheet.Cells[1, 71], objSheet.Cells[1, 17]); title.Value2 = Type.Missing; title.Merge(Type.Missing); title.Value2 = "中国移动12580业务登记表"; title.Borders.Color = 0; title.Borders.Weight = 2; // 标题变粗 objRange = objSheet.get_Range("G1", "Q1"); objFont = objRange.Font; objFont.Bold = true; objFont.Size = 20; #endregion #region 客户基本信息 //客户基本信息 Excel.Range baseInfo = objSheet.get_Range(objSheet.Cells[2, 1], objSheet.Cells[2, 16]); baseInfo.Value2 = Type.Missing; baseInfo.Merge(Type.Missing); baseInfo.Value2 = "客户基本信息"; baseInfo.Borders.Color = 0; baseInfo.Borders.Weight = 2; // 客户基本信息变粗 objRange = objSheet.get_Range("A2", "P2"); objFont = objRange.Font; objFont.Bold = true; #endregion #region 移动名片 优先推荐 //移动名片 Excel.Range mobileCart = objSheet.get_Range(objSheet.Cells[2, 17], objSheet.Cells[2, 21]); mobileCart.Value2 = Type.Missing; mobileCart.Merge(Type.Missing); mobileCart.Value2 = "移动名片"; mobileCart.Borders.Color = 0; mobileCart.Borders.Weight = 2; // 移动名片变粗 objRange = objSheet.get_Range("Q2", "U2"); objFont = objRange.Font; objFont.Bold = true; #endregion /.... #region Add Data 添加数据 objRange = objSheet.get_Range("A3", objOpt); objRange.set_Value(objOpt, "编号"); objFont = objRange.Font; objFont.Bold = true; objFont.Size = 10; objRange = objSheet.get_Range("B3", objOpt); objRange.set_Value(objOpt, "城市"); objFont = objRange.Font; objFont.Bold = true; objFont.Size = 10; objRange = objSheet.get_Range("C3", objOpt); objRange.set_Value(objOpt, "区"); objFont = objRange.Font; objFont.Bold = true; objFont.Size = 10; objRange = objSheet.get_Range("D3", objOpt); objRange.set_Value(objOpt, "商户名称"); objFont = objRange.Font; objFont.Bold = true; objFont.Size = 10; objRange = objSheet.get_Range("E3", objOpt); objRange.set_Value(objOpt, "分店名"); objFont = objRange.Font; objFont.Bold = true; objFont.Size = 10; int auto = 3; string current = ""; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { auto++; current = auto.ToString(); objRange = objSheet.get_Range("B" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["cityname"].ToString()); objRange = objSheet.get_Range("C" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["quyuname"].ToString()); objRange = objSheet.get_Range("D" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["shopname"].ToString()); objRange = objSheet.get_Range("G" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["bclassname"].ToString()); objRange = objSheet.get_Range("H" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["sclassname"].ToString()); objRange = objSheet.get_Range("I" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["address"].ToString()); objRange = objSheet.get_Range("J" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["tel"].ToString()); objRange = objSheet.get_Range("M" + current, objOpt); objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["username"].ToString()); objRange = objSheet.get_Range("P" + current, objOpt); objRange.set_Value(objOpt, Mobile .Public .WebPublic.ClearHtml ( ds.Tables[0].Rows[i]["chuneirong"].ToString())); objRange.WrapText = true; } #endregion // Save the workbook and quit Excel. objBook.SaveAs(strSampleFolderCom + "xxx.xls", objOpt, objOpt, objOpt, objOpt, objOpt, Excel.XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt); objBook.Close(false, objOpt, objOpt); objExcel.Quit(); //以下释放excel进程 System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange); objBook = null; objBooks = null; objSheets = null; objSheet = null; objExcel = null; objRange = null; pnlLoad.Visible = false; //把操作Excel文件用到的对象实例全部释放。 //然后资源回收! GC.Collect();}} 还要把office组件引用在bin目录下忘记了,用的时候要调用using Excel=Microsoft.Office.Interop.Excel; protected void ExportyhInfo_Click(object sender, EventArgs e) 方法应该在上方//获取所需要的参数 private object objOpt = System.Reflection.Missing.Value; private object strSampleFolderCom = "F:\\Excel\\"; 模板操作excel Microsoft.Office.Interop.Excel.ApplicationClass apc = new Microsoft.Office.Interop.Excel.ApplicationClass(); apc.Visible = true ; Microsoft.Office.Interop.Excel.Workbook wkbook = apc.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet wksheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbook.ActiveSheet; int rowIndex = 2; int colIndex = 0; wksheet.get_Range(apc.Cells[1, 1], apc.Cells[dataTable.Rows.Count, dataTable.Columns.Count]).NumberFormat = "@"; //取得列标题 foreach (DataColumn dc in dataTable.Columns) { colIndex++; wksheet.Cells[1, colIndex] = dc.ColumnName; } //取得表格中数据 foreach (DataRow dr in dataTable.Rows) { colIndex = 1; foreach (DataColumn dc in dataTable.Columns) { if (dc.DataType == System.Type.GetType("System.DateTime")) { apc.Cells[rowIndex, colIndex] = "'" + (Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd"); } else if (dc.DataType == System.Type.GetType("System.String")) { apc.Cells[rowIndex, colIndex] = "'" + dr[dc.ColumnName].ToString(); } else { apc.Cells[rowIndex, colIndex] = "'" + dr[dc.ColumnName].ToString(); } wksheet.get_Range(apc.Cells[rowIndex, colIndex], apc.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; colIndex++; } rowIndex++; } //设置表格样式 wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, dataTable.Columns.Count]).Interior.ColorIndex = 20; wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, dataTable.Columns.Count]).Font.ColorIndex = 3; wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, dataTable.Columns.Count]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; wksheet.get_Range(apc.Cells[1, 1], apc.Cells[dataTable.Rows.Count, dataTable.Columns.Count]).Columns.AutoFit(); if (File.Exists(fileName)) { File.Delete(fileName); } wksheet.Name = "统计结果"; //wkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // wkbook.Close(Type.Missing, Type.Missing, Type.Missing); //apc.Quit(); wkbook = null; apc = null; GC.Collect(); 网上有例子,可以直接读取EXECL文件 从要复制数据库的数据上选择所有任务,执行数据导出命令,目的选择excel,先导到excel中再做相关调整。你试试行不行。 怎么没有一个经典的例子呢?如果数据成功导入excel,请问如何直接打印出来呢? 在 excel 中进行数据调整之后直接打印不就可以了吗 你是什么意思啊? 就是把数据导入模板excel 直接调用打印预览功能打印 这样就可以了 已经导入excel了 直接打印就可以了。 你那种不行,从数据库着手不安全,再说用户都从网页界面操作。点击按钮就把数据导入到模板excel直接打印出来,不要其他中间复杂过程 asp.net中,触发click事件,方法出现在html而不是.cs文件 跨网站查询 asp.net的treeview如何做成文件管理器? 请问怎样在页面间传递一个表的数据呢 赋值问题--请大伙帮帮忙,感激不尽! 将字符串转换成datetime格式的函数是什么?? 有关Form认证的问题 我也来凑个热闹,Oracle下500万记录分页(含思路) 文件上传的问题?在线等,急!! 奇怪为什么不能显示我选中的值? 新手求助:关于asp.net网站上传问题 GridView的操作(急)
http://topic.csdn.net/t/20061228/17/5262604.html
http://www.cnblogs.com/ringwang/archive/2007/12/11/991220.html
做个datatable
直接导入到excel 自己打印
代码如下,分都给我吧public partial class manager_Shop_List_Down : System.Web.UI.Page
{
private object strSampleFolder = "F:\\Excel\\Excel.xls"; //Excel对象的引用
private Excel.Application objExcel = null;//一个excel对象
private Excel.Workbooks objBooks = null; //
private Excel._Workbook objBook = null;
private Excel.Sheets objSheets = null;
private Excel._Worksheet objSheet = null;
private Excel.Range objRange = null;
private Excel.Font objFont = null;
//获取所需要的参数
private object objOpt = System.Reflection.Missing.Value;
private object strSampleFolderCom = "F:\\Excel\\";
Mobile.Bll.ShopList bll = new Mobile.Bll.ShopList();
DataSet ds = new DataSet();
ds = bll.GetShopList();
objExcel = new Excel.Application();
objBooks = (Excel.Workbooks)objExcel.Workbooks;
objBook = (Excel._Workbook)(objBooks.Add(objOpt));
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)(objSheets.get_Item(1));
protected void ExportyhInfo_Click(object sender, EventArgs e)
{
#region 标题:中国移动12580业务登记表
//标题位置
Excel.Range title = objSheet.get_Range(objSheet.Cells[1, 71], objSheet.Cells[1, 17]);
title.Value2 = Type.Missing;
title.Merge(Type.Missing);
title.Value2 = "中国移动12580业务登记表";
title.Borders.Color = 0;
title.Borders.Weight = 2;
// 标题变粗
objRange = objSheet.get_Range("G1", "Q1");
objFont = objRange.Font;
objFont.Bold = true;
objFont.Size = 20;
#endregion #region 客户基本信息
//客户基本信息
Excel.Range baseInfo = objSheet.get_Range(objSheet.Cells[2, 1], objSheet.Cells[2, 16]);
baseInfo.Value2 = Type.Missing;
baseInfo.Merge(Type.Missing);
baseInfo.Value2 = "客户基本信息";
baseInfo.Borders.Color = 0;
baseInfo.Borders.Weight = 2;
// 客户基本信息变粗
objRange = objSheet.get_Range("A2", "P2");
objFont = objRange.Font;
objFont.Bold = true;
#endregion
#region 移动名片 优先推荐
//移动名片
Excel.Range mobileCart = objSheet.get_Range(objSheet.Cells[2, 17], objSheet.Cells[2, 21]);
mobileCart.Value2 = Type.Missing;
mobileCart.Merge(Type.Missing);
mobileCart.Value2 = "移动名片";
mobileCart.Borders.Color = 0;
mobileCart.Borders.Weight = 2;
// 移动名片变粗
objRange = objSheet.get_Range("Q2", "U2");
objFont = objRange.Font;
objFont.Bold = true;
#endregion
/.... #region Add Data 添加数据
objRange = objSheet.get_Range("A3", objOpt);
objRange.set_Value(objOpt, "编号");
objFont = objRange.Font;
objFont.Bold = true;
objFont.Size = 10; objRange = objSheet.get_Range("B3", objOpt);
objRange.set_Value(objOpt, "城市");
objFont = objRange.Font;
objFont.Bold = true;
objFont.Size = 10; objRange = objSheet.get_Range("C3", objOpt);
objRange.set_Value(objOpt, "区");
objFont = objRange.Font;
objFont.Bold = true;
objFont.Size = 10; objRange = objSheet.get_Range("D3", objOpt);
objRange.set_Value(objOpt, "商户名称");
objFont = objRange.Font;
objFont.Bold = true;
objFont.Size = 10; objRange = objSheet.get_Range("E3", objOpt);
objRange.set_Value(objOpt, "分店名");
objFont = objRange.Font;
objFont.Bold = true;
objFont.Size = 10; int auto = 3;
string current = "";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
auto++;
current = auto.ToString();
objRange = objSheet.get_Range("B" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["cityname"].ToString());
objRange = objSheet.get_Range("C" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["quyuname"].ToString());
objRange = objSheet.get_Range("D" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["shopname"].ToString());
objRange = objSheet.get_Range("G" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["bclassname"].ToString());
objRange = objSheet.get_Range("H" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["sclassname"].ToString());
objRange = objSheet.get_Range("I" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["address"].ToString());
objRange = objSheet.get_Range("J" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["tel"].ToString());
objRange = objSheet.get_Range("M" + current, objOpt);
objRange.set_Value(objOpt, ds.Tables[0].Rows[i]["username"].ToString());
objRange = objSheet.get_Range("P" + current, objOpt);
objRange.set_Value(objOpt, Mobile .Public .WebPublic.ClearHtml ( ds.Tables[0].Rows[i]["chuneirong"].ToString()));
objRange.WrapText = true;
}
#endregion
// Save the workbook and quit Excel.
objBook.SaveAs(strSampleFolderCom + "xxx.xls", objOpt, objOpt,
objOpt, objOpt, objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false, objOpt, objOpt);
objExcel.Quit();
//以下释放excel进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);
objBook = null;
objBooks = null;
objSheets = null;
objSheet = null;
objExcel = null;
objRange = null;
pnlLoad.Visible = false; //把操作Excel文件用到的对象实例全部释放。
//然后资源回收!
GC.Collect();
}}
忘记了,用的时候要调用
using Excel=Microsoft.Office.Interop.Excel;
方法应该在上方//获取所需要的参数
private object objOpt = System.Reflection.Missing.Value;
private object strSampleFolderCom = "F:\\Excel\\";
Microsoft.Office.Interop.Excel.Workbook wkbook = apc.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet wksheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbook.ActiveSheet; int rowIndex = 2;
int colIndex = 0; wksheet.get_Range(apc.Cells[1, 1], apc.Cells[dataTable.Rows.Count, dataTable.Columns.Count]).NumberFormat = "@"; //取得列标题
foreach (DataColumn dc in dataTable.Columns)
{
colIndex++;
wksheet.Cells[1, colIndex] = dc.ColumnName;
} //取得表格中数据
foreach (DataRow dr in dataTable.Rows)
{
colIndex = 1;
foreach (DataColumn dc in dataTable.Columns)
{
if (dc.DataType == System.Type.GetType("System.DateTime"))
{
apc.Cells[rowIndex, colIndex] = "'" + (Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd");
}
else
if (dc.DataType == System.Type.GetType("System.String"))
{
apc.Cells[rowIndex, colIndex] = "'" + dr[dc.ColumnName].ToString();
}
else
{
apc.Cells[rowIndex, colIndex] = "'" + dr[dc.ColumnName].ToString();
} wksheet.get_Range(apc.Cells[rowIndex, colIndex], apc.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; colIndex++;
}
rowIndex++;
} //设置表格样式
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, dataTable.Columns.Count]).Interior.ColorIndex = 20;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, dataTable.Columns.Count]).Font.ColorIndex = 3;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, dataTable.Columns.Count]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
wksheet.get_Range(apc.Cells[1, 1], apc.Cells[dataTable.Rows.Count, dataTable.Columns.Count]).Columns.AutoFit(); if (File.Exists(fileName))
{
File.Delete(fileName);
}
wksheet.Name = "统计结果";
//wkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // wkbook.Close(Type.Missing, Type.Missing, Type.Missing);
//apc.Quit();
wkbook = null;
apc = null;
GC.Collect();
用户都从网页界面操作。点击按钮就把
数据导入到模板excel直接打印出来,不要其他中间复杂过程