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 }
