C#导入导出数据到Excel的通用类源码
发布网友
发布时间：2022-12-23 21:10
共1个回答
热心网友
时间：2023-10-03 09:53
public class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;
public int ReturnStatus
{
get{return _ReturnStatus;}
}
public string ReturnMessage
{
get{return _ReturnMessage;}
}
public ExcelIO()
{
}
public DataSet ImportExcel(string fileName)
{
Excel.Application xlApp=new Excel.ApplicationClass();
if(xlApp==null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
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 = "Excel文件处于打开状态，请保存关闭";
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++)
{
da = new OleDbDataAdapter(sql,conn);
da.Fill(ds,SheetSet[i-1]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return ds;
}
public bool ExportExcel(string reportName,DataTable dt,string saveFileName)
{
if(dt==null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空！";
return false;
}
bool fileSaved=false;
Excel.Application xlApp=new Excel.ApplicationClass();
if(xlApp==null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
return false;
}
Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
worksheet.Cells.Font.Size = 10;
Excel.Range range;
long totalCount=dt.Rows.Count;
long rowRead=0;
float percent=0;
worksheet.Cells[1,1]=reportName;
((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12;
((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=(Excel.Range)worksheet.Cells[2,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+3,i+1]=dt.Rows[r][i].ToString();
}
rowRead++;
}
range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,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(saveFileName!="")
{
try
{
workbook.Saved =true;
workbook.SaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exception ex)
{
fileSaved=false;
_ReturnStatus = -1;
_ReturnMessage = "导出文件时出错,文件可能正被打开！n"+ex.Message;
}
}
else
{
fileSaved=false;
}
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;
}
}
