前几天接到小姐姐的求助,说工作使用Excel,收货地址没有区分省市区,还要一个一个复制粘贴,好烦啊~嘟嘟嘟~~~嘟嘟嘟~~
本着舔到就是赚到的精神,安排上了。。。
一.小姐姐具体需求，把红框里的字符串切割之后,赋值到黑框里。
二.程序员分析需求
1.Excel读写
2.字符串切割
^-^好像也没有多复杂哈,但是还是记录一下,以供以后用到的时候快速回忆~
贴出设计的UI(耗费大量脑细胞)
三.功能实现
1.Excel读取
Excel读取网上教程一大堆,具体所需要的DLL都一样,文章最后我会把工程放上去,这里就不多做描述,关于只DLL有一点说的,Unity高版本(我的是2018.4.0)能直接引用System.Data,无需导入system.data.dll,下面就是代码部分了,这里要注意的是,Excel表格后缀不同(xls,xlsx),读取的时候函数也是不同的(网上说1997~2003/2007版本,反正我不管,我就要说后缀不同),这里直接贴出代码,我把Excel里面的每一张表中所有内容都读取到了ExcelData类里面,所以就有了一个Dictionary<string, ExcelData>字典储存整张Excel表的数据,key为表名称,value为表数据。
/// <summary>
/// 读表
/// </summary>
/// <param name="path"></param>
/// <param name="versions"></param>
/// <returns></returns>
public static DataSet ReadExcel(string path, EVersions versions)
{
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = null;
switch (versions)
{
case EVersions.A1997To2003:
//1997-2003xls
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case EVersions.A2007://xlsx
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
default:
break;
}
DataSet result = excelReader.AsDataSet();
excelReader.Close();
return result;
}
/// <summary>
/// 获取全部表名称
/// </summary>
/// <param name="path"></param>
/// <param name="versions"></param>
/// <returns></returns>
public static List<DataTable> ReadTablesData(string path, EVersions versions)
{
DataSet resultds = ReadExcel(path, versions);
List<DataTable> vs = new List<DataTable>();
for (int i = 0; i < resultds.Tables.Count; i++)
{
vs.Add(resultds.Tables[i]);
}
return vs;
}
public static Dictionary<string, ExcelData> GetExcelData(string fileName, EVersions versions)
{
Dictionary<string, ExcelData> DataInfo = new Dictionary<string, ExcelData>();
List<DataTable> dataTables = ReadTablesData(fileName, versions);
List<List<string>> tempExcel = new List<List<string>>();
for (int k = 0; k < dataTables.Count; k++)
{
DataTable tabName = dataTables[k];
int sumColumn = tabName.Columns.Count;
int sumRow = tabName.Rows.Count;
for (int i = 0; i < sumRow; i++)
{
if (!string.IsNullOrEmpty(tabName.Rows[i][0].ToString()))
{
List<string> rowContentent = new List<string>();
for (int j = 0; j < sumColumn; j++)
{
rowContentent.Add(tabName.Rows[i][j].ToString());
}
tempExcel.Add(rowContentent);
}
}
ExcelData excelData = new ExcelData();
excelData.ExcelContent = tempExcel;
DataInfo.Add(tabName.TableName, excelData);
}
return DataInfo;
}
public class ExcelData
{
public List<List<string>> ExcelContent;
public ExcelData()
{
ExcelContent = new List<List<string>>();
}
}到这里Excel表格读取部分,就写完了,按照顺序,下面就要从一个字符串中匹配省市区了。UI界面交互有很多没有营养的代码,这里就不多做描述了。
2.匹配包含省市区的字符串
这里我给出了两种匹配方式,1是智能匹配(正则表达式)2.根据特殊字符切割(为了防止智能匹配不智能的情况)
先说简单的,特殊字符切割,这里严格来说不能叫匹配了,应该是叫切割,这里是让小姐姐自己在表格里面添加特殊字符,以用于切割字符串的标识,这里我设置的为*
贴出代码:
case EIncise.特殊字符:
string[] strGroup = str.Split(new char[] { '*' }, 4);
split = new SplitInfo(strGroup[0], strGroup[1], strGroup[2], strGroup[3]);
break;下面说智能匹配,智能匹配这里用到了正则表达式匹配,刚开始写了一串匹配组发现匹配成功率不是很高,只要正则表达式组中有1个组没有匹配到内容,那各个组中数据就全都没有了(在下学艺不精),贴出代码
/// <summary>
/// 获取城市信息
/// </summary>
/// <param name="address"></param>
/// <returns></returns>
public static SplitInfo SplitStrInfo(string address)
{
string regex = "(?<province>[^省]+省|.+自治区|上海|北京|天津|重庆|上海市|北京市|天津市|重庆市)(?<city>[^市]+市|.+自治州)(?<county>.*?区|[^县]+县|[^区].+区|.+镇|.+局)(?<village>.*)";
Regex r = new Regex(regex);
Match m = r.Match(address);
GroupCollection group = m.Groups;
string province = group["province"].Value;
string city = group["city"].Value;
string county = group["county"].Value;
string village = group["village"].Value;
return new SplitInfo(province, city, county, village);
}匹配率不高,那智能匹配就不智能了,那可不行,这里想到了分段匹配,思想就是,先匹配省,匹配到省,把除去省的剩余字符串匹配市,依次类推,
这样写的好处就是,就算省没有匹配到,也不会影响后面市,区的匹配,贴出代码
public static string SplitStr = "";
public static int CountyIndex = 0;
/// <summary>
/// 获取城市信息
/// </summary>
/// <param name="address"></param>
/// <returns></returns>
public static SplitInfo SplitStrInfo(string address)
{
Initialized();
string province = SplitStrProvince(address);
string city = SplitStrCity();
string county = SplitStrCounty();
string village = SplitStrVillage();
SplitInfo splitInfo = new SplitInfo(province, city, county, village);
//Debug.Log(splitInfo.ToString());
return splitInfo;
}
/// <summary>
/// 初始化
/// </summary>
private static void Initialized()
{
SplitStr = "";
CountyIndex = 0;
}
/// <summary>
/// 获取省
/// </summary>
/// <param name="address"></param>
/// <returns></returns>
public static string SplitStrProvince(string address)
{
string regex = "(?<province>[^省]+省|.+自治区|上海市|北京市|天津市|重庆市|上海|北京|天津|重庆)";
Regex r = new Regex(regex);
Match m = r.Match(address);
GroupCollection group = m.Groups;
Capture capture = group["province"];
string province = "";
province = capture.Value;
if (string.IsNullOrEmpty(province))
{
SplitStr = address;
}
else
{
SplitStr = address.Substring(capture.Length + capture.Index);
}
return province;
}
/// <summary>
/// 获取市
/// </summary>
/// <param name="address"></param>
/// <returns></returns>
public static string SplitStrCity()
{
string regex = "(?<city>[^市]+市|.+自治州)";
Regex r = new Regex(regex);
Match m = r.Match(SplitStr);
GroupCollection group = m.Groups;
Capture capture = group["city"];
string city = "";
city = capture.Value;
if (!string.IsNullOrEmpty(city))
{
SplitStr = SplitStr.Substring(capture.Length + capture.Index);
}
return city;
}
/// <summary>
/// 获取区
/// </summary>
/// <param name="address"></param>
/// <returns></returns>
public static string SplitStrCounty()
{
string regex = "(?<county>.*?区|[^县]+县|[^区].+区|.+镇|.+局)";
Regex r = new Regex(regex);
Match m = r.Match(SplitStr);
GroupCollection group = m.Groups;
Capture capture = group["county"];
string county = "";
county = group["county"].Value;
if (!string.IsNullOrEmpty(county))
{
SplitStr = SplitStr.Substring(capture.Length + capture.Index);
}
CountyIndex = capture.Index;
return county;
}
/// <summary>
/// 获取剩余地址
/// </summary>
/// <returns></returns>
public static string SplitStrVillage()
{
string village = "";
village = SplitStr.Substring(CountyIndex);
return village;
}这里写完测试,匹配率就很高,很智能了。
3.Excel写入
Excel写的功能,需要EPPlus.DLL的,后面工程中会提供,这里为了实现,预览切割前的数据,预览切割后的数据,这两个按钮的功能实现(虽然到现在还没有实现),所以在修改字典数据的时候重新Copy了一份字典,这里用到了深克隆,即引用地址是不同的,修改两个字典中的数据互不影响,下面贴出深克隆代码
public T DeepCopy<T>(T obj)
{
object retval;
using (MemoryStream memoryStream = new MemoryStream())
{
BinaryFormatter formatter = new BinaryFormatter();
//序列化
formatter.Serialize(memoryStream, obj);
memoryStream.Seek(0, SeekOrigin.Begin);
//反序列化
retval = formatter.Deserialize(memoryStream);
memoryStream.Close();
}
return (T)retval;
}到这里,我还是怕我写的软件,会耽误了小姐姐,所以我写Excel的时候是重新新建了一个表格,数据全部写进去,写没什么可说的,就是一行行的写,下面贴出代码
public static void WriteExcel(string _filePath, Dictionary<string, ExcelData> data)
{
if (File.Exists(_filePath))
{
string ext = Path.GetExtension(_filePath);
string fileNmae = Path.GetFileName(_filePath);
fileNmae = fileNmae + "(ZAZ)";
_filePath = fileNmae + ext;
}
FileInfo _excelName = new FileInfo(_filePath);
if (_excelName.Exists)
{
//删除旧文件，并创建一个新的 excel 文件。
_excelName.Delete();
_excelName = new FileInfo(_filePath);
}
//通过ExcelPackage打开文件
using (ExcelPackage package = new ExcelPackage(_excelName))
{
foreach (var item in data)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(item.Key);
for (int i = 0; i < item.Value.ExcelContent.Count; i++)
{
for (int J = 0; J < item.Value.ExcelContent[i].Count; J++)
{
worksheet.Cells[i + 1, J + 1].Value = item.Value.ExcelContent[i][J];
}
//worksheet.Cells[1, 2].Value = "姓名";
//worksheet.Cells[1, 3].Value = "性别";
}
//保存excel
package.Save();
}
}
#endregion
}最后贴出效果
1.界面设置(注意红框里列数均从0开始数)
2.表格对比(切割后,切割前)
vs
此软件用到的信息提示,用到的就是我之前写过的通用提示框,还是挺方便的~~
贴出码云工程地址 :https://gitee.com/dr_mayun/ExcelOperation.git
好了,文章到此就结束了,本文有什么错误的观点或者用法,希望各位看官能够及时提出,希望在自己积累知识的同时,能帮助到大家~
391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
