C#实现Excel的导入与导出《转》
转：http://blog.csdn.net/xuenzhen123/article/details/4713019ss
前两天研究如何使用C#实现Excel数据的导入与导出，在网上查找大量资料，最后经过整理，写了一个通用的类，与大家分享一下，共同学习。代码如下：
public class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;
private const string CREATE_ERROR = "Can not create excel file, may be your computer has not installed excel!";
private const string IMPORT_ERROR = "Your excel file is open, please save and close it.";
private const string EXPORT_ERROR = "This is an error that the excel file may be open when it be exported. /n";
public int ReturnStatus
{
get { return _ReturnStatus; }
}
public string ReturnMessage
{
get { return _ReturnMessage; }
}
public ExcelIO()
{
}
/// <summary>
/// Excel文件的导出
/// 其中每个工作表为一个DataTable
/// </summary>
/// <param name="fileName">Excel文件的完整路径</param>
/// <returns></returns>
public DataSet ImportExcel(string fileName)
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = CREATE_ERROR;
return null;
}
Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = IMPORT_ERROR;
return null;
}
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] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
}
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();
DataSet ds = new DataSet();
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;
for (int i = 1; i <= n; i++)
{
string sql = "select * from [" + SheetSet[i - 1] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[i - 1]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return ds;
}
/// <summary>
/// Excel导入
/// 每个Datatable为一个工作表，工作表的名字为Datatable的名字
/// </summary>
/// <param name="ds">数据源</param>
/// <param name="saveFileName">要保存的Excel文件的完整路径</param>
/// <returns></returns>
public bool ExportExcel(DataSet ds, string saveFileName)
{
if (ds == null)
{
_ReturnStatus = -1;
_ReturnMessage = "The DataSet is null!";
return false;
}
bool fileSaved = false;
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = CREATE_ERROR;
return false;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
for (int j = ds.Tables.Count - 1; j >= 0; j--)
{
DataTable dt = ds.Tables[j];
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheet.Name = ds.Tables[j].TableName.ToString();
worksheet.Cells.Font.Size = 10;
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, 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 + 1, dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
}
((Excel.Worksheet)workbook.Worksheets[ds.Tables.Count + 1]).Delete();
if (saveFileName != "")
{
try
{
workbook.Saved = true;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel8, miss, miss, miss, miss,
Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = EXPORT_ERROR + ex.Message;
}
}
else
{
fileSaved = false;
}
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;
}
}
当然，你需要添加引用：
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
各位网友对以上算法有什么建议请指出，谢谢。
