Excel完美导出怎么弄啊! 我从datatable导出数据到Excel中,在Excel2007中打开,提示不是正确的Excel格式,但是能打开查看,然后另存为再打开就没问题了。在Excel2003中就直接乱码了!第一次处理这东西,希望弄过的朋友给我份完美导出的完整代码学习下!最好能有各种属性设置的,比如表头颜色之类的! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application(); SaveFileDialog savefiledialog = new SaveFileDialog(); System.Reflection.Missing miss = System.Reflection.Missing.Value; appexcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbookdata; Microsoft.Office.Interop.Excel.Worksheet worksheetdata; Microsoft.Office.Interop.Excel.Range rangedata; //设置对象不可见 appexcel.Visible = false; System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us"); workbookdata = appexcel.Workbooks.Add(miss); worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss); //给工作表赋名称 worksheetdata.Name = "saved"; for (int i = 0; i < dt.Columns.Count; i++) { worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString(); } //因为第一行已经写了表头,所以所有数据都应该从a2开始 rangedata = worksheetdata.get_Range("a2", miss); Microsoft.Office.Interop.Excel.Range xlrang = null; //irowcount为实际行数,最大行 int irowcount = dt.Rows.Count; int iparstedrow = 0, icurrsize = 0; //ieachsize为每次写行的数值,可以自己设置 int ieachsize = 10000; //icolumnaccount为实际列数,最大列数 int icolumnaccount = dt.Columns.Count; //在内存中声明一个ieachsize×icolumnaccount的数组,ieachsize是每次最大存储的行数,icolumnaccount就是存储的实际列数 object[,] objval = new object[ieachsize, icolumnaccount]; icurrsize = ieachsize; while (iparstedrow < irowcount) { if ((irowcount - iparstedrow) < ieachsize) icurrsize = irowcount - iparstedrow; //用for循环给数组赋值 for (int i = 0; i < icurrsize; i++) { for (int j = 0; j < icolumnaccount; j++) objval[i, j] = dt.Rows[i + iparstedrow][j].ToString(); System.Windows.Forms.Application.DoEvents(); } string X = "A" + ((int)(iparstedrow + 2)).ToString(); string col = ""; if (icolumnaccount <= 26) { col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString(); } else { col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString(); } xlrang = worksheetdata.get_Range(X, col); // 调用range的value2属性,把内存中的值赋给excel xlrang.Value2 = objval; iparstedrow = iparstedrow + icurrsize; 太佩服2楼的了。你的代码写的太牛B了。简直到了出神入化的地步,能写的这么详细。能和教我的·NET老师想媲美了。 纠结蛋疼,我是弄的Web ,,,,, Spread .NET 6 SP3 完美了 似乎这代码运行完以后会在电脑中留2个Excel程序!。 我也遇到过,就是导入正常。导出后在导入就不行了。就是Excel表格换个表格名就不行了。不是文件名! 用MyXls 非常好用 不用调服务器com组件string filename = "预付保费网银" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_sssss") + ".xls"; XlsDocument xls = new XlsDocument(); xls.FileName = filename; Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1"); XF xfdate = xls.NewXF(); xfdate.Format = StandardFormats.Decimal_2; Cells cells = sheet.Cells; cells.Add(1, 1, "文件类型"); cells.Add(1, 2, "ExtPayBatch"); cells.Add(2, 1, "标题"); cells.Add(2, 2, "支付转账经办"); string[] titl = { "付款账号", "币种", "本/他行标志", "收款单位编号", "收款人账号", "收款人名称", "支付联行号", "开户网点名称", "开户地所在省/市/自治区", "开户地所在市/县", "支付方式", "支付金额", "预约支付标志", "预约支付日期", "预约支付时间", "摘要", "取票人证件种类", "取票人号码", "取票人姓名", "备注" }; for (int i = 0; i < titl.Length; i++) { cells.Add(4, i + 1, titl[i]); } string sql = "select fk_zh,sk_zh,sk_name,sk_hh,sk_kh,fee_pay from yfbf_fk where id in (" + ids + ")"; DataTable dt = DBUtility.DbHelperOra.Query(sql).Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { cells.Add(i + 5, 1, dt.Rows[i]["fk_zh"]); cells.Add(i + 5, 2, "人民币"); cells.Add(i + 5, 3, "其他银行账户"); cells.Add(i + 5, 5, dt.Rows[i]["sk_zh"]); cells.Add(i + 5, 6, dt.Rows[i]["sk_name"]); cells.Add(i + 5, 7, dt.Rows[i]["sk_hh"]); cells.Add(i + 5, 8, dt.Rows[i]["sk_kh"]); cells.Add(i + 5, 11, "大额支付"); cells.Add(i + 5, 12, Convert.ToDecimal(dt.Rows[i]["fee_pay"]), xfdate); cells.Add(i + 5, 13, "立即支付"); cells.Add(i + 5, 16, "转款"); } string path = HttpContext.Current.Server.MapPath("~") + "\\YFBF\\excel"; xls.Save(path); return path + "\\" + filename; 写excel的xml格式参考:http://topic.csdn.net/u/20080117/10/965ad972-08c7-42b7-8969-f2ad24ad4175.html 各位大大来帮帮忙,本人菜鸟 如何用asp.net进行手机开发 一个事务进程里面,重复对一条记录进行update,可否?? 还是关于选项卡问题!!急 知道LISTBOX控件中的一个字符串,如何获取该字符串在LISTBOX中的编号呀?? |zyciis| 网站数据被注入了<script .... /> 现在怎么防止 关于服务器控件id的问题?? 如何用asp.net中实现登陆的时候用SSL(HTTPS) 关于时间显示格式的问题~~ 急,从别的地方copy过来的项目,为什麽不能打开! "创建控件时出错"-----未将对象引用到对象的实例 一段POST提交的.NET代码,报错,请大家帮忙查看,谢谢
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata;
Microsoft.Office.Interop.Excel.Range rangedata;
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookdata = appexcel.Workbooks.Add(miss);
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = "saved";
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头,所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数,最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值,可以自己设置
int ieachsize = 10000;
//icolumnaccount为实际列数,最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组,ieachsize是每次最大存储的行数,icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
icurrsize = irowcount - iparstedrow;
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
// 调用range的value2属性,把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
string filename = "预付保费网银" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_sssss") + ".xls";
XlsDocument xls = new XlsDocument();
xls.FileName = filename;
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
XF xfdate = xls.NewXF();
xfdate.Format = StandardFormats.Decimal_2;
Cells cells = sheet.Cells;
cells.Add(1, 1, "文件类型"); cells.Add(1, 2, "ExtPayBatch");
cells.Add(2, 1, "标题"); cells.Add(2, 2, "支付转账经办");
string[] titl = { "付款账号", "币种", "本/他行标志", "收款单位编号", "收款人账号", "收款人名称", "支付联行号", "开户网点名称", "开户地所在省/市/自治区", "开户地所在市/县", "支付方式", "支付金额", "预约支付标志", "预约支付日期", "预约支付时间", "摘要", "取票人证件种类", "取票人号码", "取票人姓名", "备注" };
for (int i = 0; i < titl.Length; i++)
{
cells.Add(4, i + 1, titl[i]);
} string sql = "select fk_zh,sk_zh,sk_name,sk_hh,sk_kh,fee_pay from yfbf_fk where id in (" + ids + ")";
DataTable dt = DBUtility.DbHelperOra.Query(sql).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
cells.Add(i + 5, 1, dt.Rows[i]["fk_zh"]);
cells.Add(i + 5, 2, "人民币");
cells.Add(i + 5, 3, "其他银行账户");
cells.Add(i + 5, 5, dt.Rows[i]["sk_zh"]);
cells.Add(i + 5, 6, dt.Rows[i]["sk_name"]);
cells.Add(i + 5, 7, dt.Rows[i]["sk_hh"]);
cells.Add(i + 5, 8, dt.Rows[i]["sk_kh"]);
cells.Add(i + 5, 11, "大额支付");
cells.Add(i + 5, 12, Convert.ToDecimal(dt.Rows[i]["fee_pay"]), xfdate);
cells.Add(i + 5, 13, "立即支付");
cells.Add(i + 5, 16, "转款"); }
string path = HttpContext.Current.Server.MapPath("~") + "\\YFBF\\excel";
xls.Save(path);
return path + "\\" + filename;
http://topic.csdn.net/u/20080117/10/965ad972-08c7-42b7-8969-f2ad24ad4175.html