|
前言
用.net6开发一个Winform程序,处理Excel文件,并把结果导出Excel文件。
要用到两个算法,一是turf.js库的booleanPointInPolygon方法,判断经纬度坐标是否在区域内;二是经纬度纠偏算法,因为对方给的区域坐标集合有偏移,需要纠偏。
这两个算法,网上找C#的实现,一是不好找;二是找来的不信任,我还要测试以确保没有问题。我之前做电子地图使用过turf.js库和js版本的纠偏算法,比较信任,确定没有问题。
所以我就打算通过C#调用js库的方法,来实现数据处理。
安装ClearScript
ClearScript是微软开源的js引擎,支持windows、linux、mac。
NuGet搜索安装:
Microsoft.ClearScript.Core
Microsoft.ClearScript.V8
Microsoft.ClearScript.V8.Native.win-x64
引入js文件
把leaflet.mapCorrection.js、turf.v6.5.0.min.js和自己写的calc.js放入工程中,右击属性设置复制到输出目录:如果较新则复制。
calc.js通过调用leaflet.mapCorrection.js和turf.v6.5.0.min.js中的方法实现功能,文件内容如下:- function calc(lng, lat, polygonStr) {
- var point = turf.point([lng, lat]);
- var polygonPoints = JSON.parse(polygonStr);
- var polygon = turf.polygon(polygonPoints);
- var bl = turf.booleanPointInPolygon(point, polygon);
- return bl;
- }
- function correct(lng, lat) {
- var newPoint = new CoordConvertor().gcj02_To_gps84(lng, lat);
- return newPoint;
- }
复制代码 创建V8ScriptEngine对象
- private V8ScriptEngine _engine = new V8ScriptEngine();
复制代码 通过js引擎加载js文件
在Form1_Load方法中添加如下代码:- _engine.AddHostType("Console", typeof(Console));
- string fileName = AppDomain.CurrentDomain.BaseDirectory + "turf.v6.5.0.min.js";
- string js;
- using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
- {
- byte[] bArr = new byte[fs.Length];
- await fs.ReadAsync(bArr, 0, bArr.Length);
- js = ASCIIEncoding.UTF8.GetString(bArr);
- }
- _engine.Execute(js);
- fileName = AppDomain.CurrentDomain.BaseDirectory + "calc.js";
- using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
- {
- byte[] bArr = new byte[fs.Length];
- await fs.ReadAsync(bArr, 0, bArr.Length);
- js = ASCIIEncoding.UTF8.GetString(bArr);
- }
- _engine.Execute(js);
- fileName = AppDomain.CurrentDomain.BaseDirectory + "leaflet.mapCorrection.js";
- using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
- {
- byte[] bArr = new byte[fs.Length];
- await fs.ReadAsync(bArr, 0, bArr.Length);
- js = ASCIIEncoding.UTF8.GetString(bArr);
- }
- _engine.Execute(js);
复制代码 C#调用js方法实现经纬度坐标纠偏
- double lng = Convert.ToDouble(lnglat[0]);
- double lat = Convert.ToDouble(lnglat[1]);
- //坐标纠偏
- dynamic newPoint = _engine.Invoke("correct", new object[] { lng, lat });
- lng = newPoint.lng;
- lat = newPoint.lat;
复制代码 C#调用js方法判断经纬度点位是否在多边形内
- //_selectedRegionPoints是多边形坐标点位集合json字符串
- bool bl = (bool)_engine.Invoke("calc", new object[] { lng, lat, _selectedRegionPoints });
复制代码 程序开发完成后发布
发布后文件夹拷贝到用户的win10系统中可以直接使用,不需要安装.net6环境。我自己的很老的win7 sp1虚拟机上跑不起来,ClearScriptV8.win-x64.dll无法加载成功,暂不知道为什么。
Form1.cs完整代码如下:
当时程序写的急,当然,程序还可以优化,不过没必要,要处理的数据量不大,功能没问题就行。
[code]using Models;using Newtonsoft.Json;using System.Drawing;using System.Text;using System.Text.RegularExpressions;using Microsoft.ClearScript.JavaScript;using Microsoft.ClearScript.V8;using NPOI.HSSF.UserModel;using NPOI.XSSF.UserModel;using NPOI.SS.UserModel;using System.Reflection;using System.Windows.Forms;using NPOI.Util;namespace 点位{ public partial class Form1 : Form { private Regions _regions; private List _cameraList = new List(); private V8ScriptEngine _engine = new V8ScriptEngine(); private string _selectedRegionPoints; public Form1() { InitializeComponent(); } private async void Form1_Load(object sender, EventArgs e) { //通过js引擎加载js文件 _engine.AddHostType("Console", typeof(Console)); string fileName = AppDomain.CurrentDomain.BaseDirectory + "turf.v6.5.0.min.js"; string js; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { byte[] bArr = new byte[fs.Length]; await fs.ReadAsync(bArr, 0, bArr.Length); js = ASCIIEncoding.UTF8.GetString(bArr); } _engine.Execute(js); fileName = AppDomain.CurrentDomain.BaseDirectory + "calc.js"; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { byte[] bArr = new byte[fs.Length]; await fs.ReadAsync(bArr, 0, bArr.Length); js = ASCIIEncoding.UTF8.GetString(bArr); } _engine.Execute(js); fileName = AppDomain.CurrentDomain.BaseDirectory + "leaflet.mapCorrection.js"; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { byte[] bArr = new byte[fs.Length]; await fs.ReadAsync(bArr, 0, bArr.Length); js = ASCIIEncoding.UTF8.GetString(bArr); } _engine.Execute(js); //行政区划下拉列表初始化 fileName = AppDomain.CurrentDomain.BaseDirectory + "安徽.json"; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { byte[] bArr = new byte[fs.Length]; await fs.ReadAsync(bArr, 0, bArr.Length); string json = ASCIIEncoding.UTF8.GetString(bArr); _regions = JsonConvert.DeserializeObject(json); } List citys = _regions.RECORDS.ToList().FindAll(a => a.civilcode.Length == 4); cbxCity.DataSource = citys; cbxCity.DisplayMember = "civilname"; cbxCity.ValueMember = "civilcode"; } private void button1_Click(object sender, EventArgs e) { openFileDialog1.Title = "选择要处理的Excel文件"; openFileDialog1.Filter = "Excel文件(*.xlsx)|*.xlsx"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { } } } private void cbxCity_SelectedIndexChanged(object sender, EventArgs e) { Records record = cbxCity.SelectedItem as Records; List citys = _regions.RECORDS.ToList().FindAll(a => a.civilcode.Length > 4 && a.civilcode.Substring(0, 4) == record.civilcode); citys.Insert(0, new Records() { civilcode = null, civilname = "==请选择==" }); cbxCounty.DataSource = citys; cbxCounty.DisplayMember = "civilname"; cbxCounty.ValueMember = "civilcode"; } private void cbxCounty_SelectedIndexChanged(object sender, EventArgs e) { Records record = cbxCounty.SelectedItem as Records; if (record.civilcode == null) { record = cbxCity.SelectedItem as Records; } Regex regex = new Regex(@"^POLYGON\((\(.*\),?)*\)$"); var mc = regex.Matches(record.polygongeo); StringBuilder sb = new StringBuilder(); foreach (Match m in mc) { string value = m.Groups[1].Value.TrimStart('(').TrimEnd(')'); string[] lnglatArr = value.Split(','); bool first = true; if (sb.Length > 0) { sb.Append(","); } sb.Append("[["); foreach (string lnglatStr in lnglatArr) { string[] lnglat = lnglatStr.Trim().Split(' '); double lng = Convert.ToDouble(lnglat[0]); double lat = Convert.ToDouble(lnglat[1]); //坐标纠偏 dynamic newPoint = _engine.Invoke("correct", new object[] { lng, lat }); lng = newPoint.lng; lat = newPoint.lat; if (first) { first = false; sb.AppendFormat($"[{lng}, {lat}]"); } else { sb.AppendFormat($",[{lng}, {lat}]"); } } sb.Append("]]"); } _selectedRegionPoints = sb.ToString(); } private async void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e) { await Task.Delay(10); //读取Excel _cameraList = new List(); using (FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheetAt(0); for (int i = 1; i { if (!string.IsNullOrWhiteSpace(cameraInfo.Lng) && !string.IsNullOrWhiteSpace(cameraInfo.Lat)) { double lng = Convert.ToDouble(cameraInfo.Lng); double lat = Convert.ToDouble(cameraInfo.Lat); bool bl = (bool)_engine.Invoke("calc", new object[] { lng, lat, _selectedRegionPoints }); if (bl) //区域内 { return false; } else //区域外 { return true; } } else { return false; } }); saveFileDialog1.Title = "选择处理结果要保存的位置及文件名"; saveFileDialog1.Filter = "Excel文件(*.xlsx)|*.xlsx"; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { if (File.Exists(saveFileDialog1.FileName)) { File.Delete(saveFileDialog1.FileName); } string template = AppDomain.CurrentDomain.BaseDirectory + "点位模板.xlsx"; XSSFWorkbook workbook; using (FileStream fs = new FileStream(template, FileMode.Open, FileAccess.Read, FileShare.Read)) { workbook = new XSSFWorkbook(fs); using (FileStream fs2 = new FileStream(saveFileDialog1.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { ISheet sheet = workbook.GetSheetAt(0); sheet.RemoveRow(sheet.GetRow(1)); sheet.RemoveRow(sheet.GetRow(2)); Dictionary cellStyles = GetCellStyles(sheet); int i = 1; foreach (CameraInfo cameraInfo in _cameraList) { IRow row = sheet.CreateRow(i); ICell cell1 = row.CreateCell(1, CellType.String); ICell cell2 = row.CreateCell(2, CellType.String); ICell cell3 = row.CreateCell(3, CellType.String); ICell cell4 = row.CreateCell(4, CellType.String); ICell cell5 = row.CreateCell(5, CellType.String); ICell cell6 = row.CreateCell(6, CellType.String); ICell cell7 = row.CreateCell(7, CellType.String); ICell cell8 = row.CreateCell(8, CellType.String); ICell cell9 = row.CreateCell(9, CellType.String); ICell cell10 = row.CreateCell(10, CellType.String); ICell cell11 = row.CreateCell(11, CellType.String); ICell cell12 = row.CreateCell(12, CellType.String); SetCellStyles(row, cellStyles); cell1.SetCellValue(cameraInfo.CameraNo); cell2.SetCellValue(cameraInfo.City); cell3.SetCellValue(cameraInfo.County); cell4.SetCellValue(cameraInfo.CameraName); cell5.SetCellValue(cameraInfo.Lng); cell6.SetCellValue(cameraInfo.Lat); cell7.SetCellValue(cameraInfo.CameraFunType); cell8.SetCellValue(cameraInfo.Region); cell9.SetCellValue(cameraInfo.Type); cell10.SetCellValue(cameraInfo.Status); cell11.SetCellValue(cameraInfo.Mac); cell12.SetCellValue(cameraInfo.Ip); i++; } workbook.Write(fs2); } MessageBox.Show("完成"); } } } private Dictionary GetCellStyles(ISheet sheet) { var styleRow = sheet.GetRow(5); Dictionary result = new Dictionary(); for (int i = 1; i |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|