EXCEL的导入导出
2024-08-06 20:05:59
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;
}
}
}
最新文章
- markdown语法与使用
- Excel文件相关：XLS格式文件基本操作
- Extjs3 + swfUpload实现多文件上传控件
- Sublime text追踪函数插件：ctags
- iOS之通过PaintCode快速实现交互动画的最方便方法 未解问题
- AOP这些应用场景(交叉业务)
- python 练习 26
- 关于FragmentManager findFragmentById 返回nul
- UVA 10004 Bicoloring
- Laravel 5 基础（二）- 路由、控制器和视图简介
- 安装IIS之后运行aspx 显示“服务器应用程序不可用” 解决办法
- Net中的AOP
- UVALive-4287 Proving Equivalences 有向图的强连通分量+缩点
- android小知识之圆角ListView
- D. Bear and Two Paths(贪心构造)
- 熟悉JS中的常用选择器及属性、方法的调用
- locate 命令详解
- 原生js封装插件
- iostat iotop 查看硬盘的读写、 free 查看内存的命令 、netstat 命令查看网络、tcpdump 命令
- 1. apache如何启动