C#导出数据到Excel文件

C#导出数据到Excel文件

/// <summary>
    /// 导出到Excel类，项目需引用Microsodt.Office.Interop.Excel,
    /// 类文件需using System.Data与System.Windows.Forms命名空间
    /// </summary>
    public class CToExcel
    {
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="fileName">默认文件名</param>
        /// <param name="listView">数据源，一个页面上的ListView控件</param>
        /// <param name="titleRowCount">标题占据的行数，为0表示无标题</param>
        public void ExportExcel(string fileName, System.Windows.Forms.ListView listView,int titleRowCount)
        {
            string saveFileName = "";
            //bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
            Microsoft.Office.Interop.Excel.Application xlApp;
            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
  
            }
            catch (Exception)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            finally
            {
            }
  
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            //写Title
            if(titleRowCount!=0)
                MergeCells(worksheet, 1, 1, titleRowCount, listView.Columns.Count, listView.Tag.ToString());
  
            //写入列标题
            for (int i = 0; i <= listView.Columns.Count - 1; i++)
            {
                worksheet.Cells[titleRowCount+1, i + 1] = listView.Columns[i].Text;
  
            }
            //写入数值
            for (int r = 0; r <= listView.Items.Count - 1; r++)
            {
                for (int i = 0; i <= listView.Columns.Count - 1; i++)
                {
                    worksheet.Cells[r + titleRowCount+2, i + 1] = listView.Items[r].SubItems[i].Text;
                }
                System.Windows.Forms.Application.DoEvents();
            }
  
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            //if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
            //{
            //    Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
            //    rg.NumberFormat = "00000000";
            //}
  
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！n" + ex.Message);
                }
  
            }
            //else
            //{
            //    fileSaved = false;
            //}
            xlApp.Quit();
            GC.Collect();//强行销毁
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
            MessageBox.Show(fileName + "导出到Excel成功", "提示", MessageBoxButtons.OK);
        }
        /// <summary>
        /// DataTable导出到Excel
        /// </summary>
        /// <param name="fileName">默认的文件名</param>
        /// <param name="dataTable">数据源,一个DataTable数据表</param>
        /// <param name="titleRowCount">标题占据的行数，为0则表示无标题</param>
        public void ExportExcel(string fileName,System.Data.DataTable dataTable,int titleRowCount)
        {
            string saveFileName = "";
            //bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
            Microsoft.Office.Interop.Excel.Application xlApp;
            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
  
            }
            catch (Exception)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            finally
            {
            }
  
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            //写Title
            if(titleRowCount!=0)
                MergeCells(worksheet, 1, 1, titleRowCount, dataTable.Columns.Count, dataTable.TableName);
  
            //写入列标题
            for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
            {
                worksheet.Cells[titleRowCount+1, i + 1] = dataTable.Columns[i].ColumnName;
  
            }
            //写入数值
            for (int r = 0; r <= dataTable.Rows.Count - 1; r++)
            {
                for (int i = 0; i <= dataTable.Columns.Count - 1; i++)
                {
                    worksheet.Cells[r +titleRowCount+ 2, i + 1] = dataTable.Rows[r][i].ToString();
                }
                System.Windows.Forms.Application.DoEvents();
            }
  
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            //if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
            //{
            //    Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
            //    rg.NumberFormat = "00000000";
            //}
  
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！n" + ex.Message);
                }
  
            }
            //else
            //{
            //    fileSaved = false;
            //}
            xlApp.Quit();
            GC.Collect();//强行销毁
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
            MessageBox.Show(fileName + "导出到Excel成功", "提示", MessageBoxButtons.OK);
        }
        /// <summary>  
        /// 合并单元格，并赋值，对指定WorkSheet操作  
        /// </summary>  
        /// <param name="sheetIndex">WorkSheet索引</param>  
        /// <param name="beginRowIndex">开始行索引</param>  
        /// <param name="beginColumnIndex">开始列索引</param>  
        /// <param name="endRowIndex">结束行索引</param>  
        /// <param name="endColumnIndex">结束列索引</param>  
        /// <param name="text">合并后Range的值</param>  
        public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
        {
            Microsoft.Office.Interop.Excel.Range range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
  
            range.ClearContents();  //先把Range内容清除，合并才不会出错  
            range.MergeCells = true;
            range.Value2 = text;
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
        }
    }

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。