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

原创 2015年07月10日 17:30:29
/// <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();
        }


ExportToExcel 一个通用的把dataset导出至EXCEL的类

一个通用类库,可以把dataset导出至EXCEL,优点是不需要安装OFFICE,只需要引用DocumentFormat.OpenXml.DLL WINFORM,WPF 调用方法CreateExcel...
  • eryongyan
  • eryongyan
  • 2013年12月09日 16:38
  • 3164

ASP.NET DataSet数据生成Excel文档导出下载

public partial class am_ElectronicInvoinceExportExcel : BasePage { protected void Page_L...
  • luolei3344
  • luolei3344
  • 2017年03月30日 17:06
  • 379

DataSet导出到Excel,并生成文件(C#实现,可合并行和列)

using System; using System.IO; using System.Data; using System.Reflection; using System.Diagnost...
  • xujunheng
  • xujunheng
  • 2014年09月09日 19:06
  • 959

【C#项目实战】将EXCEl表格导入DataSet

【前言】 还是做的抽奖系统里的, 将人名单导入到DataSet里去。 【正文】         单击导入按钮后 private void btnLook_Click(object send...
  • srk950606
  • srk950606
  • 2017年03月26日 20:24
  • 451

wcf传输Dataset大数据量(转)

http://www.th7.cn/Program/net/201309/148486.shtml   由于WCF不能传输DataTable(不能序列化),所以更多项目中都会使用DataSet作为...
  • zunguitiancheng
  • zunguitiancheng
  • 2014年03月29日 12:13
  • 741

使用NPOI读取Excel数据到DataTable

现在XML文件的存储格式大行其道,但是也不是适用于所有情况,很多单位的数据交换还是使用Excel的形式。这就使得我们 需要读取Excel内的数据,加载到程序中进行处理。但是怎样有效率的读取,怎样使程序...
  • wwei466
  • wwei466
  • 2014年10月07日 17:20
  • 3906

使用EPPLUS快速转换Excel

EPPlus 是使用Open Office XML格式(xlsx)读写Excel 2007 / 2010文件的.net开发库。 Program.cs代码: class Program { ...
  • WuLex
  • WuLex
  • 2017年06月14日 13:35
  • 596

把excel转化成dataset

//创建Application对象 Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Offic...
  • wsmmdts
  • wsmmdts
  • 2013年10月07日 20:12
  • 326

DataSet和Excel之间的导入

之前在做一个处理Access数据库软件的时候,应用了DataSet
  • ldworld
  • ldworld
  • 2014年11月14日 14:41
  • 529

C# DataTableToExcel的几种实现方式及遇到的问题

个人总结了几种常用DataTableToExcel的实现方式: 1.StreamWriter写入:缺点,数据类型只能为整数型如:007显示为7;优点,适合大量数据,速度快。 2.Microsoft...
  • wdl1071705706
  • wdl1071705706
  • 2015年01月30日 16:20
  • 2464
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:csharp: Export DataSet into Excel and import all the Excel sheets to DataSet
举报原因:
原因补充:

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