1.导入:
public DataSet LoadDataFromExcel(string filePath,HttpResponse resp)
{
try
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
System.Data.DataTable dtSheetName = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
String sql = "SELECT * FROM [" + strTableNames[0] + "]";//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, "Sheet1");
OleConn.Close();
return OleDsExcle;
}
catch
{
resp.Write("<script>alert('Failed!')</script>");
return null;
}
}
2.导出:
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (File.Exists(strFileName))
return;
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
// object oMissing = System.Reflection.Missing.Value;
Workbook xlBook = xlApp.Workbooks.Add(true);
Worksheet xlSheet = xlBook.Worksheets[1] as Worksheet;
//将DataTable的列名导入Excel表第一行
int rowIndex = 1;
int columnIndex = 0;
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
if (dc.ColumnName == "时间")
xlSheet.Cells[rowIndex, columnIndex] = "周一";
else
xlSheet.Cells[rowIndex, columnIndex] = dc.ColumnName;
Range rg = (Range)xlSheet.Columns[columnIndex];
rg.NumberFormatLocal = "@";
}
rowIndex = 2;
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
for (int j = 0; j < columnNum; j++)
{
xlSheet.Cells[rowIndex + i, j + 1] = tmpDataTable.Rows[i][j].ToString();
}
}
xlApp.DisplayAlerts = false;
xlApp.AlertBeforeOverwriting = false;
//xlBook.Save();
xlBook.SaveAs(strFileName);
//xlApp.SaveWorkspace(strFilePath + "Summary.xls");
//xlBook.SaveCopyAs(strFilePath + "Summary.xls");
/* xlBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);*/
/* System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);*/
xlBook = null;
//结束进程,释放资源
xlApp.Quit();
GC.Collect();
}
3.下载文件;
public static void DownLoadFile(System.Web.UI.Page page, string fileName)
{
try
{
fileName = HttpContext.Current.Server.MapPath(fileName);
FileInfo DownloadFile = new FileInfo(fileName); //设置要下载的文件
page.Response.Clear(); //清除缓冲区流中的所有内容输出
page.Response.ClearHeaders(); //清除缓冲区流中的所有头
page.Response.Buffer = false; //设置缓冲输出为false
//设置输出流的 HTTP MIME 类型为application/octet-stream
page.Response.ContentType = "application/octet-stream";
//将 HTTP 头添加到输出流
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.Name, System.Text.Encoding.UTF8));
page.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
//将指定的文件直接写入 HTTP 内容输出流
page.Response.WriteFile(DownloadFile.FullName);
page.Response.Flush(); //向客户端发送当前所有缓冲的输出
//将当前所有缓冲的输出发送到客户端
//HttpContext.Current.ApplicationInstance.CompleteRequest();
page.Response.End();
}
catch
{
//错误处理
}
}