asp.net Excel

http://files.cnblogs.com/aland-liu/MyXls.SL2.rar
 
方法3：
 
         private void ExportExcel(string fileName, System.Data.DataTable dt)
        {
            try
            {
                XlsDocument xls = new XlsDocument();
                xls.FileName = fileName;
 
                string s = fileName;
                s = s.Substring(s.LastIndexOf('\\') + 1, s.LastIndexOf('.') - s.LastIndexOf('\\') - 1);
                org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets.AddNamed(s);
 
                ColumnInfo cinfo = new ColumnInfo(xls, sheet);
                cinfo.Collapsed = true;
                cinfo.ColumnIndexStart = 0;
                cinfo.ColumnIndexEnd = (ushort)dt.Columns.Count;
                sheet.AddColumnInfo(cinfo);
 
                XF cellXF = xls.NewXF();
                cellXF.VerticalAlignment = VerticalAlignments.Centered;
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
                //cellXF.Font.Bold = true;
 
                Cells cells = sheet.Cells;
 
                for (int i = 1; i <= dt.Columns.Count; i++)
                {
                    cells.Add(1, i, dt.Columns[i - 1].ColumnName, cellXF);
                }
 

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cells.Add(2 + i, 1 + j, dt.Rows[i][j].ToString(), cellXF);
                    }
                }
 
                xls.Save();
                //System.Diagnostics.Process.Start(fileName);
            }
            catch
            {
            }
        }

 

 

 

 

 

 

 

 

1,首先要导入Com文件Microsoft Excel 11.0 Object Library.
2,要添加Interop.Excel.dll文件
http://files.cnblogs.com/ghostljj/Interop.Excel.rar
3.执行下面步骤
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">要导出的DataTable</param>
public void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null) return;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
// lblMsg.Text = "无法创建Excel对象，可能您的机子未安装Excel";
lblMsg.Text = GetLocalResourceObject("noexcel").ToString();
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;

//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高

}
//写入内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}

range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight =Excel.XlBorderWeight.xlThin;
}

try
{
workbook.Saved = true;
workbook.SaveCopyAs(System.Web.HttpRuntime.AppDomainAppPath +"XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name +".xls");
}
catch (Exception ex)
{
//lblMsg.Text = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
lblMsg.Text = GetLocalResourceObject("error").ToString() +"\n" + ex.Message;
}

workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();

xlApp.Quit();

int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁

#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion

//提供下载
BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response,"ReportToExcel.xls"
, System.Web.HttpRuntime.AppDomainAppPath +"XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name +".xls", 1024000);
}

//--------------------------------------------------------
4.如果是放在IIS中,现在是不能到出的,还要配置一下
方案一:在Web.config中添加
<system.web>
<identity impersonate="true" userName="管理员名" password="密码" />
<system.web>
方案二:
(1)在运行->dcomcnfg打开组件服务
(2) 在 控制台根目录->组件服务->计算机->我的电脑->DCOM配置->Microsoft Excel应用程序->属性->安全
(3)启动和激活权限->使用自定义->添加一个ASPNET用户,还有打开本地启动和本地激活
访问权限->使用自定义->添加一个ASPNET用户,还有打开本地访问和远程访问