using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
namespace COMMON
{
public class Excel_OutputInput
{
private int _ReturnStatus;
private string _ReturnMessage;
/// <summary>
/// 执行返回状态
/// </summary>
public int ReturnStatus
{
get
{
return _ReturnStatus;
}
}
/// <summary>
/// 执行返回信息
/// </summary>
public string ReturnMessage
{
get
{
return _ReturnMessage;
}
}
public Excel_OutputInput()
{
}
/// <summary>
/// 导入EXCEL到DataSet
/// </summary>
/// <param name="fileName">Excel全路径文件名</param>
/// <returns>导入成功的DataSet</returns>
public DataTable ImportExcel(string fileName)
{
//判断是否安装EXCEL
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
return null;
}
//判断文件是否被其他进程使用
Microsoft.Office.Interop.Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, , false, , "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, , true, , );
}
catch
{
_ReturnStatus = -;
_ReturnMessage = "Excel文件处于打开状态，请保存关闭";
return null;
}
//获得所有Sheet名称
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = ; i <= n; i++)
{
SheetSet[i - ] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
}
//释放Excel相关对象
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导入到DataSet
DataSet ds = new DataSet();
DataTable table = new DataTable();
string conStr;
FileInfo file = new FileInfo(fileName);
string extention = file.Extension;
switch (extention)
{
case ".xls":
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
break;
case ".xlsx":
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0";
break;
default:
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
break;
}
//string connStr = " Provider = Microsoft.Jet.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=Excel 12.0";
using (OleDbConnection conn = new OleDbConnection(conStr))
{
conn.Open();
OleDbDataAdapter da;
string sql = "select * from [" + SheetSet[] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[]);
da.Dispose();
table = ds.Tables[];
conn.Close();
conn.Dispose();
}
return table;
}
/// <summary>
/// 把DataTable导出到EXCEL
/// </summary>
/// <param name="reportName">报表名称</param>
/// <param name="dt">数据源表</param>
/// <param name="saveFileName">Excel全路径文件名</param>
/// <returns>导出是否成功</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -;
_ReturnMessage = "数据集为空！";
return false;
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装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[];//取得sheet1
worksheet.Cells.Font.Size = ;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = ;
float percent = ;
worksheet.Cells[, ] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, ]).Font.Size = ;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, ]).Font.Bold = true;
//写入字段
for (int i = ; i < dt.Columns.Count; i++)
{
worksheet.Cells[, i + ] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, i + ];
range.Interior.ColorIndex = ;
range.Font.Bold = true;
}
//写入数值
for (int r = ; r < dt.Rows.Count; r++)
{
for (int i = ; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + , i + ] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)( * rowRead)) / totalCount;
}
range = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[dt.Rows.Count + , 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 > )
{
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 > )
{
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 = -;
_ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
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;
}
}
}