public void AddColumn(string sheetName, string colName)
{
worksheet = workbook.Worksheet(sheetName);
if(worksheet.LastColumnUsed()!=null)
worksheet.LastColumnUsed().ColumnRight().Cell(1).Value = colName;
else
worksheet.Cell("A1").Value = colName;
Save();
}
public void AddColumn(string sheetName, string colName)
{
worksheet = workbook.Worksheet(sheetName);
if (worksheet.LastColumnUsed() != null)
{
worksheet.LastColumnUsed().ColumnRight().Cell(1).Value = colName;
}
else
{
worksheet.Cell("A1").Value = colName;
}
Save();
}
private List <ColumnDefine> GetColumnDefines(IXLWorksheet worksheet)
{
var lastColumnLetter = worksheet.LastColumnUsed().Cell(1).Address.ColumnLetter;
var columns = new List <ColumnDefine>();
foreach (var header in worksheet.Row(1).Cells())
{
var value = header?.Value?.ToString()?.Trim();
// 标题内容为空认为表格到底
if (string.IsNullOrWhiteSpace(value))
{
break;
}
columns.Add(new ColumnDefine
{
Index = header.Address.ColumnNumber,
Name = value,
DataType = value.Contains("时间") || value.Contains("日期") ? "DateTime" : "String",
IsPrimary = false
});
if (header.Address.ColumnLetter == lastColumnLetter)
{
break;
}
}
return(columns);
}
public ExcelReader(string filename)
{
workbook = new XLWorkbook(File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
worksheet = workbook.Worksheets.First();
totalRows = worksheet.LastRowUsed().RowNumber();
totalColumns = worksheet.LastColumnUsed().ColumnNumber();
}
/// <summary>
/// Adjust column with to accommodate for the drop down buttons
/// </summary>
/// <param name="worksheet"></param>
public static void AddSpaceForDropDownMarker(this IXLWorksheet worksheet)
{
var lastCol = worksheet.LastColumnUsed().ColumnNumber();
for (var colIdx = 1; colIdx <= lastCol; colIdx++)
{
worksheet.Column(colIdx).Width += 2; // Add space for the drop down
}
}
/// <summary>
/// Excelのデータをセルごとに読み込んで2次元配列に代入する
/// </summary>
/// <param name="sheetNumber">シート番号(1から)</param>
/// <param name="workBook">XLWorkbook変数</param>
/// <returns>使用しているExcelのセルのデータをstringの2次元配列で返す</returns>
public string[,] ExtractionExcelData(int sheetNumber, XLWorkbook workBook)
{
IXLWorksheet workSheet = workBook.Worksheet(sheetNumber);
(int column, int row)xlCellAddress;
xlCellAddress.column = workSheet.LastColumnUsed().ColumnNumber();
xlCellAddress.row = workSheet.LastRowUsed().RowNumber();
string[,] xlStrDataArray = new string[xlCellAddress.column, xlCellAddress.row];
return(xlStrDataArray);
}
public static double getTotalWidth(IXLWorksheet ws, int startCol)
{
var totalWidth = 0.0;
foreach (var col in ws.Columns(startCol, ws.LastColumnUsed().ColumnNumber()))
{
totalWidth += col.Width * 5.69;
}
return totalWidth;
}
private void AdicionarEstilo(IXLWorksheet worksheet, DataTable tabelaDados)
{
int ultimaColunaUsada = worksheet.LastColumnUsed().ColumnNumber();
int ultimaLinhaUsada = worksheet.LastRowUsed().RowNumber();
AdicionarEstiloCabecalho(worksheet, ultimaColunaUsada);
AdicionarEstiloCorpo(worksheet, tabelaDados, ultimaColunaUsada, ultimaLinhaUsada);
worksheet.ShowGridLines = false;
worksheet.ColumnsUsed().AdjustToContents();
worksheet.RowsUsed().AdjustToContents();
}
public void Preview()
{
const int columnsMaxCount = 50;
const int rowsMaxCount = 50;
var firstColumn = _sheet.FirstColumn();
var lastColumn = _sheet.LastColumnUsed().ColumnNumber() < columnsMaxCount?
_sheet.LastColumnUsed() :
_sheet.Column(columnsMaxCount);
var firstRow = _sheet.FirstRow();
var lastRow = _sheet.LastRowUsed().RowNumber() < rowsMaxCount?
_sheet.LastRowUsed() :
_sheet.Row(rowsMaxCount);
var previewData = new string[lastColumn.ColumnNumber(), lastRow.RowNumber()];
for (var row = firstRow.RowNumber(); row <= lastRow.RowNumber(); row++)
{
for (int col = firstColumn.ColumnNumber(); col <= lastColumn.ColumnNumber(); col++)
{
previewData[col - 1, row - 1] = _sheet.Row(row).Cell(col).GetString();
Console.Write(previewData[col - 1, row - 1] + " \t");
}
Console.WriteLine();
}
Console.WriteLine();
var rowsUsed = _sheet.LastRowUsed().RowNumber();
int rowsRoundedCount = rowsUsed < 100 ?
lastRow.RowNumber() :
(int)Math.Round((double)(rowsUsed - rowsUsed / 20) / 10) * 10;
Console.WriteLine("\n=== SUMMARY ===\n");
Console.WriteLine($"Примерное кол-во строк: {rowsRoundedCount}");
}
/// <summary>
/// Excelのデータをセルごとに読み込んでジャグ配列に代入する
/// </summary>
/// <param name="sheetNumber">シート番号(1から)</param>
/// <param name="workBook">XLWorkbook変数</param>
/// <returns>使用しているExcelのセルのデータをstringのジャグ配列で返す</returns>
public string[][] ExtractionExcelDataJagged(int sheetNumber, XLWorkbook workBook)
{
IXLWorksheet workSheet = workBook.Worksheet(sheetNumber);
(int column, int row)xlCellAddress;
xlCellAddress.column = workSheet.LastColumnUsed().ColumnNumber();
xlCellAddress.row = workSheet.LastRowUsed().RowNumber();
// ジャグ配列にExcelのセルのデータを入れる
string[][] xlStrDataJaggedArray = new string[xlCellAddress.row][];
xlStrDataJaggedArray = Enumerable.Range(0, xlCellAddress.column)
.Select(row => (new string[xlCellAddress.column]).Select(str => workSheet.Cell(1, 1).Value.ToString()).ToArray()).ToArray();
string[][] xlStrDataArray2 = new string[xlCellAddress.row][];
return(xlStrDataJaggedArray);
}
PopulationByAges GetAllDataFromWorksheet(IXLWorksheet worksheet, Dictionary <string, HeaderItem> headers, string primaryKey)
{
var headerCells = worksheet.Cells();
foreach (var header in headers)
{
header.Value.Address = GetSpecificHeaderColumn(headerCells, header.Key);
}
var lastRowNumber = worksheet.LastRowUsed().Cell(headers[primaryKey].Address.ColumnNumber).Address.RowNumber;
var lastColNumber = worksheet.LastColumnUsed().Cell(headers[primaryKey].Address.RowNumber).Address.ColumnNumber;
var bodyCells = worksheet.Range($"A1:{ToColumnName(lastColNumber)}{lastRowNumber}").Cells();
var primaryRows = bodyCells.Where(i => i.Address.ColumnNumber == headers[primaryKey].Address.ColumnNumber).Select(i => i);
StringBuilder sb = new StringBuilder();
sb.Append("{\"contents\":[");
foreach (var primaryRow in primaryRows)
{
Regex regex = new Regex("^[0-9]");
if (regex.IsMatch(primaryRow.Value.ToString().Trim()) == false)
{
continue;
}
sb.Append("{");
var excelRow = primaryRow.Address.RowNumber;
foreach (var header in headers)
{
var valueObject = worksheet.Cells().Where(i => i.Address.RowNumber == excelRow &&
i.Address.ColumnNumber == headers[header.Key].Address.ColumnNumber)
.Select(i => i.Value)
.FirstOrDefault();
sb.Append($"\"{header.Key}\":\"{valueObject.ToString().Trim()}\",");
}
sb.ToString().Remove(sb.Length - 1, 1);
sb.Append("},");
}
sb.ToString().Remove(sb.Length - 1, 1);
sb.Append("]}");
return(JsonConvert.DeserializeObject <PopulationByAges>(sb.ToString()));
}
private List <int> DateList(IXLWorksheet worksheet)
{
List <int> dateList = new List <int>();
var lastColumnNumber = worksheet.LastColumnUsed().ColumnNumber();
//date list
List <IXLRangeColumn> dateRangeList = worksheet.Range(13, 9, 13, lastColumnNumber).AsRange().Columns().ToList();
foreach (var cell in dateRangeList)
{
var dateTime = cell.FirstCell().Value is DateTime ? (DateTime)cell.FirstCell().Value : new DateTime();
var date = dateTime.Day;
dateList.Add(date);
}
return(dateList);
}
private void WriteLocaleValues(IXLRow currentRow, ResourceItem resource)
{
foreach (var value in resource.Values)
{
if (value.Key == DefaultCultureColumn)
{
continue;
}
var columnHeader = _sheet.FirstRow().Cells().FirstOrDefault(x => x.Value.ToString() == value.Key);
if (columnHeader == null)
{
_sheet.FirstRow().LastCellUsed().CellRight().SetValue(value.Key);
columnHeader = _sheet.LastColumnUsed().FirstCell();
}
currentRow.Cell(columnHeader.WorksheetColumn().ColumnNumber()).SetDataType(XLCellValues.Text);
currentRow.Cell(columnHeader.WorksheetColumn().ColumnNumber()).SetValue(value.Value);
}
}
public List <SecondParamModel> ManupilateSecondPart(IXLWorksheet worksheet)
{
System.Collections.Generic.List <SecondParamModel> secondParamModels = new List <SecondParamModel>();
var lastRowNumber = worksheet.LastRowUsed().RowNumber();
var lastColumnNumber = worksheet.LastColumnUsed().ColumnNumber();
var secontRangeAdditional = worksheet.Range(_startRow, 1, lastRowNumber, 1);
var rowList = secontRangeAdditional.AsTable().DataRange.Rows().ToList();
var itemLastRow = rowList.Find(o =>
{
var value = o.FirstCell().Value;
return(value != null && value is string && (string)value == "Clock hours");
});
if (itemLastRow != null)
{
int lastItemRowNumber = itemLastRow.RowNumber() - 3;
var secondRange = worksheet.Range(_startRow, 1, lastItemRowNumber - 1, lastColumnNumber);
var secondRowList = secondRange.AsRange().Rows().ToList();
List <int> worksheetDateList = DateList(worksheet);
int i = 0;
foreach (IXLRangeRow row in secondRowList)
{
if (i % 2 == 0)
{
SecondParamModel paramModel = new SecondParamModel();
var cellList = row.Cells().ToList();
paramModel.ItemCode = cellList[0].Value.ToString();
paramModel.Category = cellList[1].Value.ToString();
paramModel.Style = cellList[2].Value.ToString();
paramModel.Item = cellList[3].Value.ToString();
//TODO : convert int string
//order
string oqty = cellList[4].Value.ToString();
if (oqty != "" && oqty != "TBA")
{
double d = Double.Parse(oqty);
d = Math.Round(d, 0);
paramModel.OrderQty = (d.ToString(CultureInfo.InvariantCulture).Split('.')[0]);
}
else
{
paramModel.OrderQty = (oqty);
}
//plan
string pqty = cellList[5].Value.ToString();
if (pqty != "" && pqty != "TBA")
{
double d = Double.Parse(pqty);
d = Math.Round(d, 0);
paramModel.PlanQty = (d.ToString(CultureInfo.InvariantCulture).Split('.')[0]);
}
else
{
paramModel.PlanQty = (pqty);
}
paramModel.SMV = cellList[6].Value.ToString();
paramModel.SMO = cellList[7].Value.ToString();
Dictionary <int, string> dateDictionary = new Dictionary <int, string>();
for (int j = 8; j < lastColumnNumber; j++)
{
IXLCell cell = cellList[j];
var dateValue = cell.Value.ToString();
if (dateValue != "")
{
double d = Double.Parse(dateValue);
d = Math.Round(d, 0);
dateDictionary.Add(worksheetDateList[j - 8], d.ToString(CultureInfo.InvariantCulture).Split('.')[0]);
}
else
{
dateDictionary.Add(worksheetDateList[j - 8], dateValue);
}
}
paramModel.DateList = dateDictionary;
secondParamModels.Add(paramModel);
}
i++;
}
}
return(secondParamModels);
}
public IXLRange CopyTo(IXLRange range)
{
// LastCellUsed may produce the wrong result, see https://github.com/ClosedXML/ClosedXML/issues/339
var lastCell = _sheet.Cell(
_sheet.LastRowUsed(true)?.RowNumber() ?? 1,
_sheet.LastColumnUsed(true)?.ColumnNumber() ?? 1);
var tempRng = _sheet.Range(_sheet.Cell(1, 1), lastCell);
var rowDiff = tempRng.RowCount() - range.RowCount();
if (rowDiff > 0)
{
range.InsertRowsBelow(rowDiff, true);
}
else if (rowDiff < 0)
{
range.Worksheet.Range(
range.LastRow().RowNumber() + rowDiff + 1,
range.FirstColumn().ColumnNumber(),
range.LastRow().RowNumber(),
range.LastColumn().ColumnNumber())
.Delete(XLShiftDeletedCells.ShiftCellsUp);
}
range.Worksheet.ConditionalFormats.Remove(c => c.Range.Intersects(range));
var columnDiff = tempRng.ColumnCount() - range.ColumnCount();
if (columnDiff > 0)
{
range.InsertColumnsAfter(columnDiff, true);
}
else if (columnDiff < 0)
{
range.Worksheet.Range(
range.FirstRow().RowNumber(),
range.LastColumn().ColumnNumber() + columnDiff + 1,
range.LastRow().RowNumber(),
range.LastColumn().ColumnNumber())
.Delete(XLShiftDeletedCells.ShiftCellsLeft);
}
tempRng.CopyTo(range.FirstCell());
var tgtSheet = range.Worksheet;
var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber;
using (var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber))
foreach (var row in srcRows)
{
var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1);
xlRow.OutlineLevel = row.OutlineLevel;
if (row.IsHidden)
{
xlRow.Collapse();
}
else
{
xlRow.Expand();
}
}
return(range);
}
/// -----------------------------------------------------------------------------
/// <summary>
/// DataTableをもとにxlsxファイルを作成（テンプレート無し）</summary>
/// <param name="dt">
/// 出力対象DataTable</param>
/// <param name="functionName">
/// 機能名</param>
/// <param name="startRow">
/// 出力開始行</param>
/// <param name="startCol">
/// 出力開始列</param>
/// <param name="header">
/// 出力するヘッダを格納した配列</param>
/// -----------------------------------------------------------------------------
public void DtToXls(DataTable dt, string functionName, string outXlsFile, int startRow, int startCol, string[] header)
{
//string xlspath = System.Configuration.ConfigurationManager.AppSettings["workpath"];
////string xlspath = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal);
XLWorkbook currentWorkbook = new XLWorkbook();
IXLWorksheet worksheet = currentWorkbook.Worksheets.Add("data");
//string DTime = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
//string outXlsFile = xlspath + functionName + "_" + DTime + ".xlsx";
try
{
int cntrow = 0; // 行カウント
int cntcol = 0; // 列カウント
int maxcol = dt.Columns.Count;
// DataTable を一行ずつエクセルに出力
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
if (cntcol < maxcol)
{
string colName = col.ColumnName.ToUpper();
var value = row[cntcol];
// 値の型取得
string valueType = value.GetType().Name.ToUpper();
#region カラム名を見て、フォーマット指定してセルに出力
// 金額関係
if (colName.IndexOf("KINGAKU") > -1)
{
// 値をdecimalにキャスト（Excel出力時に文字列扱いにさせないため）
decimal dValue = decimal.Parse(value.ToString());
// セルに値出力
worksheet.Cell(cntrow + startRow, cntcol + startCol).SetValue(dValue)
.Style.NumberFormat.SetFormat("#,##0");
}
// 単価・原価関係
else if (colName.IndexOf("TANKA") > -1 || colName.IndexOf("GENKA") > -1)
{
// 値をdecimalにキャスト（Excel出力時に文字列扱いにさせないため）
decimal dValue = decimal.Parse(value.ToString());
// セルに値出力
worksheet.Cell(cntrow + startRow, cntcol + startCol).SetValue(dValue)
.Style.NumberFormat.SetFormat("#,##0.00");
}
// 数量関係
else if (colName.IndexOf("SURYO") > -1)
{
// 値をdecimalにキャスト（Excel出力時に文字列扱いにさせないため）
decimal dValue = decimal.Parse(value.ToString());
// セルに値出力
worksheet.Cell(cntrow + startRow, cntcol + startCol).SetValue(dValue)
.Style.NumberFormat.SetFormat("0.00");
}
// 日付関係の場合
else if (valueType.Equals("DATETIME"))
{
DateTime dtValue = DateTime.Parse(value.ToString());
// セルに値出力
worksheet.Cell(cntrow + startRow, cntcol + startCol).SetValue(dtValue)
.Style.NumberFormat.SetFormat("yyyy/mm/dd");
}
// 金額・単価・数量関係以外のdecimalの場合
else if (valueType.Equals("DECIMAL"))
{
// 値をdecimalにキャスト（Excel出力時に文字列扱いにさせないため）
decimal dValue = decimal.Parse(value.ToString());
// セルに値出力
worksheet.Cell(cntrow + startRow, cntcol + startCol).SetValue(dValue)
.Style.NumberFormat.SetFormat("0.00");
}
else
{
string strValue = value.ToString().TrimEnd();
// セルに値出力
worksheet.Cell(cntrow + startRow, cntcol + startCol).SetValue(strValue)
.Style.NumberFormat.SetFormat("@");
}
#endregion
#region 型を見て、文字の配列を指定
// 値の型がSTRINGの場合
if (valueType.Equals("STRING"))
{
IXLCell targetcell = worksheet.Cell(cntrow + startRow, cntcol + startCol);
// 左揃え
targetcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
// 値の型がINTまたはDECIMALの場合
else if (valueType.IndexOf("INT") > -1 || valueType.Equals("DECIMAL"))
{
IXLCell targetcell = worksheet.Cell(cntrow + startRow, cntcol + startCol);
// 右揃え
targetcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
// 値の型がDATETIMEの場合
else if (valueType.IndexOf("DATETIME") > -1)
{
IXLCell targetcell = worksheet.Cell(cntrow + startRow, cntcol + startCol);
// 中央揃え
targetcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
#endregion
// 列カウントアップ
cntcol++;
}
}
// 列カウントリセット
cntcol = 0;
// 行カウントアップ
cntrow++;
}
// ヘッダ挿入
worksheet.Cell("A2").InsertData(new[] { header });
// ヘッダ行の色を変える
worksheet.Range(2, 1, 2, worksheet.LastColumnUsed().ColumnNumber()).Style.Fill.BackgroundColor = XLColor.LightGray;
// 値が存在するセル範囲を取得
var usedRange = worksheet.RangeUsed();
// 使用している範囲のフォントサイズ指定
usedRange.Style.Font.FontSize = 11;
// 使用している範囲のフォントの種類を指定
usedRange.Style.Font.FontName = "ＭＳ Ｐ明朝";
// 全部の列の幅を自動調整
worksheet.Columns().AdjustToContents();
// 使用しているセルの範囲に罫線を引く
usedRange.Style.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetBottomBorder(XLBorderStyleValues.Thin)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
// RangeUsedメソッドを使用してセルの範囲を取得するとタイトル部分まで取得してしまうため
// タイトルのフォーマットは別で指定
#region タイトル行の設定
// タイトル挿入
worksheet.Cell("A1").Value = functionName;
// タイトルのみ大きくする
worksheet.Cell("A1").Style.Font.FontSize = 20;
// タイトルのフォントの種類を指定
worksheet.Cell("A1").Style.Font.FontName = "ＭＳ Ｐ明朝";
// タイトルセルを結合
worksheet.Range(1, 1, 1, worksheet.LastColumnUsed().ColumnNumber()).Merge();
#endregion
// ヘッダとタイトルを中央揃え
worksheet.Range(1, 1, 2, worksheet.LastColumnUsed().ColumnNumber()).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// 保存
currentWorkbook.SaveAs(outXlsFile);
}
catch (Exception ex)
{
throw ex;
}
finally
{
currentWorkbook.Dispose();
}
}
private static bool SplitKD(IXLWorksheet worksheet, string title, int column)
{
// row to scan for KD
const int row = 6;
try
{
while (!worksheet.Cell(row, column).IsEmpty())
{
// scan for first KD derivative in TRIM LEVEL row
if (worksheet.Cell(row, column).GetString().EndsWith("KD"))
{
// remove existing column grouping - commented out as grouping no longer applied as you can't un-group on a protected worksheet
// worksheet.Columns(8, worksheet.LastColumnUsed().ColumnNumber()).Ungroup();
// add KD SPEC GROUP heading
worksheet.Cell(1, column).Value = title.ToUpper() + " KD";
// insert and size new divider column before KD derivatives
worksheet.Column(column).InsertColumnsBefore(1);
worksheet.Column(column).Width = 8;
// group non-KD derivatives - commented out as you can't un-group on a protected worksheet
// worksheet.Columns(8, column - 1).Group();
// merge non-KD SPEC GROUP heading
worksheet.Range(1, 8, 1, column - 1).Merge();
// group KD derivatives - commented out as you can't un-group on a protected worksheet
// worksheet.Columns(column + 1, worksheet.LastColumnUsed().ColumnNumber()).Group();
// merge KD SPEC GROUP heading
worksheet.Range(1, column + 1, 1, worksheet.LastColumnUsed().ColumnNumber()).Merge();
// add vertical KD title
worksheet.Cell(1, column).Value = title.ToUpper() + " KD";
// merge and format vertical divider heading
worksheet.Range(1, column, 9, column).Merge();
worksheet.Range(1, column, worksheet.LastRowUsed().RowNumber(), column).Style
.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom)
.Alignment.SetTextRotation(90);
// do for first KD derivative then break out
return true;
}
column = column + 1;
}
return false;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
public bool readExcelFile(IXLWorksheet workSheet)
{
try
{
//float countOfTransaction = 0;
//foreach (IXLRow row in workSheet.Rows())
//{
// foreach (IXLCell cell in row.Cells())
// {
// string cellValue = cell.Value.ToString();
// string ptName = workSheet.Row(1).ToString();
// }
//}
const int FIRST_ROW = 1;
const int FIRST_COLUMN = 1;
//const int VOLUME_OF_TANSACTION_COLUMN = 12;
//const string VOLUME_OF_TANSACTION_COLUMN_NAME = "حجم معامله";
//const int COUNT_OF_TANSACTION_COLUMN = 18;
//const string COUNT_OF_TANSACTION_COLUMN_NAME = "میانگین حجم معاملات";
int rowCount = workSheet.LastRowUsed().RowNumber();
int columnCount = workSheet.LastColumnUsed().ColumnNumber();
//float countOfTransaction = 0;
for (int rowNumber = FIRST_ROW; rowNumber <= rowCount; rowNumber++)
{
IXLRow row = workSheet.Row(rowNumber);
for (int columnNumber = FIRST_COLUMN; columnNumber <= columnCount; columnNumber++)
{
IXLCell cell = row.Cell(columnNumber);
string cellValue = cell.Value.ToString();
if (rowNumber == FIRST_ROW && columnNumber != FIRST_COLUMN)
{
string itemName = cellValue.Replace("ي", "ی").Replace("ك", "ک");
Item Item = _dbContext.Items.Where(x => x.Name == itemName).FirstOrDefault();
if (Item == null)
{
Item = new Item();
Item.Name = itemName;
_dbContext.Items.Add(Item);
_dbContext.SaveChanges();
}
}
else if (rowNumber != FIRST_ROW)
{
if (columnNumber == FIRST_COLUMN)
{
string symbolName = cellValue.Replace("ي", "ی").Replace("ك", "ک");
Symbol symbol = _dbContext.Symbols.Where(x => x.Name == symbolName).FirstOrDefault();
if (symbol == null)
{
symbol = new Symbol();
symbol.Name = symbolName;
_dbContext.Symbols.Add(symbol);
_dbContext.SaveChanges();
}
}
else
{
DateTime spDate = DateTime.ParseExact("2019-04-30 00:00:00.000", "yyyy-MM-dd HH:mm:ss.fff",
CultureInfo.InvariantCulture);
var currentDate = new SqlParameter("@pCurrentDate", spDate);
var value = new SqlParameter("@pValue", cellValue.ToString() == "" || cellValue.ToString() == "NaN" ? 0 : (float)(Math.Round(float.Parse(cellValue.ToString()), 2)));
var symbolName = new SqlParameter("@pSymbolName", row.Cell(FIRST_COLUMN).Value.ToString().Replace("ي", "ی").Replace("ك", "ک"));
var itemName = new SqlParameter("@pItemName", workSheet.Row(FIRST_ROW).Cell(columnNumber).Value.ToString().Replace("ي", "ی").Replace("ك", "ک"));
_dbContext.Database.ExecuteSqlCommand("EXEC UD__InsertParamValue__ @pCurrentDate , @pValue , @pSymbolName , @pItemName", currentDate, value, symbolName, itemName);
}
}
}
}
return(true);
}
catch (Exception ex)
{
Debug.WriteLine("File", ex.Message.ToString());
return(false);
}
}
public override int CountColumns() => worksheet.LastColumnUsed().ColumnNumber();
public int getColumnCount()
{
return(ws.LastColumnUsed().ColumnNumber());
}
public async Task <bool> ExportToExcelFileAsync(DataToExport dataToExport, string filePath)
{
bool success = false;
await Task.Run(() =>
{
try
{
using var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet();
string canalType = dataToExport.CanalCharacteristics.canalType;
IList <Parameter> canalGeometryParameters = dataToExport.CanalCharacteristics.Item2;
ws.Cell("A2").SetValue("Тип канала");
ws.Cell("A3").SetValue(canalType);
for (int i = 0; i < canalGeometryParameters.Count; i++)
{
ws.Cell(2, i + 4).SetValue(canalGeometryParameters[i].Name);
ws.Cell(3, i + 4).SetValue(canalGeometryParameters[i].Value + $" {canalGeometryParameters[i].MeasureUnit}");
}
string materialType = dataToExport.MaterialCharacteristics.materialType;
IList <Parameter> materialPropertyParameters = dataToExport.MaterialCharacteristics.Item2;
ws.Cell("A5").SetValue("Тип материала");
ws.Cell("A6").SetValue(materialType);
for (int i = 0; i < materialPropertyParameters.Count; i++)
{
ws.Cell(5, i + 4).SetValue(materialPropertyParameters[i].Name);
ws.Cell(6, i + 4).SetValue(materialPropertyParameters[i].Value + $" {materialPropertyParameters[i].MeasureUnit}");
}
IXLColumn lastMergedColumn = ws.LastColumnUsed();
ws.Range(ws.Cell(1, 1), ws.LastColumnUsed().Cell(1)).Merge().SetValue("Входные параметры");
IXLCell xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> variableParameters = dataToExport.VariableParameters;
for (int i = 0; i < variableParameters.Count; i++)
{
xLCell.SetValue(variableParameters[i].Name);
xLCell = xLCell.CellBelow().SetValue(variableParameters[i].Value + $" {variableParameters[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Варьируемые параметры");
lastMergedColumn = ws.LastColumnUsed();
xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> empiricalParametersOfMathModel = dataToExport.EmpiricalParametersOfMathModel;
for (int i = 0; i < empiricalParametersOfMathModel.Count; i++)
{
xLCell.SetValue(empiricalParametersOfMathModel[i].Name);
xLCell = xLCell.CellBelow().SetValue(empiricalParametersOfMathModel[i].Value + $" {empiricalParametersOfMathModel[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Эмпирические коэффициенты математической модели");
IDictionary <string, IList <Parameter> > discreteOutputParameters = dataToExport.DiscreteOutputParameters;
xLCell = xLCell.CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow()
.WorksheetRow().Cell(1);
foreach (KeyValuePair <string, IList <Parameter> > keyValuePair in discreteOutputParameters)
{
IXLCell firstCell = xLCell;
foreach (Parameter parameter in keyValuePair.Value)
{
xLCell.SetValue(parameter.Name);
xLCell = xLCell.CellBelow().SetValue($"{parameter.Value} {parameter.MeasureUnit}");
xLCell = xLCell.CellAbove().CellRight();
}
if (keyValuePair.Value.Count <= 0)
{
continue;
}
if (keyValuePair.Value.Count == 1)
{
xLCell = xLCell.CellRight();
continue;
}
ws.Range(firstCell.CellAbove(), ws.LastRowUsed().LastCellUsed().CellAbove().CellAbove()).
Merge().SetValue(keyValuePair.Key);
xLCell = xLCell.CellRight();
}
xLCell = xLCell.CellLeft().CellLeft().CellAbove().CellAbove();
ws.Range(xLCell, xLCell.WorksheetRow().Cell(1)).Merge().SetValue("Результаты");
xLCell = ws.LastRowUsed().Cell(1).CellBelow().CellBelow();
ws.Range(xLCell, xLCell.CellRight().CellRight().CellRight()).Merge().SetValue("Таблица результатов");
IList <(Parameter coordinate, Parameter temperature, Parameter viscosity)> resultsTable = dataToExport.ContiniousResults;
xLCell.CellBelow().SetValue($"{resultsTable[0].coordinate.Name}, {resultsTable[0].coordinate.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].temperature.Name}, {resultsTable[0].temperature.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].viscosity.Name}, {resultsTable[0].viscosity.MeasureUnit}");
xLCell = xLCell.CellBelow().CellBelow();
NumberFormatInfo nfi = new NumberFormatInfo
{
NumberDecimalSeparator = "."
};
foreach (var(coordinate, temperature, viscosity) in resultsTable)
{
xLCell.SetValue(((double)coordinate.Value)
.ToString($"F{dataToExport.CoordinatePrecision}", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)temperature.Value).ToString($"F2", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)viscosity.Value).ToString("F2", nfi))
.SetDataType(XLDataType.Number);
xLCell = xLCell.CellBelow();
}
//ws.RowsUsed().AdjustToContents();
//ws.ColumnsUsed().AdjustToContents();
//using var stream = new MemoryStream();
//dataToExport.TemperaturePlot.Save(stream, ImageFormat.Png);
//ws.AddPicture(stream).MoveTo(ws.Cell("H8"))
// .WithSize(dataToExport.TemperaturePlot.Width, dataToExport.TemperaturePlot.Height);
//using var stream1 = new MemoryStream();
//dataToExport.ViscosityPlot.Save(stream1, ImageFormat.Png);
//ws.AddPicture(stream1).MoveTo(ws.Cell("O8"))
// .WithSize(dataToExport.ViscosityPlot.Width, dataToExport.ViscosityPlot.Height);
wb.SaveAs(filePath);
success = true;
}
catch { success = false; }
});
return(success);
}
/// <summary> /// Number of last user column /// </summary> /// <param name="sheet"></param> /// <returns></returns> public static int ColumnUsedCount(this IXLWorksheet sheet) => sheet.LastColumnUsed().ColumnNumber();