C# Excel 的导入导出下载

最新推荐文章于 2023-03-27 20:28:11 发布

原创最新推荐文章于 2023-03-27 20:28:11 发布 · 785 阅读

1 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

41 篇文章

订阅专栏

本文详细介绍了如何使用C#编程语言通过ADO.NET连接Excel文件，实现从Excel导入数据到创建数据集，再到将数据集导出为Excel文件的过程。包括错误处理、数据类型转换和文件下载功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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
{
//错误处理
}
}