private ISheet _iSheet;
private IWorkbook _inWorkBook;
/// <summary>
/// 打开excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
using (FileDialog dlg = new OpenFileDialog())
{
dlg.CheckPathExists = true;
dlg.CheckFileExists = false;
dlg.AddExtension = true;
dlg.ValidateNames = true;
dlg.Title = "选择excel文件";
dlg.FilterIndex = 1;
dlg.RestoreDirectory = true;
dlg.Filter =
"支持格式 (*.xlsx*.xls)|*.xlsx;*.xls;|Excel (*.xlsx*.xls)|*.xlsx;*.xls";
if (dlg.ShowDialog() == DialogResult.OK)
{
try
{
using (var file = new FileStream(dlg.FileName, FileMode.Open, FileAccess.Read))
{
if (dlg.FileName.EndsWith(".xlsx") || dlg.FileName.EndsWith(".XLSX"))
{
_inWorkBook = new XSSFWorkbook(file);
}
else if (dlg.FileName.EndsWith(".xls") || dlg.FileName.EndsWith(".Xls") || dlg.FileName.EndsWith(".XLS"))
{
_inWorkBook = new HSSFWorkbook(file);
}
if (_inWorkBook != null)
{
// 读取xlsx xls中的工作表Sheet,显示出来让用户选择
for (int i = 0; i < _inWorkBook.NumberOfSheets; i++)
{
comboBox1.Items.Add(_inWorkBook.GetSheetName(i));
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
/// <summary>
/// 选择excel中的工作表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox2.Items.Clear();
comboBox3.Items.Clear();
_iSheet = _inWorkBook.GetSheet(comboBox1.SelectedItem.ToString());
List<ICell> iCells;
//默认读取第一行为列标题
if (_iSheet.LastRowNum > 0)
{
if (_iSheet.GetRow(0) != null)
{
iCells = _iSheet.GetRow(0).Cells;
}
else
{
MessageBox.Show("默认获取Excel第一行为列标题,检测到第一行内容为空!");
return;
}
}
else
{
MessageBox.Show("工作表为空!");
return;
}
foreach (ICell iCell in iCells)
{
String value = "";
// 支持数值和字符串类型
if (iCell.CellType.Equals(CellType.String))
{
value = iCell.StringCellValue;
}
else if (iCell.CellType.Equals(CellType.Numeric))
{
value = iCell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
}
else
{
MessageBox.Show(String.Format("第一行列名称:\"{0}\",类型:\"{1}\"不支持", value, iCell.CellType));
continue;
}
// 加载XY
comboBox2.Items.Add(value);
comboBox3.Items.Add(value);
}
}
/// <summary>
/// 读取excel的xy经纬度坐标并绘制
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
try
{
if (_inWorkBook != null)
{
if (comboBox1.SelectedIndex == -1 || comboBox2.SelectedIndex == -1 || comboBox3.SelectedIndex == -1)
{
MessageBox.Show("请确认输入选项填写完整!");
return;
}
}
// 获得XY索引
var XIndex = GetCellRealIndex(comboBox2.SelectedItem.ToString());
var YIndex = GetCellRealIndex(comboBox3.SelectedItem.ToString());
// 如果您还想读取其它字段作为属性,请在此处设置属性索引
var props = new Dictionary<short, string>(); // short对应属性列索引,string对应属性列名称
// 读取excel
List<int> errRows = new List<int>();
List<PointLatLngWithProperty> points = ExcelUtil.GetPointsFromOneSheet(_iSheet, XIndex, YIndex, props, ref errRows);
if (points == null || points.Count <= 0)
MessageBox.Show("没有读取到任何数据!");
if (errRows.Count > 0)
{
// 对读取错误的行进行提示
var errStr = "";
foreach (var row in errRows)
{
errStr += row + ",";
}
// 记录错误行号
errStr = errStr.Remove(errStr.Length - 1);
MessageBox.Show(String.Format("有{0}条数据存在问题。", errRows.Count));
};
// 绘制结果
foreach (var geo in points)
{
// AZMapMarkerGoogleType.red_small 为地标点的样式,可以随意指定
var marker = new AZMapMarkerGoogle(geo.Point, AZMapMarkerGoogleType.red_small)
{
IsHitTestVisible = true,
Properties = geo.Properties,
};
_graphicLayer.Markers.Add(marker);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 读取列索引
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private short GetCellRealIndex(String value)
{
var minColIx = _iSheet.GetRow(0).FirstCellNum;
var maxColIx = _iSheet.GetRow(0).LastCellNum;
for (var colIx = minColIx; colIx < maxColIx; colIx++)
{
var iCell = _iSheet.GetRow(0).GetCell(colIx);
if (iCell == null) continue;
String cellValue;
if (iCell.CellType.Equals(CellType.String))
{
cellValue = iCell.StringCellValue;
}
else if (iCell.CellType.Equals(CellType.Numeric))
{
cellValue = iCell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
}
else
{
continue;
}
if (cellValue.Equals(value))
{
return colIx;
}
}
return -1;
}
|