public
DataSet LoadDataFromExcel(
string
filePath)
{
try
{
string
strConn;
strConn =
string
.Format(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"
, filePath);
OleDbConnection OleConn =
new
OleDbConnection(strConn);
OleConn.Open();
String sql =
"SELECT * FROM [Sheet1$]"
;
OleDbDataAdapter OleDaExcel =
new
OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle =
new
DataSet();
OleDaExcel.Fill(OleDsExcle,
"Sheet1"
);
OleConn.Close();
return
OleDsExcle;
}
catch
(Exception err)
{
return
null
;
}
}
/// <summary>
/// </summary>
/// <param name="dt">要导出Excel的DataTable</param>
/// <returns></returns>
public
bool
DoExport(System.Data.DataTable dt)
{
Microsoft.Office.Interop.Excel.Application app =
new
ApplicationClass();
if
(app ==
null
)
{
throw
new
Exception(
"Excel无法启动"
);
}
app.Visible =
true
;
Workbooks wbs = app.Workbooks;
Workbook wb = wbs.Add(Missing.Value);
Worksheet ws = (Worksheet)wb.Worksheets[1];
int
cnt = dt.Rows.Count;
int
columncnt = dt.Columns.Count;
object
[,] objData =
new
Object[cnt + 1, columncnt];
for
(
int
i = 0; i < columncnt; i++)
{
objData[0, i] = dt.Columns[i].ColumnName;
}
for
(
int
i = 0; i < cnt; i++)
{
System.Data.DataRow dr = dt.Rows[i];
for
(
int
j = 0; j < columncnt; j++)
{
objData[i + 1, j] = dr[j];
}
}
Range r = ws.get_Range(app.Cells[1, 1], app.Cells[cnt + 1, columncnt]);
r.NumberFormat =
"@"
;
r.Value2 = objData;
r.EntireColumn.AutoFit();
app =
null
;
return
true
;
}