dataset 导入 excel参考 http://topic.csdn.net/t/20050119/16/3738430.html
来源:互联网 发布:怎么开淘宝找货源 编辑:程序博客网 时间:2024/06/01 11:50
你可以dataset 导入 excel参考
http://community.csdn.net/Expert/topic/3077/3077526.xml?temp=.8746912
http://www.dev-club.com/club/bbs/showEssence.asp?id=26350
http://dev.csdn.net/Develop/article/18/18623.shtm
http://community.csdn.net/Expert/topic/3112/3112296.xml?temp=.926861
http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50
http://expert.csdn.net/Expert/TopicView1.asp?id=2928057
www.foxhis.com/powermjtest/
原文代码:
private void Button1_Click(object sender, System.EventArgs e)
{
//写入Excel的方法:
//定义需要参数。
string SourceFile="Data.XLS"; //源文件名称。
string TemplatePath=Server.MapPath("ExcelTemplate"); //存放源文件的文件夹路径。
string DownloadPath=Server.MapPath("ExcelDownload"); //副本的文件夹路径。
//副本的文件名。
string TempFileName = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".XLS";
object missing = System.Reflection.Missing.Value;
Excel.Application myExcel=new Excel.Application();
//打开新文件
myExcel.Application.Workbooks.Open(TemplatePath+"//"+SourceFile,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing, missing,missing);
Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet curSheet = (Excel.Worksheet)myBook.Sheets[2];
string DownloadFilePath=DownloadPath+"//"+TempFileName;
int i=0;
while (i<=10)
{
myExcel.Cells[4+i,2]=i.ToString();
myExcel.Cells[4+i,3]=i.ToString();
myExcel.Cells[4+i,4]=i.ToString();
myExcel.Cells[4+i,5]=i.ToString();
myExcel.Cells[4+i,6]=i.ToString();
i++;
}
myBook.Saved=true;
//myBook.SaveAs(DownloadFilePath,missing,"","",false,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,missing,missing);
myBook.PrintPreview(0);
//myBook.PrintOut(missing,missing,missing,missing,missing,missing,missing,missing);
myBook.Close(false, null,null);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
myExcel = null;
GC.Collect();
//Response.Redirect("ExcelDownload//"+TempFileName); //下载文件
}
如果要设置颜色,
public void OutputExcel(DataView dv,string str)
{
//
// TODO: 在此处添加构造函数逻辑
//
//dv为要输出到Excel的数据,str为标题名称
GC.Collect();
Application excel;// = new Application();
int rowIndex=4;
int colIndex=1;
_Workbook xBk;
_Worksheet xSt;
excel= new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//
//取得标题
//
foreach(DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4,colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
//
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if(col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum,2] = "合计";
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色,共计有56种
//
//取得整个报表的标题
//
excel.Cells[2,2] = str;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
//
//显示效果
//
excel.Visible=true;
//xSt.Export(Server.MapPath(".")+"//"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
xBk.SaveCopyAs(Server.MapPath(".")+"//"+this.xlfile.Text+".xls");
ds = null;
xBk.Close(false, null,null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath(this.xlfile.Text+".xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset="GB2312";
Response.ContentEncoding=System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
http://community.csdn.net/Expert/topic/3077/3077526.xml?temp=.8746912
http://www.dev-club.com/club/bbs/showEssence.asp?id=26350
http://dev.csdn.net/Develop/article/18/18623.shtm
http://community.csdn.net/Expert/topic/3112/3112296.xml?temp=.926861
http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50
http://expert.csdn.net/Expert/TopicView1.asp?id=2928057
www.foxhis.com/powermjtest/
原文代码:
private void Button1_Click(object sender, System.EventArgs e)
{
//写入Excel的方法:
//定义需要参数。
string SourceFile="Data.XLS"; //源文件名称。
string TemplatePath=Server.MapPath("ExcelTemplate"); //存放源文件的文件夹路径。
string DownloadPath=Server.MapPath("ExcelDownload"); //副本的文件夹路径。
//副本的文件名。
string TempFileName = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".XLS";
object missing = System.Reflection.Missing.Value;
Excel.Application myExcel=new Excel.Application();
//打开新文件
myExcel.Application.Workbooks.Open(TemplatePath+"//"+SourceFile,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing, missing,missing);
Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet curSheet = (Excel.Worksheet)myBook.Sheets[2];
string DownloadFilePath=DownloadPath+"//"+TempFileName;
int i=0;
while (i<=10)
{
myExcel.Cells[4+i,2]=i.ToString();
myExcel.Cells[4+i,3]=i.ToString();
myExcel.Cells[4+i,4]=i.ToString();
myExcel.Cells[4+i,5]=i.ToString();
myExcel.Cells[4+i,6]=i.ToString();
i++;
}
myBook.Saved=true;
//myBook.SaveAs(DownloadFilePath,missing,"","",false,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,missing,missing);
myBook.PrintPreview(0);
//myBook.PrintOut(missing,missing,missing,missing,missing,missing,missing,missing);
myBook.Close(false, null,null);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
myExcel = null;
GC.Collect();
//Response.Redirect("ExcelDownload//"+TempFileName); //下载文件
}
如果要设置颜色,
public void OutputExcel(DataView dv,string str)
{
//
// TODO: 在此处添加构造函数逻辑
//
//dv为要输出到Excel的数据,str为标题名称
GC.Collect();
Application excel;// = new Application();
int rowIndex=4;
int colIndex=1;
_Workbook xBk;
_Worksheet xSt;
excel= new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//
//取得标题
//
foreach(DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4,colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
//
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if(col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum,2] = "合计";
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色,共计有56种
//
//取得整个报表的标题
//
excel.Cells[2,2] = str;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
//
//显示效果
//
excel.Visible=true;
//xSt.Export(Server.MapPath(".")+"//"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
xBk.SaveCopyAs(Server.MapPath(".")+"//"+this.xlfile.Text+".xls");
ds = null;
xBk.Close(false, null,null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath(this.xlfile.Text+".xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset="GB2312";
Response.ContentEncoding=System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
- dataset 导入 excel参考 http://topic.csdn.net/t/20050119/16/3738430.html
- http://topic.csdn.net/t/20030106/17/1332084.html
- http://topic.csdn.net/t/20050406/11/3912976.html
- http://topic.csdn.net/t/20031204/21/2528768.html
- http://topic.csdn.net/t/20061023/19/5103384.html
- 将字典对象存入到Application中 (参考http://topic.csdn.net/t/20060524/15/4775541.html)
- 如何把标准输出重定向为指定缓冲区 http://topic.csdn.net/t/20020111/16/472320.html
- enum遍历http://topic.csdn.net/t/20030613/00/1909808.html
- 9003错误,sqlserver无法启动,HELP!!!十万火急~~~!!http://topic.csdn.net/t/20060708/09/4867769.html
- 改变表的所有者http://topic.csdn.net/t/20020221/19/536354.html
- 让程序只启动一个实例(from: http://topic.csdn.net/t/20010427/08/106676.html)
- cxGrid使用技巧 转载自http://topic.csdn.net/t/20060106/09/4500997.html#
- temp_http://topic.csdn.net/t/20040130/02/2687082.html
- 请问winxp家庭版下能不能装IIs,怎么装?(http://topic.csdn.net/t/20030306/15/1499030.html)
- 怎么在获得一个库里面所有的表名?http://topic.csdn.net/t/20020801/09/915234.html
- 清问一个Windows的程序的LDT是谁创建的?http://topic.csdn.net/t/20061228/17/5262577.html
- 大公司的php面试题http://topic.csdn.net/u/20071202/16/e53996de-4725-4dfa-bf14-c197afeed93c.html
- 使用SQL Server操作Excel表格(转自:http://topic.csdn.net/u/20101130/19/8ddba4c4-217b-4804-8ddb-3de57927f640.html)
- GCC 内嵌汇编实现动态调用 windows API
- smarty实例(一)
- 《Agile Web Development with Rails》读书笔记(一)
- asp.net修改web.config
- AS3中的如何动态加载影片剪辑
- dataset 导入 excel参考 http://topic.csdn.net/t/20050119/16/3738430.html
- IM互通新方案-GTalk to VoIP回拨服务
- 冒泡排序的一个例子
- 我的一些相册MV
- 正则表达式实例分析(转)
- Javascript 版本的 Sprintf 字符串格式化函数
- 即兴创意、情动流泪大奖揭晓!zuzong 与 gxlgp 夺魁!"每逢佳节倍思亲" 祝大家中秋甜蜜, 月饼快乐! :-)
- 给GridView分页的页码添加事件
- 从别人设计的系统看自己应该注意什么