//从Excel读取数据
staticpublicDataTable ImputExcel(string TableName,string FileName)
{
String sConnectionString="provider=Microsoft.Jet.OLEDB.4.0; " +"data source=";
sConnectionString += FileName + ";" +
"Extended Properties=Excel 8.0;";
string sql=string.Empty;
sql = "SELECT * FROM ["+TableName+"$]";
OleDbConnection conn;
try
{
conn = newOleDbConnection(sConnectionString);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "连接字符串错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
returnnull;
}
OleDbCommand cmd = newOleDbCommand(sql,conn);
OleDbDataAdapter dataAdapter = newOleDbDataAdapter();
dataAdapter.SelectCommand = cmd;
DataTable dataTable=newDataTable();
try
{
dataAdapter.Fill(dataTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "读取Excel错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
returnnull;
}
return dataTable;
}
//把数据导出到Excel中
staticpublicvoid OutputExcel(DataTable OutputDataTable, string saveFileName)
staticpublicvoid OutputExcel(DataTable OutputDataTable, string saveFileName)
{
bool fileSaved = false;
string mySql = string.Empty;
string helpSql = string.Empty;
string FileName = OutputDataTable.TableName;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
workbooks.OpenXML(saveFileName, Excel.XlSheetType.xlWorksheet, LoadOption.Upsert);
worksheet.Name = FileName;
//写入字段
for (int i = 0; i < OutputDataTable.Columns.Count; i++)
{
string columnName = OutputDataTable.Columns[i].ColumnName;
worksheet.Cells[1, i + 1] = columnName;
mySql += columnName + ",";
helpSql += "?,";
}
mySql = mySql.Substring(0, mySql.Length - 1);
helpSql = helpSql.Substring(0, helpSql.Length - 1);
string sqlInsert = "INSERT INTO [" + FileName + "$]" + "(" + mySql + ")" + " values " + "(" +helpSql+")";
//INSERT INTO [Sheet1$] (F1, F2) values (?, ?)
//保存Excel
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);
}
xlApp.Quit();//关闭EXCEL
GC.Collect();
//读取Excel架构
if (fileSaved == true)
{
String sConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; " + "data source=";
sConnectionString += saveFileName + ";" +
"Extended Properties=Excel 8.0;";
string sql = string.Empty;
sql = "SELECT * FROM [" + FileName + "$]";
OleDbConnection conn;
try
{
conn = newOleDbConnection(sConnectionString);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "连接字符串错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
OleDbCommand cmd = newOleDbCommand(sql, conn);
OleDbDataAdapter dataAdapter = newOleDbDataAdapter();
dataAdapter.SelectCommand = cmd;
OleDbCommand cmd2 = newOleDbCommand(sqlInsert, conn);
dataAdapter.InsertCommand = cmd2;
DataTable dataTable = newDataTable();
try
{
dataAdapter.Fill(dataTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "读取Excel错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
conn.Open();
foreach (DataRow dr in OutputDataTable.Rows)
{
foreach (DataColumn dc in dataTable.Columns)
{
cmd2.Parameters.AddWithValue(dc.ColumnName, dr[dc.ColumnName]);
}
//cmd2.ExecuteNonQuery();
}
conn.Close();
}
}