利用Aspose.Cells.dll 操作Excel，内容如下：
1、界面设计：
2、逻辑：
using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.IO; namespace ReadyExcel
{
public partial class frmMatchingExcel : Form
{
public frmMatchingExcel()
{
InitializeComponent();
} Stream cardStream;
Stream priceStream;
string importExcelPath = @"D:\importExcel";
string importErrorExcelPath = @"D:\importExcelError"; //读取到流量卡数据
private void button1_Click(object sender, EventArgs e)
{
richTextBox1.Text = "[" + DateTime.Now.ToString() + "] " + "正在读取中...";
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "excel文件|*.xls;*.xlsx;*.*";
if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
{
cardStream = openfile.OpenFile();
} //读取成功，但没读到数据
if (cardStream == null)
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "没有读取到流量卡数据";
}
else
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "读取完毕";
}
} //流量卡价格数据
private void button2_Click(object sender, EventArgs e)
{
if (cardStream != null)
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "正在读取中...";
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "excel文件|*.xls;*.xlsx;*.*";
if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
{
priceStream = openfile.OpenFile();
} if (priceStream == null)
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "没有读取到流量卡价格数据";
}
else
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "读取完毕";
}
}
else
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "请先选择‘流量卡数据’表";
}
} //匹配流量卡价格
private void button3_Click(object sender, EventArgs e)
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "正在匹配流量卡价格...";
//读取成功
if (cardStream != null && priceStream != null)
{
string isOk = string.Empty;
List<CarEntity> importExcelData = ImportExcelToDataTable.AnalysisExcel(cardStream, priceStream, importExcelPath, importErrorExcelPath, out isOk);
if (string.IsNullOrEmpty(isOk))
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "成功匹配完流量卡价格";
else
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "匹配流量卡价格操作失败:" + isOk; //初始化文件流信息
cardStream = null;
priceStream = null;
}
else
{
richTextBox1.Text += "\r\n\r\n[" + DateTime.Now.ToString() + "] " + "请先选择‘流量卡’和‘流量卡价格’数据表";
}
} private void button4_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
3、公用类：
====ImportExcelToDataTable====
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Aspose.Cells;
using System.Collections.Concurrent; namespace ReadyExcel
{
public class ImportExcelToDataTable
{
/// <summary>
/// 解析文件流到Excel
/// </summary>
/// <param name="cardStream"></param>
/// <param name="priceStream"></param>
/// <param name="fileparh"></param>
/// <param name="importErrorExcelPath"></param>
/// <param name="rmg"></param>
/// <returns></returns>
public static List<CarEntity> AnalysisExcel(Stream cardStream, Stream priceStream, string fileparh, string importErrorExcelPath, out string rmg)
{
string isok = string.Empty;
List<CarEntity> importExcelData = new List<CarEntity>();
List<CarEntity> errorData = new List<CarEntity>();
try
{
//获取流量卡信息
Workbook book_Card = new Workbook(cardStream);
Worksheet sheet_Card = book_Card.Worksheets[0];
DataTable dtCard = sheet_Card.Cells.ExportDataTableAsString(0, 0, sheet_Card.Cells.MaxDataRow + 1, sheet_Card.Cells.MaxDataColumn + 1, true);
List<CarEntity> _listCard = ConvertHelper.ToList<CarEntity>(dtCard).ToList();
ConcurrentQueue<CarEntity> queueCard = new ConcurrentQueue<CarEntity>();
Parallel.ForEach(_listCard, item => { queueCard.Enqueue(item); }); //获取流量卡价格信息
Workbook book_Price = new Workbook(priceStream);
Worksheet sheet_Price = book_Price.Worksheets[0];
DataTable dtPrice = sheet_Price.Cells.ExportDataTableAsString(0, 0, sheet_Price.Cells.MaxDataRow + 1, sheet_Price.Cells.MaxDataColumn + 1, true);
List<CarPrice> _listPrice = ConvertHelper.ToList<CarPrice>(dtPrice).ToList();
ConcurrentQueue<CarPrice> queuePrice = new ConcurrentQueue<CarPrice>();
Parallel.ForEach(_listPrice, item => { queuePrice.Enqueue(item); }); //设置流量卡渠道价
foreach (var item in _listCard)
{
CarPrice _price = _listPrice.Where(i => i.CarNo.IndexOf(item.SIMNo) > 0
|| (i.CarNo.Split('-').Length == 2 ? (Convert.ToInt64(i.CarNo.Split('-')[0]) <= Convert.ToInt64(item.SIMNo) && Convert.ToInt64(item.SIMNo) <= Convert.ToInt64(i.CarNo.Split('-')[1])) : false)).FirstOrDefault(); //记录价格匹配成功数据
if (_price != null)
{
item.ChannelPrice = _price.price;
importExcelData.Add(item);
}
else//记录价格匹配失败数据
{
errorData.Add(item);
}
} //导出“匹配成功”数据
DataTable dtImport = ToDataTable(importExcelData);
bool isSuccess = DataTableToExcel(fileparh, dtImport, false);
if (!isSuccess)
{
isok = "导出有效数据失败";
}
else
{ } //导出“匹配失败”数据
DataTable dtError = ToDataTable(errorData);
bool isOk = DataTableToExcel(importErrorExcelPath, dtError, false);
if (!isOk)
{
isok += "导出无效数据失败";
}
}
catch (Exception ex)
{
isok = ex.Message;
}
rmg = isok;
return importExcelData;
} /// <summary>
/// 创建表
/// </summary>
/// <returns></returns>
public static DataTable createDataTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("SIMNo", typeof(string));
dt.Columns.Add("ICCID", typeof(string));
dt.Columns.Add("IMSI", typeof(string));
dt.Columns.Add("IMEI", typeof(string));
dt.Columns.Add("Saledate", typeof(string));
dt.Columns.Add("Status", typeof(string));
dt.Columns.Add("CardStatus", typeof(string));
dt.Columns.Add("ActiveStatus", typeof(string));
dt.Columns.Add("MonthFlow", typeof(string));
dt.Columns.Add("OperatorsType", typeof(string));
dt.Columns.Add("AvailableFlow", typeof(string));
dt.Columns.Add("MainPackage", typeof(string));
dt.Columns.Add("ServiceStartTime", typeof(string));
dt.Columns.Add("ServiceEndTime", typeof(string));
dt.Columns.Add("AuthState", typeof(string));
dt.Columns.Add("RenewDate", typeof(string));
dt.Columns.Add("Suspend", typeof(string));
dt.Columns.Add("UsedFlow", typeof(string));
dt.Columns.Add("UsageRateFlow", typeof(string));
dt.Columns.Add("ChannelPrice", typeof(string)); return dt;
} #region Convert a List{T} to a DataTable.
/// <summary>
/// Convert a List{T} to a DataTable.
/// </summary>
public static DataTable ToDataTable<T>(List<T> items)
{
var tb = new DataTable(typeof(T).Name);
System.Reflection.PropertyInfo[] props = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
foreach (System.Reflection.PropertyInfo prop in props)
{
Type t = GetCoreType(prop.PropertyType);
tb.Columns.Add(prop.Name, t);
}
foreach (T item in items)
{
var values = new object[props.Length]; for (int i = 0; i < props.Length; i++)
{
values[i] = props[i].GetValue(item, null);
} tb.Rows.Add(values);
} return tb;
} /// <summary>
/// Determine of specified type is nullable
/// </summary>
public static bool IsNullable(Type t)
{
return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
} /// <summary>
/// Return underlying type if type is Nullable otherwise return the type
/// </summary>
public static Type GetCoreType(Type t)
{
if (t != null && IsNullable(t))
{
if (!t.IsValueType)
{
return t;
}
else
{
return Nullable.GetUnderlyingType(t);
}
}
else
{
return t;
}
}
#endregion /// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="filePath">保存路径</param>
/// <param name="dataTable">数据集</param>
/// <param name="isShowExcle">导出后是否打开文件</param>
/// <returns></returns>
public static bool DataTableToExcel(string filePath, DataTable dataTable, bool isShowExcle)
{
int rowNumber = dataTable.Rows.Count;
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;
if (rowNumber == 0)
{
return false;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
Microsoft.Office.Interop.Excel.Range range; foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
}
range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
range.Value2 = objData;
range.NumberFormatLocal = "@";
worksheet.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return true;
}
}
}
====ConvertHelper====
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Reflection; namespace ReadyExcel
{
public class ConvertHelper
{
/// <summary>
/// 转换 DataTable 对象为 IList 对象
/// </summary>
/// <param name="datas">数据集合</param>
/// <returns>数组对象</returns>
public static T[] ToArray<T>(DataTable datas) where T : class, new()
{
List<T> list = ToList<T>(datas) as List<T>;
return list.ToArray();
} /// <summary>
/// 转换IList对象为DataTable对象
/// </summary>
/// <param name="datas">数据集合</param>
/// <returns>DataTable对象</returns>
public static DataTable ToDataTable<T>(IList<T> datas)
{
return ToDataTable<T>(datas, null);
} /// <summary>
/// 转换IList对象为DataTable对象
/// </summary>
/// <param name="datas">数据集合</param>
/// <returns>DataTable对象</returns>
public static DataTable ToDataTable<T>(T[] datas)
{
return ToDataTable<T>(datas, null);
} /// <summary>
/// 转换IList对象为DataTable对象
/// </summary>
/// <param name="datas">数据集合</param>
/// <param name="tableName">要创建的表名</param>
/// <returns>DataTable对象</returns>
public static DataTable ToDataTable<T>(IList<T> datas, string tableName)
{
Type type = typeof(T);
if (string.IsNullOrEmpty(tableName))
{
tableName = type.Name;
}
DataTable table = new DataTable(tableName);
table.BeginLoadData();
PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo info in properties)
{
string typeName = info.PropertyType.ToString();
if (info.PropertyType.IsGenericType)
{
typeName = info.PropertyType.GetGenericArguments()[0].ToString();
}
Type type2 = Type.GetType(typeName, false);
if (type2 != null)
{
table.Columns.Add(info.Name, type2);
}
}
if ((datas != null) && (datas.Count > 0))
{
foreach (object obj2 in datas)
{
DataRow row = table.NewRow();
foreach (PropertyInfo info2 in properties)
{
if ((Type.GetType(info2.PropertyType.ToString(), false) != null) && (info2.GetValue(obj2, null) != null))
{
row[info2.Name] = info2.GetValue(obj2, null);
}
}
table.Rows.Add(row);
}
}
table.EndLoadData();
table.AcceptChanges();
return table;
} /// <summary>
/// 转换IList对象为DataTable对象
/// </summary>
/// <param name="datas">数据集合</param>
/// <param name="tableName">要创建的表名</param>
/// <returns>DataTable对象</returns>
public static DataTable ToDataTable<T>(T[] datas, string tableName)
{
IList<T> list;
if ((datas == null) || (datas.Length == 0))
{
list = new List<T>();
}
else
{
list = new List<T>(datas);
}
return ToDataTable<T>(list, tableName);
} /// <summary>
/// 转换 DataTable 对象为 IList 对象
/// </summary>
/// <param name="datas">数据集合</param>
/// <returns>IList 对象</returns>
public static IList<T> ToList<T>(DataTable datas) where T : class, new()
{
IList<T> list = new List<T>();
if ((datas != null) && (datas.Rows.Count != 0))
{
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (DataRow row in datas.Rows)
{
T local = Activator.CreateInstance<T>();
foreach (DataColumn column in datas.Columns)
{
object obj2 = null;
if (row.RowState == DataRowState.Deleted)
{
obj2 = row[column, DataRowVersion.Original];
}
else
{
obj2 = row[column];
}
if (obj2 != DBNull.Value)
{
foreach (PropertyInfo info in properties)
{
if (column.ColumnName.Equals(info.Name, StringComparison.CurrentCultureIgnoreCase))
{
info.SetValue(local, obj2, null);
}
}
}
}
list.Add(local);
}
}
return list;
} /// <summary>
/// DataTable To List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static IList<T> ConvertToList<T>(DataTable dt) where T : class, new()
{
// 定义集合
IList<T> ts = new List<T>();
// 获得此模型的类型
Type type = typeof(T);
string tempName = "";
foreach (DataRow dr in dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
// 检查DataTable是否包含此列
tempName = pi.Name;
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite)
{
continue;
}
object value = dr[tempName];
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
ts.Add(t);
}
return ts;
} /// <summary>
/// 将集合类转换成DataTable (标准写法)
/// </summary>
/// <param name="list">集合</param>
/// <returns></returns>
public static DataTable ConvertToDataTable<T>(IList<T> list)
{
DataTable result = new DataTable();
if (list.Count > 0)
{
Type type = typeof(T);
result.TableName = type.Name;
PropertyInfo[] propertys = type.GetProperties(); foreach (PropertyInfo pi in propertys)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
foreach (object t in list)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(t, null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
} /// <summary>
/// 转换类型
/// </summary>
/// <param name="property"></param>
/// <returns></returns>
public static DbType ConvertType(PropertyInfo property)
{
var dbTypeResult = DbType.String;
if (property == null)
{
return dbTypeResult;
} var typeName = property.PropertyType.Name;
switch (typeName)
{
case "UInt64":
dbTypeResult = DbType.UInt64;
break;
case "String":
dbTypeResult = DbType.String;
break;
case "Int32":
dbTypeResult = DbType.Int32;
break;
case "SByte":
dbTypeResult = DbType.SByte;
break;
case "DateTime":
dbTypeResult = DbType.DateTime;
break;
case "UInt32":
dbTypeResult = DbType.UInt32;
break;
case "Byte":
dbTypeResult = DbType.Byte;
break;
case "Decimal":
dbTypeResult = DbType.Decimal;
break;
case "UInt16":
dbTypeResult = DbType.UInt16;
break;
}
return dbTypeResult;
}
}
}
4、两个实体：
====CarEntity====
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace ReadyExcel
{
public class CarEntity
{ /// <summary>
/// 卡片号码
/// </summary>
public string SIMNo { get; set; } public string ICCID { get; set; } public string IMSI { get; set; } public string IMEI { get; set; } /// <summary>
/// 发卡（开户）日期
/// </summary>
public string Saledate { get; set; } /// <summary>
/// 状态：1未知、2正常、3停机、4未激活、5销号
/// </summary>
public string Status { get; set; } /// <summary>
/// 卡（VKEL）状态：1测试期、2沉默期、3服务期、4服务即将到期、5停机、6暂停使用、7保号期、8已销号、100其他(默认)
/// </summary>
public string CardStatus { get; set; } = "其他"; /// <summary>
/// 开机状态：1未知、2在线、3离线、4关机
/// </summary>
public string ActiveStatus { get; set; } /// <summary>
/// 月实时流量
/// </summary>
public string MonthFlow { get; set; } /// <summary>
/// 运营商类型，1 移动，2 联通，3 电信
/// </summary>
public string OperatorsType { get; set; } /// <summary>
/// 总流量
/// </summary>
public string AvailableFlow { get; set; } /// 主套餐名称
/// </summary>
public string MainPackage { get; set; } /// <summary>
/// 服务到期开始时间
/// </summary>
public string ServiceStartTime { get; set; } /// <summary>
/// 服务到期结束时间
/// </summary>
public string ServiceEndTime { get; set; } /// <summary>
/// 认证状态：1未认证、2认证中、3已认证、4未通过
/// </summary>
public string AuthState { get; set; } /// <summary>
/// 最后续费日期
/// </summary>
public string RenewDate { get; set; } /// <summary>
/// 停机保号：有：无
/// </summary>
public string Suspend { get; set; } /// <summary>
/// 已用流量
/// </summary>
public string UsedFlow { get; set; } /// <summary>
/// 流量使用率(%)
/// </summary>
public string UsageRateFlow { get; set; } /// <summary>
/// 渠道价
/// </summary>
public string ChannelPrice { get; set; }
}
}
====CarPrice====
namespace ReadyExcel
{
public class CarPrice
{
public string CarNo { get; set; } public string ICCID { get; set; } public string IMSI { get; set; } /// 主套餐名称
/// </summary>
public string MainPackage { get; set; } public string count { get; set; } public string price { get; set; } public string amount { get; set; }
}
}