大家好:
Microsoft Excel 97-2003 工作表 (.xls) 寫入access
其中訂單編號，早期是沒有英文字母，只有數字，後來以1個英文字與數字編號，一次將新舊定單編號共5000筆輸入(少量輸入還正常)
訂單編號若以1個英文字與數字編號的就會空白，但只有數字的訂單會留下來，我有將xls設定為通用模式或者文字，都還是會空白，access的columnname也都設為varchar(255)也都不行
想問有甚麼方法可以解決，謝謝
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
已邀請的邦友 {{ invite_list.length }}/5
方法一 :
在 Excel 內變更訂單欄位格式為文字 :
方法二 :
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檔並寫入新的資料...
謝謝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();
"大概是從excel讀資料到datatable再從datatable更新到access"
相同的資料搬動了2次才到 Access , 在怎麼寫也快不起來.
這是方法的問題 , 不是程式寫法的問題 .
因為之前用問題的方法寫入，寫入access資料會不齊全，但很快..
iThome鐵人賽
看影片追技術