iT邦幫忙

0

C# excel 輸入到access

大家好:
Microsoft Excel 97-2003 工作表 (.xls) 寫入access
其中訂單編號,早期是沒有英文字母,只有數字,後來以1個英文字與數字編號,一次將新舊定單編號共5000筆輸入(少量輸入還正常)
訂單編號若以1個英文字與數字編號的就會空白,但只有數字的訂單會留下來,我有將xls設定為通用模式或者文字,都還是會空白,access的columnname也都設為varchar(255)也都不行
想問有甚麼方法可以解決,謝謝
https://ithelp.ithome.com.tw/upload/images/20210303/20097057NDiKncyVH5.png

string fileName = Path.GetFileNameWithoutExtension(this.textBox1.Text);
OleDbConnection Conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + this.textBox1.Text.Trim() + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"); //開啟excel資料表
OleDbCommand Command = new OleDbCommand();
Command.Connection = Conn;
OleDbConnection MydatabaseConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + DBPath); //開啟資料庫
OleDbCommand MydataCommand = new OleDbCommand();
MydataCommand.Connection = MydatabaseConn;
try
{
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + comboBox1.Text.Trim() + "$]", Conn); //選擇excel工作表(comboBox1.Text.Trim())
Conn.Open();
OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCmdSelect);
DataSet dt = new DataSet();
objAdapter.Fill(dt);
string strTemp = "[識別碼] AUTOINCREMENT PRIMARY KEY";
foreach (DataColumn column in dt.Tables[0].Columns)
{                      
string columnname = ",["+ column.ColumnName + "] varchar(255)";
strTemp += columnname;   
}
Conn.Close();
MydataCommand.CommandText = "CREATE TABLE ["+ comboBox1.Text.Trim() +"](" + strTemp + ")";
MydatabaseConn.Open();
MydataCommand.ExecuteNonQuery();
MydataCommand.Connection.Close();
Command.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].["+ comboBox1.Text.Trim() + "] SELECT * FROM [" + comboBox1.Text.Trim() + "$]";
Conn.Open();
Command.ExecuteNonQuery();
Conn.Close();
MessageBox.Show("上傳資料庫成功");
comboBox2.Items.Add(comboBox1.Text.Trim());
                }

                catch (Exception ex)

                {
                    MessageBox.Show(ex.Message);
                }
MydataCommand.CommandText 跟 Command.CommandText的SQL

https://ithelp.ithome.com.tw/upload/images/20210303/20097057MeshUDEFzG.png

看更多先前的討論...收起先前的討論...
rogeryao iT邦大師 1 級 ‧ 2021-03-03 22:27:09 檢舉
將 Command.CommandText 匯出,看看實際的 SQL 是什麼.
DBPath = ?
mayyola iT邦研究生 5 級 ‧ 2021-03-03 22:59:25 檢舉
已將圖片上傳,如果將訂單編號格式分類上傳成兩個表單是沒問題,但合成一個表單欄位有些會變空白
rogeryao iT邦大師 1 級 ‧ 2021-03-03 23:23:56 檢舉
SELECT COUNT([訂單]) FROM [客戶通訊$]
得到的筆數與 EXCEL 內的訂單數一樣嗎?
rogeryao iT邦大師 1 級 ‧ 2021-03-03 23:36:59 檢舉
--

1 個回答

0
rogeryao
iT邦大師 1 級 ‧ 2021-03-03 23:43:43
最佳解答

方法一 :
在 Excel 內變更訂單欄位格式為文字 :
https://ithelp.ithome.com.tw/upload/images/20210306/20085021t98bFivqcu.png
https://ithelp.ithome.com.tw/upload/images/20210306/20085021RS6j1STNeS.png
https://ithelp.ithome.com.tw/upload/images/20210306/20085021MAz6Arwy6L.png
https://ithelp.ithome.com.tw/upload/images/20210306/20085021JM5aeT4mau.png
https://ithelp.ithome.com.tw/upload/images/20210306/20085021SsKYiqEGPp.png

方法二 :
C# Excel :

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApp6
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            //
            string FileStr = "D:\\VBA_SQL_Test\\InsertAccessProject\\A5.xls";
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(FileStr);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["工作表1"];
            // B 欄訂單資料轉換成文字格式
            Excel.Range formatRange;
            for (int z = 1; z <= xlWorkSheet.Rows.Count; z++)
            {
                if (xlWorkSheet.get_Range(xlWorkSheet.Cells[z, 2], xlWorkSheet.Cells[z, 2]).Value2 == null)
                {
                    break;
                }
                else
                {
                    formatRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[z, 2], xlWorkSheet.Cells[z, 2]);
                    formatRange.NumberFormatLocal = "@";
                    xlWorkSheet.get_Range(xlWorkSheet.Cells[z, 2], xlWorkSheet.Cells[z, 2]).Value2 = xlWorkSheet.get_Range(xlWorkSheet.Cells[z, 2], xlWorkSheet.Cells[z, 2]).Value2.ToString();
                }
            }
            //
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

        }
    }
}

將以上程式寫在 Command.CommandText = "INSERT INTO [MS Access;Database=... "; 之前
請參閱 : C# 寫資料到Excel的方式
範例二:依序範例一,用程式碼開啟存在的Excel檔並寫入新的資料...

mayyola iT邦研究生 5 級 ‧ 2021-03-07 21:58:48 檢舉

謝謝r大,會再參考您寫的部分

我的程式碼修正如下,大概是從excel讀資料到datatable再從datatable更新到access,大量資料(5000筆)寫入資料庫還是跑得有點慢,而且又多了好多.dll

IWorkbook workbook = null;
FileStream fs = new FileStream(this.textBox1.Text, FileMode.Open, FileAccess.Read);
if (this.textBox1.Text.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs); //建立活頁簿
}
else if (this.textBox1.Text.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs); //建立活頁簿
}
ISheet sheet = workbook.GetSheetAt(this.comboBox1.SelectedIndex); //產生資料表
DataTable table = new DataTable(comboBox1.Text.Trim());
IRow headerRow = sheet.GetRow(0); //由第一列取標題做為欄位名稱
int cellCount = headerRow.LastCellNum; //第一行有幾格
string strTemp = "[識別碼] AUTOINCREMENT PRIMARY KEY";
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{ //處理標題列
table.Columns.Add(new DataColumn(headerRow.GetCell(i).StringCellValue));
string columnname = ",[" + headerRow.GetCell(i).StringCellValue + "] varchar(255)";
strTemp += columnname;
                    }
 //以欄位文字為名新增欄位,此處全視為字串型別以求簡化
OleDbConnection MydatabaseConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + DBPath);
OleDbCommand MydataCommand = new OleDbCommand();
MydataCommand.Connection = MydatabaseConn;
MydataCommand.CommandText = "CREATE TABLE [" + comboBox1.Text.Trim() + "](" + strTemp + ")";
MydatabaseConn.Open();
MydataCommand.ExecuteNonQuery();
MydataCommand.Connection.Close();
int rowIndex = 0;
foreach (IRow row in sheet)
{
if (rowIndex++ == 0) continue;
DataRow dataRow = table.NewRow();
dataRow.ItemArray = row.Cells.Select(c => c.ToString()).ToArray();
table.Rows.Add(dataRow);
}
MydatabaseConn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
string sQuery = "SELECT * FROM " + table.TableName;
adapter.SelectCommand = new OleDbCommand(sQuery, MydatabaseConn);
adapter.Fill(table);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.Update(table);
MydatabaseConn.Close();
rogeryao iT邦大師 1 級 ‧ 2021-03-07 23:05:44 檢舉

"大概是從excel讀資料到datatable再從datatable更新到access"
相同的資料搬動了2次才到 Access , 在怎麼寫也快不起來.
這是方法的問題 , 不是程式寫法的問題 .

mayyola iT邦研究生 5 級 ‧ 2021-03-07 23:13:48 檢舉

因為之前用問題的方法寫入,寫入access資料會不齊全,但很快..

我要發表回答

立即登入回答