C#導(dǎo)入導(dǎo)出EXCEL文件的代碼實(shí)例
更新時(shí)間：2014年04月18日 13:08:08 作者：
這篇文章主要介紹了C#導(dǎo)入導(dǎo)出EXCEL文件代碼實(shí)例,代碼的流程和方法都很詳細(xì),需要的朋友可以參考下
復(fù)制代碼 代碼如下:
using System;
using System.Data;
using System.Data.OleDb;
namespace ZFSoft.Joint
{
public class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;
/// <summary>
/// 執(zhí)行返回狀態(tài)
/// </summary>
public int ReturnStatus
{
get
{
return _ReturnStatus;
}
}
/// <summary>
/// 執(zhí)行返回信息
/// </summary>
public string ReturnMessage
{
get
{
return _ReturnMessage;
}
}
public ExcelIO()
{
}
/// <summary>
/// 導(dǎo)入EXCEL到DataSet
/// </summary>
/// <param name="fileName">Excel全路徑文件名</param>
/// <returns>導(dǎo)入成功的DataSet</returns>
public DataTable ImportExcel(string fileName)
{
//判斷是否安裝EXCEL
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "無法創(chuàng)建Excel對(duì)象，可能您的計(jì)算機(jī)未安裝Excel";
return null;
}
//判斷文件是否被其他進(jìn)程使用
Microsoft.Office.Interop.Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = "Excel文件處于打開狀態(tài)，請(qǐng)保存關(guān)閉";
return null;
}
//獲得所有Sheet名稱
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
}
//釋放Excel相關(guān)對(duì)象
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
//把EXCEL導(dǎo)入到DataSet
DataSet ds = new DataSet();
DataTable table = new DataTable();
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbDataAdapter da;
string sql = "select * from [" + SheetSet[0] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[0]);
da.Dispose();
table = ds.Tables[0];
conn.Close();
conn.Dispose();
}
return table;
}
/// <summary>
/// 把DataTable導(dǎo)出到EXCEL
/// </summary>
/// <param name="reportName">報(bào)表名稱</param>
/// <param name="dt">數(shù)據(jù)源表</param>
/// <param name="saveFileName">Excel全路徑文件名</param>
/// <returns>導(dǎo)出是否成功</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "數(shù)據(jù)集為空！";
return false;
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "無法創(chuàng)建Excel對(duì)象，可能您的計(jì)算機(jī)未安裝Excel";
return false;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
//寫入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//寫入數(shù)值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "導(dǎo)出文件時(shí)出錯(cuò),文件可能正被打開！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//釋放Excel對(duì)應(yīng)的對(duì)象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return fileSaved;
}
}
}
相關(guān)文章
unity shader實(shí)現(xiàn)玻璃折射效果
這篇文章主要為大家詳細(xì)介紹了unity shader實(shí)現(xiàn)玻璃折射效果，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-04-04
這篇文章主要介紹了c# 實(shí)現(xiàn)自動(dòng)掃雷的方法，幫助大家更好的理解和學(xué)習(xí)使用c#，感興趣的朋友可以了解下2021-02-02
C#通過正則表達(dá)式實(shí)現(xiàn)提取網(wǎng)頁中的圖片
本文給大家分享的是使用C#通過正則表達(dá)式來實(shí)現(xiàn)提取網(wǎng)頁中的圖片的代碼，十分的方便，有需要的小伙伴可以參考下。2015-12-12
C#實(shí)現(xiàn)根據(jù)實(shí)體類自動(dòng)創(chuàng)建數(shù)據(jù)庫表
本文主要介紹了C#通過自定義特性實(shí)現(xiàn)根據(jù)實(shí)體類自動(dòng)創(chuàng)建數(shù)據(jù)庫表的方法。具有很好的參考價(jià)值，需要的朋友一起來看下吧2016-12-12
C#中實(shí)現(xiàn)PriorityQueue優(yōu)先級(jí)隊(duì)列的代碼
這篇文章主要介紹了C#中PriorityQueue優(yōu)先級(jí)隊(duì)列的實(shí)現(xiàn),構(gòu)造初始化這部分主要介紹關(guān)鍵的字段和方法，比較器的初始化以及堆的初始化，需要的朋友可以參考下2021-12-12
WPF中使用CallerMemberName簡化InotifyPropertyChanged的實(shí)現(xiàn)
這篇文章介紹了WPF中使用CallerMemberName簡化InotifyPropertyChanged的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-06-06
C#登錄窗口的實(shí)現(xiàn)，特點(diǎn)就是不用隱藏，感興趣的朋友不要錯(cuò)過2013-11-11
C#三種判斷數(shù)據(jù)庫中取出的字段值是否為空(NULL) 的方法
最近操作數(shù)據(jù)庫，需要判斷返回的字段值是否為空，在網(wǎng)上收集了3種方法供大家參考2013-04-04
在多線程中調(diào)用winform窗體控件的實(shí)現(xiàn)方法
這篇文章主要介紹了在多線程中調(diào)用winform窗體控件的實(shí)現(xiàn)方法,需要的朋友可以參考下2014-08-08
這篇文章主要為大家詳細(xì)介紹了C#實(shí)現(xiàn)簡單訂單管理程序，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-05-05