Excel导入导出

Excel导入导出
public static string GetExcelFirstTableName(string excelFileName)
{
string tableName = null;
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
"OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
tableName = dt.Rows[0][2].ToString().Trim();
}
}
return tableName;
}
private DataTable GetDataTableName(string path)
{
DataTable dt = new DataTable();
if (File.Exists(path))
{
string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + path; //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
conn.Close();
}
}
return dt;
}
private DataTable GetDataTable(string path, string name)
{
DataTable dt = new DataTable();
string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + path;
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + name + "]", conn);
da.Fill(dt);
}
return dt;
}
private void saveExcel(DataTable dt, string path)
{
if (dt == null)
return;
int columnsCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
int columsIndex = 0;
int rowIndex = 1;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xBook = app.Workbooks.Add();
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
foreach (DataColumn dc in dt.Columns)
{
columsIndex++;
app.Cells[rowIndex,columsIndex] = dc.ColumnName;
}
for (int i = 0; i < rowCount; i++)
{
rowIndex++;
columsIndex = 0;
for (int j = 0; j < columnsCount; j++)
{
columsIndex++;
app.Cells[rowIndex, columsIndex] = dt.Rows[i][j].ToString();
}
}
xBook.SaveCopyAs(path);
}。