将地址转化为高德地图坐标(这次是用于云图数据模板)

  1 class Program
  2     {
  3         static void Main(string[] args)
  4         {
  5             var dt = ExcelToDataTable(@"C:\Users\ZengJW\Desktop\站点地址.xls", true);
  6             List<string> address = new List<string>();//地址集合
  7             for (int i = 0; i < dt.Rows.Count; i++)
  8             {
  9                 address.Add(dt.Rows[i][1].ToString());
 10             }
 11             if (DataTableToExcel(AddressToAmap(address.ToArray()), @"C:\Users\ZengJW\Desktop\test.xls"))
 12             {
 13                 Console.WriteLine("ok！");
 14             }
 15 
 16 
 17         }
 18 
 19         /// <summary>
 20         /// 将地址转化为高德地图坐标（云图数据模板）
 21         /// </summary>
 22         /// <param name="str">地址</param>
 23         /// <returns>返回表格，列1为name，列2为address，列3为经度，列4为纬度，列5为telephone</returns>
 24         public static DataTable AddressToAmap(params string[] address)
 25         {
 26             DataTable dt = new DataTable();
 27             dt.Columns.Add("name");
 28             dt.Columns.Add("address");
 29             dt.Columns.Add("x");
 30             dt.Columns.Add("y");
 31             dt.Columns.Add("telephone");
 32 
 33             //把key替换成自己的~
 34             StringBuilder sb = new StringBuilder("https://restapi.amap.com/v3/place/text?s=rsv3&key=******************************&page=1&offset=10&city=440600&language=zh_cn&callback=jsonp_585151_&platform=JS&logversion=2.0&sdkversion=1.3&appname=https://lbs.amap.com/console/show/picker&csid=1FCC8F20-A9E7-4E79-81F4-CE27742A797C&keywords=");
 35             HttpClient hc = new HttpClient();
 36             for (int i = 0; i < address.Length; i++)
 37             {
 38 
 39                 //把需要查询的地址拼接到url
 40                 sb.Append(address[i]);
 41                 string queryStr = sb.ToString();
 42                 var responseMessage = hc.GetAsync(queryStr);
 43                 var content = responseMessage.Result.Content.ReadAsStringAsync().Result;
 44 
 45                 //去掉地址(查询字符串中"keywords="后面的地址)，重复使用
 46                 sb.Remove(queryStr.LastIndexOf('=') + 1, queryStr.Length - (queryStr.LastIndexOf('=') + 1));
 47 
 48                 //去掉多余字符串，保留核心数据
 49                 dynamic data = JsonConvert.DeserializeObject(content.Substring(content.IndexOf('(') + 1, content.Length - content.IndexOf('(') - 1 - 1));
 50 
 51                 //取搜索结果第一项
 52                 if (data.pois == null || data.pois.Count == 0)
 53                 {
 54                     continue;//给定地址搜索结果为空，跳过
 55                 }
 56                 dynamic poi = data.pois[0];
 57                 string[] location = poi.location.ToString().Split(',');//分割经纬度
 58                 string tel = "00000000000";
 59                 if (string.IsNullOrEmpty(poi.tel.ToString()))
 60                 {
 61                     tel.Replace(':', ',');//tel不为空时
 62                 }
 63 
 64                 //存储poi数据
 65                 dt.Rows.Add(poi.name, poi.address, location[0], location[1], tel);
 66             }
 67             hc.Dispose();
 68             return dt;
 69         }
 70 
 71         /// <summary>
 72         /// 将excel导入到datatable
 73         /// </summary>
 74         /// <param name="filePath">excel路径</param>
 75         /// <param name="isColumnName">第一行是否是列名</param>
 76         /// <returns>返回datatable</returns>
 77         public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
 78         {
 79             DataTable dataTable = null;
 80             FileStream fs = null;
 81             DataColumn column = null;
 82             DataRow dataRow = null;
 83             IWorkbook workbook = null;
 84             ISheet sheet = null;
 85             IRow row = null;
 86             ICell cell = null;
 87             int startRow = 0;
 88             try
 89             {
 90                 using (fs = File.OpenRead(filePath))
 91                 {
 92                     // 2007版本
 93                     if (filePath.IndexOf(".xlsx") > 0)
 94                         workbook = new XSSFWorkbook(fs);
 95                     // 2003版本
 96                     else if (filePath.IndexOf(".xls") > 0)
 97                         workbook = new HSSFWorkbook(fs);
 98                     if (workbook != null)
 99                     {
100                         sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
101                         dataTable = new DataTable();
102                         if (sheet != null)
103                         {
104                             int rowCount = sheet.LastRowNum;//总行数
105                             if (rowCount > 0)
106                             {
107                                 IRow firstRow = sheet.GetRow(0);//第一行
108                                 int cellCount = firstRow.LastCellNum;//列数
109                                                                      //构建datatable的列
110                                 if (isColumnName)
111                                 {
112                                     startRow = 1;//如果第一行是列名，则从第二行开始读取
113                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
114                                     {
115                                         cell = firstRow.GetCell(i);
116                                         if (cell != null)
117                                         {
118                                             if (cell.StringCellValue != null)
119                                             {
120                                                 column = new DataColumn(cell.StringCellValue);
121                                                 dataTable.Columns.Add(column);
122                                             }
123                                         }
124                                     }
125                                 }
126                                 else
127                                 {
128                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
129                                     {
130                                         column = new DataColumn("column" + (i + 1));
131                                         dataTable.Columns.Add(column);
132                                     }
133                                 }
134                                 //填充行
135                                 for (int i = startRow; i <= rowCount; ++i)
136                                 {
137                                     row = sheet.GetRow(i);
138                                     if (row == null) continue;
139                                     dataRow = dataTable.NewRow();
140                                     for (int j = row.FirstCellNum; j < cellCount; ++j)
141                                     {
142                                         cell = row.GetCell(j);
143                                         if (cell == null)
144                                         {
145                                             dataRow[j] = "";
146                                         }
147                                         else
148                                         {
149                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
150                                             switch (cell.CellType)
151                                             {
152                                                 case CellType.Blank:
153                                                     dataRow[j] = "";
154                                                     break;
155                                                 case CellType.Numeric:
156                                                     short format = cell.CellStyle.DataFormat;
157                                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
158                                                     if (format == 14 || format == 31 || format == 57 || format == 58)
159                                                         dataRow[j] = cell.DateCellValue;
160                                                     else
161                                                         dataRow[j] = cell.NumericCellValue;
162                                                     break;
163                                                 case CellType.String:
164                                                     dataRow[j] = cell.StringCellValue;
165                                                     break;
166                                             }
167                                         }
168                                     }
169                                     dataTable.Rows.Add(dataRow);
170                                 }
171                             }
172                         }
173                     }
174                 }
175                 return dataTable;
176             }
177             catch (Exception)
178             {
179                 if (fs != null)
180                 {
181                     fs.Close();
182                 }
183                 return null;
184             }
185         }
186 
187         /// <summary>
188         /// 将datatable导入到excel
189         /// </summary>
190         /// <param name="dt">需要导入的数据</param>
191         /// <returns>导入结果</returns>
192         public static bool DataTableToExcel(DataTable dt, string filePath)
193         {
194             bool result = false;
195             IWorkbook workbook = null;
196             FileStream fs = null;
197             IRow row = null;
198             ISheet sheet = null;
199             ICell cell = null;
200             try
201             {
202                 if (dt != null && dt.Rows.Count > 0)
203                 {
204                     workbook = new HSSFWorkbook();
205                     sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表
206                     int rowCount = dt.Rows.Count;//行数
207                     int columnCount = dt.Columns.Count;//列数
208                                                        //设置列头
209                     row = sheet.CreateRow(0);//excel第一行设为列头
210                     for (int c = 0; c < columnCount; c++)
211                     {
212                         cell = row.CreateCell(c);
213                         cell.SetCellValue(dt.Columns[c].ColumnName);
214                     }
215                     //设置每行每列的单元格,
216                     for (int i = 0; i < rowCount; i++)
217                     {
218                         row = sheet.CreateRow(i + 1);
219                         for (int j = 0; j < columnCount; j++)
220                         {
221                             cell = row.CreateCell(j);//excel第二行开始写入数据
222                             cell.SetCellValue(dt.Rows[i][j].ToString());
223                         }
224                     }
225                     using (fs = File.OpenWrite(filePath))
226                     {
227                         workbook.Write(fs);//向打开的这个xls文件中写入数据
228                         result = true;
229                     }
230                 }
231                 return result;
232             }
233             catch (Exception ex)
234             {
235                 if (fs != null)
236                 {
237                     fs.Close();
238                 }
239                 return false;
240             }
241         }
242 
243     }

View Code

Excel导入导出