C# 导出excle

----------------------------------Web导出

导出时出现的问题：

1. 导出多条数据有问题；

2. 导出多条数据没有问题，但在导出单条数据时有问题；

3. 导出乱码，个别电脑导出韩文；

分析：可能跟客户端的数据编码有关。导出方法经过不下10个版本的修正；

比较好的一篇文章是：http://www.cnblogs.com/top5/archive/2010/02/16/1668801.html

我只取了一种方法，方法如下：

/// <summary>
/// Web页面的EXCEL导出
/// </summary>
/// <param name="dt">需要导出的数据表</param>
///
public static void ExportExcel(DataTable dt, string code)
{
GridView gv1 = new GridView();

gv1.DataSource = dt;
gv1.DataBind();
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = code;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.GetEncoding(code)));
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(code);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv1.RenderControl(htw);
HttpContext.Current.Response.Write("<html><head><meta http-equiv=Content-Type content="text/html; charset=" + code + ""></head><body>");
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.Write("</body></html>");
HttpContext.Current.Response.End();
}

-------------------------------------Windows导出

        public static void CreateXls(string FileAddr, DateTime date, System.Data.DataTable dt)
        {
            Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Workbooks oBooks;
            Workbook oBook;
            Sheets oSheets;
            Worksheet oSheet;
            Range oCells;
            string sFile = "", sTemplate = "";

            sFile = string.Format(FileAddr + date.GetYYYYMMDD() + "\NG_Cash_{0}.xlsx", date.GetYYYYMMDD());
            sTemplate = FileAddr + "CashFileTemplate.xlsx";
            if (!System.IO.File.Exists(sTemplate))
            {
                System.IO.File.Create(sTemplate);
            }
            //
            oExcel.Visible = false;
            oExcel.DisplayAlerts = false;
            //// 定义一个新的工作簿
            oBooks = oExcel.Workbooks;
            oBooks.Open(sTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            oBook = oBooks.get_Item(1);
            oSheets = oBook.Worksheets;
            oSheet = (Worksheet)oSheets.get_Item(1);
            // 命名该sheet
            //oSheet.Name = "CashRecords";

            oCells = oSheet.Cells;
            //调用 dumpdata过程，将数据导入到Excel中去
            DumpData(dt, oCells);
            ////保存
            //oSheet.SaveAs(sFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing,
            //Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
            oBook.SaveCopyAs(sFile);

            oBook.Close(false, Type.Missing, Type.Missing);
            //退出Excel，并且释放调用的COM资源
            oExcel.Quit();

            GC.Collect();
            KillProcess("Excel");

        }
        private static void DumpData(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Range oCells)
        {
            DataRow dr;
            object[] ary;

            int iRow;
            int iCol;

            ////import the fields' name 
            //for (iCol = 0; iCol <= dt.Columns.Count - 1; iCol++)
            //{
            //    oCells[2, iCol + 1] = dt.Columns[iCol].ToString();
            //}

            //import the values
            for (iRow = 0; iRow <= dt.Rows.Count - 1; iRow++)
            {
                dr = dt.Rows[iRow];
                ary = dr.ItemArray;
                oCells[iRow + 2, 1] = "PSHK";
                oCells[iRow + 2, 2] = DateTime.Parse( ary[2].ToString()).ToString("yyyy-MM-dd");
                oCells[iRow + 2, 3] = DateTime.Parse(ary[2].ToString()).ToString("yyyy-MM-dd");
                oCells[iRow + 2, 4] = DateTime.Parse(ary[2].ToString()).ToString("yyyy-MM-dd");
                oCells[iRow + 2, 5] = ary[1].ToString();
                oCells[iRow + 2, 6] = "Cash";
                double amount = double.Parse(ary[6].ToString());
                CashFileData.eTransType TransType = CashFileData.GetTransType(ary[3].ToString());
                if (TransType == CashFileData.eTransType.Exchange)
                {
                    if (amount > 0)
                    {
                        oCells[iRow + 2, 7] = "Cash Transfer In";
                    }
                    else
                    {
                        oCells[iRow + 2, 7] = "Cash Transfer Out";
                    }
                }
                else if (TransType == CashFileData.eTransType.CashIn)
                {
                    oCells[iRow + 2, 7] = "Cash Transfer In";
                }
                else
                {
                    oCells[iRow + 2, 7] = "Cash Transfer Out";
                }
                oCells[iRow + 2, 8] = ary[5].ToString();
                oCells[iRow + 2, 9] = amount.ToString();
                oCells[iRow + 2, 10] = ary[7].ToString();

                //for (iCol = 0; iCol <= ary.GetLength(0) - 1; iCol++)
                //{
                //    oCells[iRow + 3, iCol + 1] = ary[iCol].ToString();
                //    //Response.Write(ary[iCol].ToString() + (char)9);
                //    Console.Write(ary[iCol].ToString() + (char)9);
                //}
            }
        }


        private static void KillProcess(string processName)
        {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName(processName))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        }