csharp: Export DataSet into Excel and import all the Excel sheets to DataSet

原创 2015年07月10日 17:30:07
/// <summary>
       /// Export DataSet into Excel
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void Form3_Load(object sender, EventArgs e)
       {
           //Create an Emplyee DataTable
           DataTable employeeTable = new DataTable("Employee");
           employeeTable.Columns.Add("Employee ID");
           employeeTable.Columns.Add("Employee Name");
           employeeTable.Rows.Add("1", "涂聚文");
           employeeTable.Rows.Add("2", "geovindu");
           employeeTable.Rows.Add("3", "李蘢怡");
           employeeTable.Rows.Add("4", "ноппчц");
           employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц");
           //Create a Department Table
           DataTable departmentTable = new DataTable("Department");
           departmentTable.Columns.Add("Department ID");
           departmentTable.Columns.Add("Department Name");
           departmentTable.Rows.Add("1", "IT");
           departmentTable.Rows.Add("2", "HR");
           departmentTable.Rows.Add("3", "Finance");
 
           //Create a DataSet with the existing DataTables
           DataSet ds = new DataSet("Organization");
           ds.Tables.Add(employeeTable);
           ds.Tables.Add(departmentTable);
 
           ExportDataSetToExcel(ds);
       }
 
       /// <summary>
       /// This method takes DataSet as input paramenter and it exports the same to excel
       /// </summary>
       /// <param name="ds"></param>
       private void ExportDataSetToExcel(DataSet ds)
       {
           //Creae an Excel application instance
           //EXCEL组件接口
           System.Reflection.Missing miss = System.Reflection.Missing.Value;
           Excel.Application excelApp = new Excel.Application();
           excelApp.Application.Workbooks.Add(true);
           string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
           string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");
           //Create an Excel workbook instance and open it from the predefined location
           //Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(FilePath);
           Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks;
           Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss);
           foreach (DataTable table in ds.Tables)
           {
               //Add a new worksheet to workbook with the Datatable name
               Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
               excelWorkSheet.Name = table.TableName;
 
               for (int i = 1; i < table.Columns.Count + 1; i++)
               {
                   excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
               }
 
               for (int j = 0; j < table.Rows.Count; j++)
               {
                   for (int k = 0; k < table.Columns.Count; k++)
                   {
                       excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                   }
               }
           }
 
           excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
           excelWorkBook.Close(false, miss, miss);
           //excelWorkBook.Save();
           books.Close();
           excelApp.Quit();
 
       }


/// <summary>
       /// EXCEL表的所有工作表导入到DataSet
       /// 涂聚文 Microsoft.ACE.OLEDB.12.0
       /// Geovin Du
       /// </summary>
       /// <param name="fileName"></param>
       /// <returns></returns>
       static DataSet  ImportExcelParse(string fileName)
       {
           string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
 
 
           DataSet data = new DataSet();
 
           foreach (var sheetName in GetExcelSheetNames(connectionString))
           {
               using (OleDbConnection con = new OleDbConnection(connectionString))
               {
                   var dataTable = new DataTable();
                   string query = string.Format("SELECT * FROM [{0}]", sheetName);
                   con.Open();
                   OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
                   adapter.Fill(dataTable);
                   data.Tables.Add(dataTable);
               }
           }
 
           return data;
       }
       /// <summary>
       /// 读取所有工作表名
       /// </summary>
       /// <param name="connectionString"></param>
       /// <returns></returns>
       static string[] GetExcelSheetNames(string connectionString)
       {
           OleDbConnection con = null;
           DataTable dt = null;
           con = new OleDbConnection(connectionString);
           con.Open();
           dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
           if (dt == null)
           {
               return null;
           }
 
           String[] excelSheetNames = new String[dt.Rows.Count];
           int i = 0;
 
           foreach (DataRow row in dt.Rows)
           {
               excelSheetNames[i] = row["TABLE_NAME"].ToString();
               i++;
           }
 
           return excelSheetNames;
       }


/// <summary>
       /// 添加图片
       /// 涂聚文
       /// </summary>
       /// <param name="dt"></param>
       protected void ExportExcelImg(System.Data.DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();   
            if (xlApp == null)
            {
                return;
            }
            xlApp.Application.Workbooks.Add(true);
            string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
            string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");
 
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            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];
            Microsoft.Office.Interop.Excel.Range range;
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            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 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
            }
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    try
                    {
                        worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                    }
                    catch
                    {
                        worksheet.Cells[r + 2, i + 1] =
               dt.Rows[r][i].ToString().Replace("=", "");
                    }
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            string strimg =Application.StartupPath+@"/IMG_6851.JPG";
            worksheet.Shapes.AddPicture(strimg, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);
           //在添加的图片上加文字
            worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "涂聚文写上", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);
            xlApp.Visible = true;
 
            workbook.SaveAs(FilePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
            workbook.Close(false, miss, miss);
            //excelWorkBook.Save();
            workbooks.Close();
            xlApp.Quit();
        }



版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

csharp: Export or Import excel using NPOI

 excel 2003: using System; using System.Collections.Generic; using System.ComponentModel; using ...

bdgrid+dataset to excel

  • 2011年09月27日 11:49
  • 3KB
  • 下载

DataSet To Excel&Chart

using System;using System.Drawing;using System.Collections.Generic;using System.Text;using System.Ru...

Dataset To Excel

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;...

smartGWT, OSGi export to excel and PDF

刚给公司一个web项目添加了前台export表格到excel以及pdf的功能,下面把实现过程记录在这篇blog中。 一些背景交待: 1、UI技术使用的是smartgwt,MVP模式,表格List...

Export GridView with Images from database to Word Excel and PDF Formats

转自: http://www.aspsnippets.com/Articles/Export-GridView-with-Images-from-database-to-Word-Excel-and-...
  • lJean
  • lJean
  • 2011年10月18日 17:55
  • 790

Excel 转Dataset报错

  • 2016年10月18日 11:27
  • 104KB
  • 下载

DataSet数据写入Excel

  • 2008年06月26日 14:19
  • 2KB
  • 下载

黑马程序员_WinForm导出DataSet到Excel中

/// /// 导出DataSet到Excel(如果DataSet中有多个DataTable,则会导出到不同的sheet中,但只生成一个excel文件) /// ...

导出DataSet到Excel

  • 2008年06月17日 10:05
  • 1KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:csharp: Export DataSet into Excel and import all the Excel sheets to DataSet
举报原因:
原因补充:

(最多只允许输入30个字)