有的客户装了不同的excel程序,所以做了四种excel文件的读写,在一定程度上可以适应各种情况的客户。
npoi :对公式,某些日期兼容的不好,速度很快,不需要装excel程序
ado::对某些日期格式兼容的不好,速度较快
office:支持office2013
wps:支持wps2016
测试:在wps与excel都安装的情况下,调用 wps sdk时,有些打开确是excel进程
源代码下载地址:http://download.csdn.net/detail/liangzhonglin/9546566
wps
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Excel;
namespace ExcelLib
{
public class CExcel
{
public string Error = "";
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 强制关闭当前Excel进程
/// </summary>
public static void Kill(IntPtr intPtr)
{
try
{
Process[] ps = Process.GetProcesses();
int ExcelID = 0;
GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k
foreach (Process p in ps)
{
string procName = p.ProcessName.ToLower();
if (procName.Equals("excel")|| procName.Equals("et"))
{
if (p.Id == ExcelID)
{
p.Kill();
}
}
}
}
catch
{
//不做任何处理
}
}
public bool DatatableToExcel(ref System.Data.DataTable dtExcel, string excelPath)
{
Error = "不支持此接口!";
return false;
}
#region 读取excel文件
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out System.Data.DataTable dtExcel)
{
Application objApp = null;
Workbook objWorkBook = null;
Worksheet objWorkSheet = null;
IntPtr wpsPtr = IntPtr.Zero;
dtExcel = null;
try
{
//获取工作表表格
object type = System.Reflection.Missing.Value;
objApp = new Application();
objApp.Visible = false;
objApp.ScreenUpdating = false;
objApp.DisplayAlerts = false;
objWorkBook = (Workbook)objApp.Workbooks.Open(excelPath, type, type, type, type, type, type, type, type, type, type, type, type, type, type);
if (objApp.Workbooks.Count == 0)
{
throw new Exception("excel文件中没有sheet表");
}
wpsPtr = new IntPtr(objApp.Hwnd);
//获得指定表
if (sheetName.Length == 0)
{
objWorkSheet = (Worksheet)objWorkBook.Worksheets.get_Item(1);
}
else
{
objWorkSheet = (Worksheet)objWorkBook.Worksheets.get_Item(sheetName);
}
//取表名赋值到dt TableName
dtExcel = new System.Data.DataTable(tableName);
//获取数据区域
int row = objWorkSheet.UsedRange.Rows.Count;
int col = objWorkSheet.UsedRange.Columns.Count;
int recordIndex = 1;
if (fieldNameList.Length > 0)
{
string[] fieldList = fieldNameList.Split(',');
for (int i = 0; i < fieldList.Length; i++)
{
dtExcel.Columns.Add(fieldList[i]);
}
}
else
{
for (int c = 1; c <= col; c++)
{
dtExcel.Columns.Add(new DataColumn((String)((Excel.Range)objWorkSheet.Cells[1, c]).Text));
}
recordIndex = 2;
}
for (int r = recordIndex; r <= row; r++)
{
DataRow newRow = dtExcel.NewRow();
for (int c = 1; c <= col; c++)
{
newRow[c - 1] = ((Excel.Range)objWorkSheet.Cells[r, c]).Text;
}
dtExcel.Rows.Add(newRow);
}
return true;
}
catch (Exception ex)
{
Error = "读取excel失败:" + ex.Message;
return false;
}
finally
{
if (objWorkBook != null)
{
objWorkBook.Close(true, null, null);
objWorkBook = null;
}
if (objApp != null)
{
objApp.Quit();
objApp = null;
}
// 杀死WPS线程
try
{
if (wpsPtr != IntPtr.Zero)
{
Kill(wpsPtr);
}
}
catch (Exception)
{ }
System.GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out System.Data.DataTable dtExcel)
{
dtExcel = null;
Error = "不支持此接口!";
return false;
}
#endregion
}
}
office
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Runtime.InteropServices;
using System.Diagnostics;
namespace ExcelLib
{
public class CExcel
{
public string Error = "";
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 强制关闭当前Excel进程
/// </summary>
public static void Kill(IntPtr intPtr)
{
try
{
Process[] ps = Process.GetProcesses();
int ExcelID = 0;
GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k
foreach (Process p in ps)
{
string procName = p.ProcessName.ToLower();
if (procName.Equals("excel") || procName.Equals("et"))
{
if (p.Id == ExcelID)
{
p.Kill();
}
}
}
}
catch
{
//不做任何处理
}
}
public bool DatatableToExcel(ref DataTable dtExcel, string excelPath)
{
Error = "不支持此接口!";
return false;
}
#region 读取excel文件
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel)
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = null;
dtExcel = null;
IntPtr excelPtr = IntPtr.Zero;
try
{
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelPtr = new IntPtr(excel.Hwnd);
excel.Visible = false;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
excel.Workbooks.Add(excelPath);
if (excel.Workbooks.Count == 0)
{
throw new Exception("excel文件中没有sheet表");
}
//获得指定表
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
//取表名赋值到dt TableName
dtExcel = new System.Data.DataTable(worksheet.Name);
worksheet.Columns.EntireColumn.AutoFit();
int row = worksheet.UsedRange.Rows.Count;
int col = worksheet.UsedRange.Columns.Count;
for (int c = 1; c <= col; c++)
{
dtExcel.Columns.Add(new DataColumn((String)((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c]).Text));
}
for (int r = 2; r <= row; r++)
{
DataRow newRow = dtExcel.NewRow();
for (int c = 1; c <= col; c++)
{
newRow[c - 1] = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, c]).Text;
}
dtExcel.Rows.Add(newRow);
}
worksheet = null;
return true;
}
catch (Exception ex)
{
Error = "读取excel失败:" + ex.Message;
return false;
}
finally
{
if (excel != null)
{
excel.Quit();
excel = null;
}
// 杀死excel线程
try
{
if ( excelPtr!= IntPtr.Zero)
{
Kill(excelPtr);
}
}
catch (Exception)
{ }
System.GC.Collect();
}
}
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out DataTable dtExcel)
{
dtExcel = null;
Error = "不支持此接口!";
return false;
}
#endregion
}
}
ado
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelLib
{
public class CExcel
{
public string Error = "";
public bool DatatableToExcel(ref DataTable dtExcel, string excelPath)
{
Error = "不支持此接口!";
return false;
}
#region 读取excel文件
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel)
{
dtExcel = null;
OleDbConnection conn = null;
try
{
string defaultSheetName = sheetName; // "[Sheet1$]";
string strConn = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'", excelPath);
// string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", excelPath);
if (sheetName.Length == 0)
{
// 取默认的sheet表名
conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
conn = null;
defaultSheetName = sheetNames.Rows[0]["TABLE_NAME"].ToString();
}
DataSet ds = new DataSet();
string sqlTabel = string.Format("select * from [{0}]", defaultSheetName);
OleDbDataAdapter oada = new OleDbDataAdapter(sqlTabel, strConn);
oada.Fill(ds);
dtExcel = ds.Tables[0];
dtExcel.TableName = tableName;
if (fieldNameList.Length > 0)
{
string[] fieldList = fieldNameList.Split(',');
for (int i = 0; i < dtExcel.Columns.Count; i++)
{
dtExcel.Columns[i].ColumnName = fieldList[i];
}
}
return true;
}
catch (Exception ex)
{
Error = "读取excel失败:" + ex.Message;
return false;
}
finally
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out DataTable dtExcel)
{
dtExcel = null;
Error = "不支持此接口!";
return false;
}
#endregion
}
}
npoi
using System;
using System.Collections.Generic;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.IO;
using System.Data;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace ExcelLib
{
public class CExcel
{
public string Error = "";
#region 生成excel
private CellType GetCellType(string type)
{
CellType cell = CellType.String;
if (type == "System.Double")
{
cell = CellType.Numeric;
}
return cell;
}
public bool DatatableToExcel(ref DataTable dtExcel, string excelPath)
{
try
{
if (excelPath == null)
{
throw new Exception("未指定生成路径");
}
bool bResult = true;
string ext = Path.GetExtension(excelPath);
switch (ext.ToLower())
{
case ".xls":
bResult= DatatableToExcel2003(ref dtExcel, excelPath);
break;
case ".xlsx":
bResult= DatatableToExcel2007(ref dtExcel, excelPath);
break;
default:
throw new Exception("保存文件名不正确");
}
return bResult;
}
catch (System.Exception ex)
{
Error = "生成excel失败" + ex.Message;
return false;
}
}
private bool DatatableToExcel2007(ref DataTable dtExcel, string excelPath)
{
try
{
XSSFWorkbook workbook = new XSSFWorkbook();
create a entry of DocumentSummaryInformation
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "Partner";
//hssfworkbook..DocumentSummaryInformation = dsi;
create a entry of SummaryInformation
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Subject = "FrontLink";
//hssfworkbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
ISheet sheet = workbook.CreateSheet(dtExcel.TableName);
int cellCount = dtExcel.Columns.Count;
IRow column = sheet.CreateRow(0);
for (int j = 0; j < cellCount; j++)
{
column.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString()));
column.CreateCell(j).SetCellValue(dtExcel.Columns[j].ColumnName);
}
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < cellCount; j++)
{
row.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString()));
row.CreateCell(j).SetCellValue(dtExcel.Rows[i][j].ToString());
}
}
for (int i = 0; i < dtExcel.Columns.Count; i++)
{
// sheet.AutoSizeColumn(i, false);// 此方法导致内存占用100%,导出速度慢
sheet.SetColumnWidth(i, 9000);
}
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(excelPath, FileMode.Create);
workbook.Write(file);
file.Close();
return true;
}
catch (System.Exception ex)
{
Error = "生成Excel失败:" + ex.Message;
return false;
}
}
private bool DatatableToExcel2003(ref DataTable dtExcel, string excelPath)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Partner";
workbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "FrontLink";
workbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
ISheet sheet = workbook.CreateSheet(dtExcel.TableName);
int cellCount = dtExcel.Columns.Count;
IRow column = sheet.CreateRow(0);
for (int j = 0; j < cellCount; j++)
{
column.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString()));
column.CreateCell(j).SetCellValue(dtExcel.Columns[j].ColumnName);
}
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < cellCount; j++)
{
row.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString()));
row.CreateCell(j).SetCellValue(dtExcel.Rows[i][j].ToString());
}
}
for (int i = 0; i < dtExcel.Columns.Count; i++)
{
//sheet.AutoSizeColumn(i, false); // 此方法导致内存占用100%,导出速度慢
sheet.SetColumnWidth(i, 9000);
}
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(excelPath, FileMode.Create);
workbook.Write(file);
file.Close();
return true;
}
catch (System.Exception ex)
{
Error = "生成Excel失败:" + ex.Message;
return false;
}
}
#endregion
#region 读取excel文件
/// <summary>
/// 读取单元格的数据,并转化成字符串值
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private string GetCellValue(ICell cell)
{
string value = "";
if (cell != null)
{
string format = cell.CellStyle.GetDataFormatString();
switch (cell.CellType)
{
case CellType.String:
value = cell.StringCellValue;
break;
case CellType.Numeric:
if (format != null && (format.IndexOf("m") > 0 || format.IndexOf("d") > 0))
{
if (format == "m/d/yy")
{
DateTime dt=cell.DateCellValue;
value = dt.ToString("yyyy-MM-dd");
}
else
{
//DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
value = cell.ToString();
}
}
else
{
value = cell.NumericCellValue.ToString();
}
break;
case CellType.Boolean:
value = cell.BooleanCellValue.ToString();
break;
case CellType.Formula:
value = cell.CellFormula;
break;
case CellType.Blank:
value = "";
break;
default:
break;
}
}
return value;
}
/// <summary>
/// 读取Excel文件内容到表里
/// </summary>
/// <param name="excelPath"></param>
/// <param name="sheetName"></param>
/// <param name="tableName"></param>
/// <param name="fieldNameList">fieldNameList有值,为指定的字段,为“”;取excel的第一行做为列值;为null,取excel的列名</param>
/// <param name="getMaxRecord">取值范围:-1取全部的值;>0取指定的值</param>
/// <param name="dtExcel"></param>
/// <returns></returns>
private bool LocalReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, int getMaxRecord, out DataTable dtExcel)
{
dtExcel = new DataTable(tableName);
FileStream file = null;
try
{
sheetName = sheetName.Trim();
file = new FileStream(excelPath, FileMode.Open);
IWorkbook workbook = WorkbookFactory.Create(file);
//HSSFWorkbook workbook = new HSSFWorkbook(file);//创建工作簿对象
NPOI.SS.UserModel.ISheet sheet = null;
if (sheetName.Length == 0)
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
int sheetHaveHeader = 0;
int cellCount = 0;
//创建表标题
if (fieldNameList == null)
{
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
//读取并生成标题行,这里能成功执行
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(i.ToString());
dtExcel.Columns.Add(column);
}
}
else if (fieldNameList.Trim().Length > 0)
{
string[] fieldList = fieldNameList.Split(',');
foreach (string field in fieldList)
{
dtExcel.Columns.Add(new DataColumn(field.Trim()));
}
cellCount = fieldList.Length;
sheetHaveHeader = 1;
}
else
{
sheetHaveHeader = 1;
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
//读取并生成标题行,这里能成功执行
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(GetCellValue(headerRow.GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL)));
dtExcel.Columns.Add(column);
}
}
bool bFilter = getMaxRecord >= 0;
//逐个读取单元格,这里就不能正确读取到
for (int i = (sheet.FirstRowNum + sheetHaveHeader); i <= sheet.LastRowNum; i++)
{
if (bFilter)
{
// 只取指定的记录数
if (dtExcel.Rows.Count >= getMaxRecord)
{
break;
}
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
DataRow dataRow = dtExcel.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = GetCellValue(row.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL)).Replace("'", "’");
}
dtExcel.Rows.Add(dataRow);
}
//for (int i = 0; i < dtExcel.Columns.Count; i++)
//{
// // sheet.AutoSizeColumn(i, false); // 此方法导致内存占用100%,导出速度慢
// sheet.SetColumnWidth(i, 9000);
//}
workbook = null;
sheet = null;
return true;
}
catch (System.Exception ex)
{
Error = "读取Excel失败:" + ex.Message;
return false;
}
finally
{
if (file != null)
{
file.Close();
}
}
}
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel)
{
return LocalReadExcelToDataTable(excelPath, sheetName, tableName, fieldNameList, -1, out dtExcel);
}
public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out DataTable dtExcel)
{
return LocalReadExcelToDataTable(excelPath, sheetName, tableName, null, getMaxRecord, out dtExcel);
}
#endregion
}
}