导出数据到Excel表（依赖本地Office）

最新推荐文章于 2023-07-17 18:08:56 发布

jota

最新推荐文章于 2023-07-17 18:08:56 发布

阅读量749

点赞数

分类专栏： C# 文章标签： C# WPF 导出 execl

本文链接：https://blog.csdn.net/shaoyiju/article/details/49981115

版权

C# 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

说明：这种方法还不是最好的方法，因为这种导出方式需要本地有安装Ofiice，如果本地没有安装Office的话，将会导出失败

<span style="font-family:Courier New;">private void button1_Click(object sender, RoutedEventArgs e)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();


            dt.TableName = "LogTable";


            dt.Columns.Add("序号", typeof(string));
            dt.Columns.Add("用户", typeof(string));
            dt.Columns.Add("日期", typeof(string));
            dt.Columns.Add("操作", typeof(string));


            DataRow dr = dt.NewRow();
            dr[0] = "1";
            dr[1] = "admin";
            dr[2] = "2015-11-22 16:30:00";
            dr[3] = "登录";


            dt.Rows.Add(dr);


            ds.Tables.Add(dt);


            ExportIntoExcel(ds);
        }


        private void ExportIntoExcel(DataSet ds)
        {
            //打开保存对话框
            Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            dlg.FileName = "ATM操作日志_" + DateTime.Now.ToString("yyyy-MM-dd_HHmmss");
            dlg.Filter = "EXCEL文件(*.xls, *.xlsx)|*.xls;*.xlsx|所有文件(*.*)|*.*";
            Nullable<bool> Res = dlg.ShowDialog();
            if (Res.HasValue)
            {
                if (Res.Value == true)
                {
                    string fileName = dlg.FileName;
                    try
                    {
                        if (DataSetToExcel(fileName, ds))
                        {
                            MessageBox.Show("导出成功");
                        }
                        else
                        {
                            MessageBox.Show("导出失败");
                        }
                    }
                    catch (Exception ex)
                    {
                        string err = ex.Message;
                        return;
                    }
                }
            }
        }


        public bool DataSetToExcel(string strFilePath, DataSet dataSet)
        {
            DataTable dataTable = dataSet.Tables[0];
            int rowNumber = dataTable.Rows.Count;       //不包括字段名
            int columnNumber = dataTable.Columns.Count;
            int colIndex = 0;


            if (rowNumber == 0)
            {
                return false;
            }


            //建立Excel对象 
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            excel.Visible = false;


            Microsoft.Office.Interop.Excel.Range range;


            //生成字段名称 
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }


            object[,] objData = new object[rowNumber, columnNumber];


            for (int r = 0; r < rowNumber; r++)
            {
                for (int c = 0; c < columnNumber; c++)
                {
                    objData[r, c] = dataTable.Rows[r][c];
                }
            }


            // 写入Excel 
            range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
            range.NumberFormat = "@";   //设置单元格为文本格式
            range.Value2 = objData;
            worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]].NumberFormat = "yyyy-m-d h:mm";


            int formatNum = GetFormatNum();


            workbook.SaveAs(strFilePath, formatNum, Missing.Value, Missing.Value, Missing.Value, 
                Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


            try
            {
                workbook.Saved = true;
                excel.UserControl = false;
                //excelapp.Quit();
            }
            catch (Exception exception)
            {
                return false;
            }
            finally
            {
                workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, 
                    Missing.Value);
                excel.Quit();
            }


            return true;
        }


        public int GetFormatNum()
        {
            int FormatNum;
            string strVersion = string.Empty;
            Microsoft.Office.Interop.Excel.Application Application = 
                new Microsoft.Office.Interop.Excel.Application();


            //激活工作簿
            Microsoft.Office.Interop.Excel.Workbook workbook = 
                (Microsoft.Office.Interop.Excel.Workbook)Application.Workbooks.Add(Missing.Value);


            //给工作簿添加一个sheet
            Microsoft.Office.Interop.Excel.Worksheet worksheet = 
                (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];


            //获取你使用的excel 的版本号
            strVersion = Application.Version;


            if (Convert.ToDouble(strVersion) < 12)
            {
                //You use Excel 97-2003
                FormatNum = -4143;
            }
            else
            {
                //you use excel 2007 or later
                FormatNum = 56;
            }


            return FormatNum;
        }
</span>