将excel转换为数据表 [英] convert excel to datatable

问题描述

我想将Excel文件中的数据(.xls/.xlsx)转换为sql中的数据表.即使在诸如sheet1，sheet2，sheet3的任何工作表中也有数据.

I want to convert the Excel file data(.xls/.xlsx) to datatable in sql..Even data in any sheet like sheet1,sheet2,sheet3.

推荐答案

引用:单击此处 [

Refer: Click here[^]

Try this:

//Create a workbook
Workbook workbook = new Workbook();           
//Load the file
workbook.LoadFromFile("DataTableSample.xls");

//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];

//Export datatable
DataTable dataTable = sheet.ExportDataTable();

看一下此CP文章:将Excel文件导入到数据集 [ ^ ]

Have a look on this CP Article: Import Excel File to DataSet[^]

在页面加载之前定义msexcel-
的属性

before page load define the properties of msexcel-

public int StateId;
  DataSet ds = new DataSet();
  Thread th2;
  DataSet myDs = new DataSet();
  string fu = null;
  int result;


      Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
      Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
      Microsoft.Office.Interop.Excel.Workbook oWB;
      Microsoft.Office.Interop.Excel.Worksheet oSheet;

然后将您的第一个工作表数据导入数据集-

then take your first sheet data into dataset -

protected void FillDataSet()
 {

     try
     {

         string strFilename = FileUpload.PostedFile.FileName;
         strFilename = System.IO.Path.GetFileName(strFilename);
         string ext = Path.GetExtension(strFilename);
         bool hasHeaders = true;
         string HDR = hasHeaders ? "Yes" : "No";
         string strConn;



         if (ext.ToLower() == ".xls")
         {

             //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + ";Extended Properties=Excel 8.0;";
             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + " ;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
             Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
             oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
             app.Visible = false;

             OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "

"，strConn); myCmd.Fill(myDs); 字符串源= @"F:\ yourdrive \" + strFilename; 字符串目标= @"F:\ yourdrive \ temp \" + strFilename; 如果(File.Exists(target)) File.Delete(目标); File.Move(源，目标); } 如果(ext.ToLower()==".xlsx") { //strConn ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + fu +;扩展属性= Excel 8.0;"; strConn ="Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + fu +;扩展属性= \" Excel 12.0; HDR ="+ HDR +"; IMEX = 2 \"; Microsoft.Office.Interop.Excel.WorkbookClass workBook =(Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu，0，true，5，"，"，true，Microsoft.Office.Interop. Excel.XlPlatform.xlWindows，"\ t"，false，false，0，true，1，0); oSheet =(Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet; app.Visible = false; //您必须使用

", strConn); myCmd.Fill(myDs); string source = @"F:\yourdrive\" + strFilename; string target = @"F:\yourdrive\temp\" + strFilename; if (File.Exists(target)) File.Delete(target); File.Move(source, target); } if (ext.ToLower() == ".xlsx") { //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + ";Extended Properties=Excel 8.0;"; strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fu + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=2\""; Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet; app.Visible = false; //You must use the

这篇关于将excel转换为数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！