基于apose.cells的excel二次开发
最近有小伙伴有这样的需求，有一个excel表格，其中需要按照某一列中的数据，筛选出某一类相同的数据，然后将该类数据放到一起，还需要将该类数据数量进行统计。说起来有点抽象。我们来看一下具体数据。其中左侧的框选中数据就是需要我们筛选到一起。该列中的数据横杠需要对左边的字符进行截取，现在需要截取8位，不足8位的就全部截取，而右边部分都需要截取完。
我这里是这样想的，首先使用apose.cell将表格中所有数据单元格读取完毕。将这些数据放到一个list中。然后遍历刚才截取字符放在Dictionary中，其中Dictionary的key和value都为唯一字符。经过这样一步后，我们就取到唯一字符，用于分组。利用分组字符来对起始的list数据进行遍历，如果说该条记录中的字符包含了该key，我们就归为该类数据。其中每一类数据，我们都使用一个list来存储。这样我们就做好了数据，下面就是将数据重新填写到excel中，进行统计，和单元格的合并。具体的实现功能大家可以参考代码。
首先我们创建一个辅助类：
using System;
using System.Collections.Generic;
using System.Text;
namespace UPExcel_rm
{
class EXCEInfo
{
private string strDJRQ;/*单据日期*/
private string strXSDDDH;/*销售订单单号*/
private string strWLMC;/*物料名称*/
private string strPH;/*批号*/
private string strJBSL;/*基本数量*/
private string id;/*每一条记录的id*/
public EXCEInfo(string strDJRQ, string strXSDDDH, string strWLMC, string strPH,
string strJBSL,string id)
{
this.strDJRQ = strDJRQ;
this.strXSDDDH = strXSDDDH;
this.strWLMC = strWLMC;
this.strPH = strPH;
this.strJBSL = strJBSL;
this.id = id;
}
public EXCEInfo() {
}
public void setID(string id) {
this.id = id;
}
public string getID() {
return this.id;
}
public void setDJRQ(string _strDJRQ)
{
this.strDJRQ = _strDJRQ;
}
public void setXSDDDH(string _strXSDDDH)
{
this.strXSDDDH = _strXSDDDH;
}
public void setWLMC(string _strWLMC)
{
this.strWLMC = _strWLMC;
}
public void setPH(string _PH)
{
this.strPH = _PH;
}
public void setJBSL(string _strJBSL)
{
this.strJBSL = _strJBSL;
}
public string getDJRQ()
{
return this.strDJRQ;
}
public string getXSDDDH()
{
return this.strXSDDDH;
}
public string getWLMC()
{
return this.strWLMC;
}
public string getPH()
{
return this.strPH;
}
public string getJBSL()
{
return this.strJBSL;
}
}
}
然后就是功能的实现代码，大家需要注意，其中获取唯一字符作为key，以及数据的筛选、excel单元格的合并。
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
namespace UPExcel_rm
{
public partial class Form1 : Form
{
private String fileName = "";
List<EXCEInfo> listExcelInfos = new List<EXCEInfo>();
public Form1()
{
InitializeComponent();
}
private void BTN_OPEN_EXCEL_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
file.Multiselect = false;
if (file.ShowDialog() == DialogResult.Cancel)
{
return;
}
String path = file.FileName;
string fileSuffix = System.IO.Path.GetExtension(path);
if (string.IsNullOrEmpty(fileSuffix))
{
System.Windows.Forms.MessageBox.Show("文件没有后缀");
return;
}
fileName = path;
this.tb_filename.Text = fileName;
}
private void BTN_RUN_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(fileName))
{
System.Windows.Forms.MessageBox.Show("文件为空");
return;
}
Workbook workbook = new Workbook();
workbook.Open(fileName);
Worksheets worksheets = workbook.Worksheets;
String myfileName = System.IO.Path.GetFileNameWithoutExtension(fileName);
int CNT = worksheets.Count;
for (int i = 0; i < CNT; i++)
{
Cells cells = workbook.Worksheets[i].Cells;
int maxColum = cells.MaxColumn;
int maxRow = cells.MaxRow;
for (int row = 0; row < maxRow; row++)
{
EXCEInfo excelInfo = new EXCEInfo();
for (int colum = 0; colum < maxRow; colum++)
{
Cell cell = cells[row, colum];
string StrValue = cell.StringValue.Trim();
if (colum == 0) {
excelInfo.setID(StrValue);
}
else if (colum == 1)
{
excelInfo.setDJRQ(StrValue);
}
else if (colum == 2)
{
excelInfo.setXSDDDH(StrValue);
}
else if (colum == 3)
{
excelInfo.setWLMC(StrValue);
}
else if (colum == 4)
{
excelInfo.setPH(StrValue);
}
else if (colum == 5)
{
excelInfo.setJBSL(StrValue);
}
}
listExcelInfos.Add(excelInfo);
}
}
Dictionary<string, string> uniqueKVP = new Dictionary<string, string>();
uniqueKVP = getUniqueKVP(listExcelInfos);
Dictionary<string, List<EXCEInfo>> res = getUniqueList(uniqueKVP,listExcelInfos);
if (ExtInfosToTable(res))
{
System.Windows.Forms.MessageBox.Show("处理成功");
}
else {
System.Windows.Forms.MessageBox.Show("处理失败");
}
}
private bool ExtInfosToTable(Dictionary<string, List<EXCEInfo>> res)
{
bool isSuc = false;
try
{
/*创建工作薄*/
Workbook wb = new Workbook();
/*创建样式*/
Style style = wb.Styles[wb.Styles.Add()];
/*设置单元格水平居中对齐和垂直居中对齐*/
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
/*新建工作表*/
Worksheet ws = wb.Worksheets[0];
/*第一条用来标记表头*/
int row = 0;
foreach (KeyValuePair<string, List<EXCEInfo>> kv in res) {
string key = kv.Key;
List<EXCEInfo> tmpList = res[key];
int tmpValue = 0;
for (int i = 0; i < tmpList.Count; i++)
{
ws.Cells[row, 0].PutValue(tmpList[i].getID());
ws.Cells[row, 1].PutValue(tmpList[i].getDJRQ());
ws.Cells[row, 2].PutValue(tmpList[i].getXSDDDH());
ws.Cells[row, 3].PutValue(tmpList[i].getWLMC());
ws.Cells[row,4].PutValue(tmpList[i].getPH());
ws.Cells[row, 5].PutValue(tmpList[i].getJBSL());
tmpValue += getNUM(tmpList[i].getJBSL());
row++;
}
/*统计将值填入到excel，合并单元格*/
ws.Cells[row - tmpList.Count, 6].PutValue(Convert.ToString(tmpValue));
ws.Cells.Merge(row - tmpList.Count, 6, tmpList.Count,1);
}
/*设置所有列为自适应列宽*/
ws.AutoFitColumns();
string path = GetAssemblyPath();
string filePath = System.IO.Path.Combine(path, "result.xls");
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
FileStream fs = System.IO.File.Create(filePath);
fs.Close();
wb.Save(filePath);
isSuc = true;
}
catch (Exception e)
{
isSuc = false;
}
return isSuc;
}
private void button1_Click(object sender, EventArgs e)
{
string str = "XECJBFCN4842-1400A01";
string str2 = str.Substring(str.LastIndexOf("-") - 8);
string str3 = str2;
}
/// <summary>
/// 又字符获取数字
/// </summary>
/// <param name="str_num"></param>
/// <returns></returns>
private int getNUM(string str_num) {
int res = 0;
try
{
res= int.Parse(str_num);
return res;
}
catch (Exception e)
{
System.Console.WriteLine(e.ToString());
}
return res;
}
/// <summary>
/// 获取某一类下的数据
/// </summary>
/// <param name="uniqueKVP"></param>
/// <param name="listExcelInfos"></param>
private Dictionary<string, List<EXCEInfo>> getUniqueList(Dictionary<string, string> uniqueKVP, List<EXCEInfo> listExcelInfos)
{
Dictionary<string, List<EXCEInfo>> res = new Dictionary<string, List<EXCEInfo>>();
for (int i = 0; i < listExcelInfos.Count; i++)
{
foreach (KeyValuePair<string, string> kv in uniqueKVP)
{
string key = kv.Key;
/*将该物料归到某一类下*/
if (listExcelInfos[i].getWLMC().Contains(key))
{
if (!res.ContainsKey(key))
{
if (isExsit(res, listExcelInfos[i]))
{
continue;
}
List<EXCEInfo> tmpList=new List<EXCEInfo>();
tmpList.Add(listExcelInfos[i]);
res.Add(key, tmpList);
}
else
{
List<EXCEInfo> tmpList = res[key];
bool iss = isExsit(res, listExcelInfos[i]);
if (!isExsit(res, listExcelInfos[i]))
{
tmpList.Add(listExcelInfos[i]);
res.Remove(key);
res.Add(key, tmpList);
}
}
}
}
}
return res;
}
private bool isExsit(Dictionary<string, List<EXCEInfo>> res, EXCEInfo curExceInfo)
{
bool isE = false;
foreach (KeyValuePair<string, List<EXCEInfo>> kv in res) {
string key = kv.Key;
List<EXCEInfo> tmpList = res[key];
for (int i = 0; i < tmpList.Count; i++)
{
EXCEInfo tmp = tmpList[i];
if (tmp.getJBSL().Equals(curExceInfo.getJBSL()) && tmp.getPH().Equals(curExceInfo.getPH()) &&
tmp.getWLMC().Equals(curExceInfo.getWLMC()) && tmp.getXSDDDH().Equals(curExceInfo.getXSDDDH())
&& tmp.getDJRQ().Equals(curExceInfo.getDJRQ()))
{
return true;
}
else
{
continue;
}
}
}
return isE;
}
//
/// <summary>
/// 获取分割字符中唯一的
/// </summary>
/// <param name="listExcelInfos"></param>
/// <returns></returns>
private Dictionary<string, string> getUniqueKVP(List<EXCEInfo> listExcelInfos)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
for (int i = 0; i < listExcelInfos.Count; i++)
{
string str = listExcelInfos[i].getWLMC();
string value="";
string beforeStr = str.Substring(0,str.LastIndexOf("-"));
if (beforeStr.Length<8)
{
value = beforeStr;
}
else if (beforeStr.Length>=8)
{
value= str.Substring(str.LastIndexOf("-") - 8);
}
string key = value;
if (!dic.ContainsKey(key))
{
dic.Add(key,value);
}
}
return dic;
}
private string GetAssemblyPath()
{
string CodeBasePath = System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
CodeBasePath = CodeBasePath.Substring(8, CodeBasePath.Length - 8);
string[] arrSection = CodeBasePath.Split(new char[] { '/' });
string FolderPath = "";
for (int i = 0; i < arrSection.Length - 1; i++)
{
FolderPath += arrSection[i] + "\\";
}
return FolderPath;
}
}
}
最后我们做出来的弹出框软件如下图所示。
界面有点丑，功能实现了就可以。最后我来看一下处理结果。
更多内容，请关注公众号