private void btn_convert_Click(object sender, EventArgs e)
{
var excelFilePath = this.txt_filepath.Text.Trim();
if (string.IsNullOrEmpty(excelFilePath))
{
MessageBox.Show("请选择Excel文件路径");
return;
}
var excelDataTable = NPOIUtility.GetDataFromExcel(excelFilePath);
var sqlData = new StringBuilder();
for (var i = 0; i < excelDataTable.Rows.Count; i++)
{
var userId = int.Parse(excelDataTable.Rows[i]["UserId"].ToString());
var publicTestId = int.Parse(excelDataTable.Rows[i]["PublicTestId"].ToString());
sqlData.Append("insert into [PublicTestUser]([UserId],[PublicTestId],[CityId]) values(");
sqlData.Append(userId);
sqlData.Append(",");
sqlData.Append(publicTestId);
sqlData.Append(",");
sqlData.Append("201);");
}
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.CreatePrompt = true;
saveDialog.OverwritePrompt = true;
saveDialog.DefaultExt = "sql";
saveDialog.Filter = "Text Files(*.sql)|*.sql";
if (DialogResult.OK == saveDialog.ShowDialog())
{
if (File.Exists(saveDialog.FileName))
{
DialogResult result = MessageBox.Show("是否覆盖？", "确定", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
StreamWriter swLog = new StreamWriter(saveDialog.FileName);
swLog.WriteLine(sqlData.ToString());
swLog.WriteLine();
swLog.Close();
}
if (result == DialogResult.No)
{
return;
}
}
else
{
StreamWriter swLog = new StreamWriter(saveDialog.FileName);
swLog.WriteLine(sqlData.ToString());
swLog.WriteLine();
swLog.Close();
}
}
}
/// <summary>
/// 東証から取得したExcelをデータモデルに変換
/// </summary>
/// <param name="filePash"></param>
/// <returns></returns>
public List <TokyoStockExchangeExcelModel> ExcelToDataModel(string filePash)
{
List <TokyoStockExchangeExcelModel> result = new List <TokyoStockExchangeExcelModel>();
if (File.Exists(filePash))
{
using (NPOIUtility npoi = new NPOIUtility(string.Empty, filePash))
{
npoi.SetWorkSheet(Define.Stock.ExcelSheetName);
int lastRow = npoi.Sheet.LastRowNum;
for (int i = 0; i <= lastRow - 1; i++)
{
if (i == 0)
{
//１行目はヘッダーなので無視
continue;
}
npoi.SetWorkRow(i);
TokyoStockExchangeExcelModel list = new TokyoStockExchangeExcelModel();
list.UpdatedDate = npoi.GetCellValue(npoi.Row.GetCell(0));
list.Code = npoi.GetCellValue(npoi.Row.GetCell(1));
list.Name = npoi.GetCellValue(npoi.Row.GetCell(2));
list.MarketName = npoi.GetCellValue(npoi.Row.GetCell(3));
list.Category33Code = Tools.IsHyphen(npoi.GetCellValue(npoi.Row.GetCell(4))) ? string.Empty : npoi.GetCellValue(npoi.Row.GetCell(4));
list.Category33Name = Tools.IsHyphen(npoi.GetCellValue(npoi.Row.GetCell(5))) ? string.Empty : npoi.GetCellValue(npoi.Row.GetCell(5));
list.Category17Code = Tools.IsHyphen(npoi.GetCellValue(npoi.Row.GetCell(6))) ? string.Empty : npoi.GetCellValue(npoi.Row.GetCell(6));
list.Category17Name = Tools.IsHyphen(npoi.GetCellValue(npoi.Row.GetCell(7))) ? string.Empty : npoi.GetCellValue(npoi.Row.GetCell(7));
list.ClassCode = Tools.IsHyphen(npoi.GetCellValue(npoi.Row.GetCell(8))) ? string.Empty : npoi.GetCellValue(npoi.Row.GetCell(8));
list.ClassName = Tools.IsHyphen(npoi.GetCellValue(npoi.Row.GetCell(9))) ? string.Empty : npoi.GetCellValue(npoi.Row.GetCell(9));
result.Add(list);
}
}
}
else
{
throw new FileNotFoundException();
}
return(result);
}