private void LerExcel(ImportacaoEntity importacao)
{
try
{
WorkBook wb = WorkBook.Load(importacao.CaminhoArquivo);
WorkSheet ws = wb.WorkSheets.First();
var table = ws.ToDataTable(true);
DataRow[] rows = table.Select();
IEnumerable <DataRow> ts = from processo in table.AsEnumerable() select processo;
foreach (DataRow row in table.Rows)
{
new RegistroBusiness().Insert(new RegistroEntity
{
Importacao = importacao,
NumeroSinistro = row["Número Sinistro Zurich"].ToString(),
Companhia = row["CIAS"].ToString(),
Processo = row["Processo"].ToString(),
Bilhete = row["Bilhete"].ToString(),
DataEmissaoVoucher = Convert.ToDateTime(row["Data de Emissão do Voucher"]),
Referencia = row["Referência"].ToString(),
DataAtendimento = Convert.ToDateTime(row["Data de Atendimento"]),
DataOcorrencia = Convert.ToDateTime(row["Data de Ocorrência"]),
Nome = row["Nome"].ToString(),
NumeroDocumento = row["Número do DOC"].ToString(),
Voucher = row["VOUCHER "].ToString(),
Cobertura = row["Cobertura Reclamada"].ToString(),
CustoOriginal = Convert.ToDecimal(row["Custo na Moeda Original"]),
TipoMoeda = row["Tipo de Moeda"].ToString(),
ValorFinal = Convert.ToDecimal(row["Valor Final (US$)"]),
Fee = Convert.ToDecimal(row["Fee"]),
TipoMoedaFee = row["Tipo de Moeda Fee"].ToString(),
ValorDolar = Convert.ToDecimal(row["Valor USD"]),
ValorReal = Convert.ToDecimal(row["Valor ND R$"]),
Dolar = Convert.ToDouble(row["Dólar"]),
NotaDebito = row["ND/NC"].ToString(),
DataEmissaoND = Convert.ToDateTime(row["Data de Emissão ND"]),
DataEnvio = Convert.ToDateTime(row["Data de envio"]),
StatusPagamento = row["Status de Pgto PARCIAL/TOTAL"].ToString()
});
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
static void FillDbTableFromSheet()
{
testDBEntities dbContext = new testDBEntities();
WorkBook workbook = WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet3");
System.Data.DataTable dataTable = sheet.ToDataTable(true);
foreach (DataRow row in dataTable.Rows)
{
Country c = new Country();
c.CountryName = row[1].ToString();
dbContext.Countries.Add(c);
}
dbContext.SaveChanges();
}
private void neural_network()
{
WorkBook workbook = WorkBook.Load("HW.csv");
WorkSheet sheet = workbook.WorkSheets.First();
DataTable dataTable = sheet.ToDataTable(true);
Random r = new Random();
for (int i = 0; i < 8; i++)
{
ini_weight[i] = r.NextDouble() * (0.5 - (-0.5)) + (-0.5);
}
for (int i = 0; i < 3; i++)
{
ini_bias[i] = r.NextDouble() * (1 - (-1)) + (-1);
}
while (true)//epoch
{
int j = 2;
double[] upd_weight = new double[8];
double[] upd_bias = new double[3];
foreach (DataRow row in dataTable.Rows)
{
// MessageBox.Show(row[0]+"");
for (int i = 0; i < 3; i++)
{
input[i] = Convert.ToDouble(row[i]);
}
target_output = Convert.ToDouble(row[3]);
feed_foward(ini_weight, ini_bias);
sheet["E" + j].Value = output[5];
// for c_error
c_error += Math.Abs(output_error);
j++;
for (int i = 0; i < 8; i++)
{
upd_weight[i] += _weight[i];
}
for (int i = 0; i < 3; i++)
{
upd_bias[i] += _bias[i];
}
}
//average biases and weight after completing one epoch
for (int i = 0; i < 8; i++)
{
upd_weight[i] = upd_weight[i] / dataTable.Rows.Count;
}
for (int i = 0; i < 3; i++)
{
upd_bias[i] = upd_bias[i] / dataTable.Rows.Count;
}
c_error = c_error / dataTable.Rows.Count;
if (c_error > t_threshold)
{
ini_weight = upd_weight;
ini_bias = upd_bias;
c++;
}
else
{
// MessageBox.Show(c_error + " " + c);
workbook.SaveAs(@"C:\Users\Pc Mart\Desktop\NewExcelFile.xlsx");
WorkBook wb = WorkBook.Load(@"C:\Users\Pc Mart\Desktop\NewExcelFile.xlsx");
WorkSheet _sheet = wb.WorkSheets.First();
_sheet.CreateChart(IronXL.Drawing.Charts.ChartType.Scatter, 2, "D", 10, "E");
dataTable = _sheet.ToDataTable(true);
dataGridView1.DataSource = dataTable;
j = 2;
var chart = chart1.ChartAreas[0];
chart.AxisX.IntervalType = System.Windows.Forms.DataVisualization.Charting.DateTimeIntervalType.Number;
chart.AxisX.LabelStyle.Format = "";
chart.AxisY.LabelStyle.Format = "";
chart.AxisX.LabelStyle.IsEndLabelVisible = true;
chart.AxisY.Minimum = 0.4;
chart.AxisY.Interval = 0.1;
chart.AxisX.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.False;
chart1.Series[0].IsVisibleInLegend = false;
chart1.Series.Add("Target Output");
chart1.Series["Target Output"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.FastLine;
chart1.Series["Target Output"].Color = Color.SeaGreen;
chart.BackColor = Color.Black;
chart1.Series.Add("Calculated Output");
chart1.Series["Calculated Output"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.FastLine;
chart1.Series["Calculated Output"].Color = Color.Red;
foreach (DataRow row in dataTable.Rows)
{
chart1.Series["Target Output"].Points.AddY(sheet["D" + j].Value);
chart1.Series["Calculated Output"].Points.AddY(sheet["E" + j].Value);
j++;
}
break;
}
}
}
private void LerExcel(string name, string caminho)
{
//XLWorkbook wb = new XLWorkbook(caminho);
//IXLWorksheet worksheet = wb.Worksheet(1);
//var row = worksheet.FirstRowUsed().RowBelow();
//foreach (var cell in worksheet.Cells())
//{
// var a = cell.Value;
// var b = cell.GetValue<string>();
//}
WorkBook wb = WorkBook.Load(caminho);
WorkSheet ws = wb.WorkSheets.First();
var table = ws.ToDataTable(true);
DataRow[] rows = table.Select();
IEnumerable <DataRow> ts = from processo in table.AsEnumerable() select processo;
foreach (DataRow dr in ts)
{
var dt = dr.Field <DateTime>(8);
}
foreach (DataRow r in rows)
{
var e = r.Field <Double>(0);
}
foreach (DataRow row in table.Rows)
{
var f = row["CIAS"];
}
foreach (var cell in ws[ws.RangeAddressAsString])
{
var a = cell.Value;
}
//using (var stream = File.Open(caminho, FileMode.Open, FileAccess.Read))
//{
// IExcelDataReader reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);
// var conf = new ExcelDataSetConfiguration
// {
// ConfigureDataTable = _ => new ExcelDataTableConfiguration
// {
// UseHeaderRow = false
// }
// };
// var dataSet = reader.AsDataSet(conf);
// var dataTable = dataSet.Tables[0];
// for (int i = 0; i < dataTable.Rows.Count; i++)
// {
// for (int j = 0; j < dataTable.Columns.Count; j++)
// {
// var b = dataTable.Rows[i][j];
// }
// }
// reader.Close();
//}
//Excel = new _Excel.Application();
//Wb = Excel.Workbooks.Open($@"{path}\{name}");
//Ws = Wb.Worksheets[1];
//var c = Ws.Cells.Value2;
}