public void Format(IXLWorksheet worksheet, ScenarioOutline scenarioOutline, ref int row)
{
int originalRow = row;
worksheet.Cell(row++, "B").Value = scenarioOutline.Name;
worksheet.Cell(row++, "C").Value = scenarioOutline.Description;
var results = this.testResults.GetScenarioOutlineResult(scenarioOutline);
if (this.configuration.HasTestResults && (results != TestResult.Inconclusive))
{
worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(results == TestResult.Passed
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
foreach (Step step in scenarioOutline.Steps)
{
this.excelStepFormatter.Format(worksheet, step, ref row);
}
row++;
foreach (var example in scenarioOutline.Examples)
{
worksheet.Cell(row++, "B").Value = "Examples";
worksheet.Cell(row, "C").Value = example.Description;
this.excelTableFormatter.Format(worksheet, example.TableArgument, ref row);
}
}
//var wb = new XLWorkbook(northwinddataXlsx);
public ExcelHelper(string filePath)
{
this.filePath = filePath;
workbook = new XLWorkbook(filePath);
if(workbook.Worksheets.Count>0)
worksheet = workbook.Worksheet(1);
}
/// <summary>
/// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
/// </summary>
/// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
/// <param name="configuration">The configuration.</param>
public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
{
workbook = worksheet.Workbook;
this.worksheet = worksheet;
this.configuration = configuration;
FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
}
private void ProcessWorksheet(IXLWorksheet xlWorksheet)
{
if (!_uniqueIdMapping.Any())
_uniqueIdMapping =
_pcfService.GetForChurch(this._churchId)
.Select(i => new { i.UniqueId, i.Id })
.ToDictionary(t => t.UniqueId, t => t.Id);
var rows = xlWorksheet.RowsUsed();
foreach (var row in rows)
{
var pcfUniqueId = row.FirstCellUsed().GetString();
var pcfId =
_uniqueIdMapping.FirstOrDefault(
i => i.Key.Equals(pcfUniqueId, StringComparison.InvariantCultureIgnoreCase)).Value;
if (pcfId != 0)
{
var m = new StagedCells()
{
Name = row.FirstCellUsed().CellRight().GetString(),
UniqueId = UniqueIdGenerator.GenerateUniqueIdForCell(name: row.FirstCellUsed().GetString()),
PCFId = pcfId,
ChurchId = this._churchId
};
_cellService.Insert(m);
}
}
}
private void MakeHeaders(IXLWorksheet sheet, TestSuite suite)
{
int column = 1;
for (; column <= deep; column++)
{
string header = string.Empty;
for (int i = 1; i < column; i++)
header += "Sub-";
sheet.Cell(row, column).Value = header + "Category";
sheet.Column(column).Width = 20.86;
}
sheet.Cell(row, deep + 1).Value = "Name";
sheet.Column(deep + 1).Width = 30;
sheet.Cell(row, deep + 2).Value = "Id";
sheet.Column(deep + 2).Width = 13;
sheet.Cell(row, deep + 3).Value = "Summary";
sheet.Column(deep + 3).Width = 42;
sheet.Cell(row, deep + 4).Value = "Preconditions";
sheet.Column(deep + 4).Width = 56.43;
sheet.Cell(row, deep + 5).Value = "Actions";
sheet.Column(deep + 5).Width = 100;
sheet.Cell(row, deep + 6).Value = "Expected Results";
sheet.Column(deep + 6).Width = 100;
row++;
}
public static void AppendOmnitureStandardPageView(IXLWorksheet omniture)
{
var values = new string[] {
"t-1",
"",
"DUNLOP",
"HOMEPAGE",
"HOMEPAGE",
"INDEX",
"index",
"",
"",
"DUNLOP | HOMEPAGE | HOMEPAGE | INDEX",
"",
"HOMEPAGE",
"HOMEPAGE",
"HOMEPAGE | HOMEPAGE",
"INDEX",
"HOMEPAGE | INDEX",
"HOMEPAGE | HOMEPAGE | INDEX",
"INDEX",
"HOMEPAGE | INDEX",
"HOMEPAGE | INDEX | INDEX",
"HOMEPAGE | HOMEPAGE | HOMEPAGE | INDEX"
};
}
public void Format(IXLWorksheet worksheet, Table table, ref int row)
{
int startRow = row;
int headerColumn = TableStartColumn;
foreach (string cell in table.HeaderRow)
{
worksheet.Cell(row, headerColumn).Style.Font.SetBold();
worksheet.Cell(row, headerColumn).Style.Font.SetItalic();
worksheet.Cell(row, headerColumn).Style.Fill.SetBackgroundColor(XLColor.AliceBlue);
worksheet.Cell(row, headerColumn++).Value = cell;
}
row++;
foreach (TableRow dataRow in table.DataRows)
{
int dataColumn = TableStartColumn;
foreach (string cell in dataRow)
{
worksheet.Cell(row, dataColumn++).Value = cell;
}
row++;
}
int lastRow = row - 1;
int lastColumn = headerColumn - 1;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.TopBorder =
XLBorderStyleValues.Thin;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.LeftBorder =
XLBorderStyleValues.Thin;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.BottomBorder =
XLBorderStyleValues.Thin;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.RightBorder =
XLBorderStyleValues.Thin;
}
public void Format(IXLWorksheet worksheet, Feature feature)
{
worksheet.Cell("A1").Style.Font.SetBold();
worksheet.Cell("A1").Value = feature.Name;
worksheet.Cell("B2").Value = feature.Description;
worksheet.Cell("B2").Style.Alignment.WrapText = false;
var results = testResults.GetFeatureResult(feature);
if (configuration.HasTestResults && results.WasExecuted)
{
worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results.WasSuccessful
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
int row = 4;
foreach (IFeatureElement featureElement in feature.FeatureElements)
{
var scenario = featureElement as Scenario;
if (scenario != null)
{
excelScenarioFormatter.Format(worksheet, scenario, ref row);
}
var scenarioOutline = featureElement as ScenarioOutline;
if (scenarioOutline != null)
{
excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
}
row++;
}
}
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 Format(IXLWorksheet worksheet, string documentString, ref int row)
{
string[] documentStringLines = documentString.Split(new[] {"\n", "\r"},
StringSplitOptions.RemoveEmptyEntries);
foreach (string line in documentStringLines)
{
worksheet.Cell(row++, 4).Value = line;
}
}
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;
}
public static double getTotalHeight(IXLWorksheet ws, int startRow)
{
var totalHeight = 0.0;
foreach (var row in ws.Rows(startRow, ws.LastRowUsed().RowNumber()))
{
totalHeight += row.Height;
}
return totalHeight;
}
public void Format(IXLWorksheet worksheet, Feature feature)
{
worksheet.Cell("A1").Style.Font.SetBold();
worksheet.Cell("A1").Value = feature.Name;
if (feature.Description.Length <= short.MaxValue)
{
worksheet.Cell("B2").Value = feature.Description;
}
else
{
var description = feature.Description.Substring(0, short.MaxValue);
Log.Warn("The description of feature {0} was truncated because of cell size limitations in Excel.", feature.Name);
worksheet.Cell("B2").Value = description;
}
worksheet.Cell("B2").Style.Alignment.WrapText = false;
var results = this.testResults.GetFeatureResult(feature);
if (this.configuration.HasTestResults && results != TestResult.Inconclusive)
{
worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results == TestResult.Passed
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
var featureElementsToRender = new List<IFeatureElement>();
if (feature.Background != null)
{
featureElementsToRender.Add(feature.Background);
}
featureElementsToRender.AddRange(feature.FeatureElements);
var row = 4;
foreach (var featureElement in featureElementsToRender)
{
var scenario = featureElement as Scenario;
if (scenario != null)
{
this.excelScenarioFormatter.Format(worksheet, scenario, ref row);
}
var scenarioOutline = featureElement as ScenarioOutline;
if (scenarioOutline != null)
{
this.excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
}
row++;
}
}
private static void CopyRowAsRange(IXLWorksheet originalSheet, int originalRowNumber, IXLWorksheet destSheet, int destRowNumber)
{
{
var destinationRow = destSheet.Row(destRowNumber);
destinationRow.Clear();
var originalRow = originalSheet.Row(originalRowNumber);
int columnNumber = originalRow.LastCellUsed(true).Address.ColumnNumber;
var originalRange = originalSheet.Range(originalRowNumber, 1, originalRowNumber, columnNumber);
var destRange = destSheet.Range(destRowNumber, 1, destRowNumber, columnNumber);
originalRange.CopyTo(destRange);
}
}
public static void AutoFitWorksheet(IXLWorksheet ws, int startCol, int startRow, double lineHeight)
{
foreach (var row in ws.RowsUsed().Skip(startRow))
{
var maxLines = 1.0;
foreach (var cell in row.CellsUsed().Skip(startCol))
{
if (cell.Value != null)
{
var lines = cell.Value.ToString().Length / (cell.WorksheetColumn().Width * 1.15);
maxLines = maxLines < lines ? lines : maxLines;
}
}
row.Height = lineHeight * Math.Ceiling(maxLines);
}
}
public void BuildTableOfContents(XLWorkbook workbook, IXLWorksheet worksheet, ref int row, int column, GeneralTree<INode> features)
{
foreach (var childNode in features.ChildNodes)
{
var featureChildNode = childNode.Data as FeatureNode;
if (featureChildNode != null)
{
var featureWorksheet = FindFirstMatchingA1TitleUsingFeatureName(workbook, featureChildNode);
this.WriteFileCell(worksheet, ref row, column, featureWorksheet);
}
else if (childNode.Data.NodeType == NodeType.Structure)
{
this.WriteDirectoryCell(worksheet, ref row, column, childNode.Data.Name);
this.BuildTableOfContents(workbook, worksheet, ref row, column + 1, childNode);
}
}
}
public void Format(IXLWorksheet worksheet, Step step, ref int row)
{
worksheet.Cell(row, "C").Style.Font.SetBold();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
worksheet.Cell(row, "C").Value = step.NativeKeyword;
worksheet.Cell(row++, "D").Value = step.Name;
if (step.TableArgument != null)
{
this.excelTableFormatter.Format(worksheet, step.TableArgument, ref row);
}
if (!string.IsNullOrEmpty(step.DocStringArgument))
{
this.excelDocumentStringFormatter.Format(worksheet, step.DocStringArgument, ref row);
}
}
private void ProcessWorkSheet(IXLWorksheet worksheet)
{
if (worksheet != null)
{
foreach (var row in worksheet.RowsUsed())
{
if (row != null && row.RowBelow() != null)
{
var currentRow = row.RowBelow();
if (currentRow != null && currentRow.CellsUsed().Any())
{
var partnerUniqueId = currentRow.FirstCellUsed().GetString();
var armShort = currentRow.FirstCellUsed().CellRight(1).GetString();
var amount = currentRow.FirstCellUsed().CellRight(2).GetDouble();
var currency = currentRow.FirstCellUsed().CellRight(3).GetString();
var month = currentRow.FirstCellUsed().CellRight(4).GetString();
var year = currentRow.FirstCellUsed().CellRight(5).GetString();
var armId = this._partnershipArmMappings.ContainsKey(armShort)
? this._partnershipArmMappings.FirstOrDefault(i => i.Key.Equals(armShort)).Value
: 0;
var currencyId = this._currencySymbol.ContainsKey(currency)
? this._currencySymbol.FirstOrDefault(i => i.Key.Equals(currency)).Value
: 0;
var partnerId = this._partnerIdMappings.ContainsKey(partnerUniqueId)
? this._partnerIdMappings.FirstOrDefault(i => i.Key.Equals(partnerUniqueId)).Value
: 0;
var item = new StagedPartnership()
{
Amount = (decimal) amount,
ArmId = armId,
ChurchId = _churchId,
CurrencyId = currencyId,
DateCreated = DateTime.Now,
Month = 1,
PartnerId = partnerId,
Year = Convert.ToInt32(year)
};
_stagedPartnershipService.Insert(item);
}
}
}
}
}
public static void AppendOmnitureHeaders(IXLWorksheet omniture)
{
var headers = new string[] {
"Id",
"Parent Id",
"Site",
"Site Section",
"Subsection",
"Active State",
"Object",
"Object Description",
"Call To Action",
"pagename",
"event",
"prop1",
"prop2",
"prop3",
"prop4",
"prop5",
"prop6",
"prop7",
"prop8",
"prop9",
"prop10",
"prop11",
"prop12",
"prop13",
"prop14",
"prop15",
"prop16",
"prop17",
"prop18",
"prop19",
"prop20",
"prop21",
"prop22",
"prop23",
"prop24",
"prop25",
"prop26",
"prop27"
};
AppendArray(omniture, headers);
}
private void ProcessWorksheet(IXLWorksheet workSheet)
{
if (workSheet != null)
{
foreach (var row in workSheet.RowsUsed())
{
if (row != null && row.RowBelow() != null)
{
var currentRow = row.RowBelow();
if (currentRow != null && currentRow.CellsUsed().Any())
{
var title = currentRow.FirstCellUsed().GetString();
var firstName = currentRow.FirstCellUsed().CellRight(1).GetString();
var lastName = currentRow.FirstCellUsed().CellRight(2).GetString();
var email = currentRow.FirstCellUsed().CellRight(3).GetString();
var phone = currentRow.FirstCellUsed().CellRight(4).GetString();
var gender = currentRow.FirstCellUsed().CellRight(5).GetString();
var dateOfBirth = currentRow.FirstCellUsed().CellRight(6).GetDateTime();
var pcfUniqueId = currentRow.FirstCellUsed().CellRight(7).GetString();
var cellUniqueId = currentRow.FirstCellUsed().CellRight(8).GetString();
var pcfId = this._pcfIdMappings.ContainsKey(pcfUniqueId) ? this._pcfIdMappings.FirstOrDefault(i => i.Key.Equals(pcfUniqueId)).Value : 0;
var cellId = this._cellIdMappings.ContainsKey(cellUniqueId) ? this._cellIdMappings.FirstOrDefault(i => i.Key.Equals(cellUniqueId)).Value : 0;
var item = new StagedPartner
{
ChurchId = this._churchId,
DateCreated = DateTime.Now,
Email = email,
Phone = phone,
DateOfBirth = dateOfBirth,
Title = title,
FirstName = firstName,
LastName = lastName,
PCFId = pcfId,
CellId = cellId,
Gender = gender,
UniqueId = IDGenerators.UniqueIdGenerator.GenerateUniqueIdForPartner(firstName + lastName),
};
_stagedPartnerService.Insert(item);
}
}
}
}
}
int Estrai_Template_2017(IXLWorksheet wsAnno, int lastRowNumber, IXLWorksheet dati, int riga)
{
var laura = wsAnno.Range("B1", "F" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var valerio = wsAnno.Range("G1", "L" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var comune = wsAnno.Range("L1", "R" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var movimenti = laura.Concat(valerio).Concat(comune).Where(r => r != null).ToArray();
foreach (var movimento in movimenti)
{
dati.Cell(riga, "A").Value = movimento.Data;
dati.Cell(riga, "B").Value = movimento.Categoria;
dati.Cell(riga, "C").Value = movimento.Per;
dati.Cell(riga, "D").Value = movimento.Descrizione;
dati.Cell(riga, "E").Value = movimento.Spesa;
riga++;
}
return riga;
}
private void ProcessWorksheet(IXLWorksheet xlWorksheet)
{
if(!_uniqueIdIdMapping.Any() )
_uniqueIdIdMapping = _zoneService.GetAll().Select(i => new {i.UniqueId, i.Id}).ToDictionary(t => t.UniqueId, t => t.Id);
var rows = xlWorksheet.RowsUsed();
foreach (var row in rows)
{
var parentUniqueId = row.FirstCellUsed().GetString();
var parentId =
_uniqueIdIdMapping.FirstOrDefault(i => i.Key.Equals(parentUniqueId, StringComparison.InvariantCultureIgnoreCase)).Value;
var name = row.FirstCellUsed().CellRight().GetString();
var m = new StagedGroup()
{
ZoneId = parentId,
Name = name,
UniqueId = UniqueIdGenerator.GenerateUniqueIdForGroup(name)
};
_groupService.Insert(m);
}
}
public void Format(IXLWorksheet worksheet, Step step, ref int row)
{
// Add comments
if (step.Comments.Any(o => o.Type == CommentType.StepComment))
{
foreach (var comment in step.Comments.Where(o => o.Type == CommentType.StepComment))
{
worksheet.Cell(row, "C").Style.Font.SetItalic();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Cell(row, "C").Value = comment.Text;
row++;
}
}
worksheet.Cell(row, "C").Style.Font.SetBold();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
worksheet.Cell(row, "C").Value = step.NativeKeyword;
worksheet.Cell(row++, "D").Value = step.Name;
if (step.Comments.Any(o => o.Type == CommentType.AfterLastStepComment))
{
foreach (var comment in step.Comments.Where(o => o.Type == CommentType.AfterLastStepComment))
{
worksheet.Cell(row, "C").Style.Font.SetItalic();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Cell(row, "C").Value = comment.Text;
row++;
}
}
if (step.TableArgument != null)
{
this.excelTableFormatter.Format(worksheet, step.TableArgument, ref row);
}
if (!string.IsNullOrEmpty(step.DocStringArgument))
{
this.excelDocumentStringFormatter.Format(worksheet, step.DocStringArgument, ref row);
}
}
public void Format(IXLWorksheet worksheet, Scenario scenario, ref int row)
{
int originalRow = row;
worksheet.Cell(row, "B").Style.Font.SetBold();
worksheet.Cell(row++, "B").Value = scenario.Name;
worksheet.Cell(row++, "C").Value = scenario.Description;
var results = this.testResults.GetScenarioResult(scenario);
if (this.configuration.HasTestResults && results.WasExecuted)
{
worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(
results.WasSuccessful
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
foreach (Step step in scenario.Steps)
{
this.excelStepFormatter.Format(worksheet, step, ref row);
}
}
public void Format(IXLWorksheet worksheet, ScenarioOutline scenarioOutline, ref int row)
{
int originalRow = row;
worksheet.Cell(row++, "B").Value = scenarioOutline.Name;
worksheet.Cell(row++, "C").Value = scenarioOutline.Description;
var results = testResults.GetScenarioOutlineResult(scenarioOutline);
if (configuration.HasTestResults && results.WasExecuted)
{
worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(results.WasSuccessful
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
foreach (Step step in scenarioOutline.Steps)
{
excelStepFormatter.Format(worksheet, step, ref row);
}
row++;
worksheet.Cell(row++, "B").Value = "Examples";
excelTableFormatter.Format(worksheet, scenarioOutline.Example.TableArgument, ref row);
}
private static void SetResLiquidOxides(
IXLWorksheet ws,
OxidesByTemperatureModel model,
int currentRow,
int currentColumn)
{
currentRow = currentRow + Model.OxidesResultModel.Count + 9;
ws.Cell(currentRow, currentColumn - 1).Value = model.Temperature;
float sum = 0;
foreach (var oxide in model.OxidesResult)
{
sum += oxide.Percentage;
ws.Cell(currentRow, currentColumn).Value = oxide.Percentage;
currentColumn++;
}
ws.Cell(currentRow, currentColumn).Value = model.OxidesResult.Sum(x => x.Percentage);
}
/// <summary>
/// Speichern der gefilterten Daten einer Filiale in einem eigenen Arbeitsblatt
/// </summary>
/// <param name="HeaderList"></param>
/// <param name="FilialenExport"></param>
/// <param name="worksheet"></param>
private void SaveFilialDataToWorksheet(List <string> HeaderList, List <CSVImportModel> FilialenExport, IXLWorksheet worksheet)
{
if (HeaderList != null)
{
worksheet.Cell(1, 1).InsertData(HeaderList, true);
}
if (FilialenExport != null)
{
worksheet.Cell(2, 1).InsertData(FilialenExport);
}
}
private void CreateExcel()
{
bool merge = false;
var workbook = new XLWorkbook();
//Tworzenie arkuszy
IXLWorksheet worksheetSummary = workbook.Worksheets.Add("Podsumowanie");
IXLRange summaryRange = worksheetSummary.Range(1, 1, Int16.MaxValue, 3);
IXLRange summaryTitlesRange = summaryRange.Range(1, 1, 1, 3);
summaryTitlesRange.Style.Font.Bold = true;
summaryTitlesRange.Cell(1, 1).Value = "Tytuł";
summaryTitlesRange.Cell(1, 2).Value = "Wydawnictwo";
summaryTitlesRange.Cell(1, 3).Value = "Ilość sprzedanych sztuk";
summaryTitlesRange.RangeUsed().Style
.Border.SetInsideBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
IXLRange summaryDataRange = summaryRange.Range(2, 1, Int16.MaxValue, 3);
for (int i = 0; i < raportElement.Count - 1; i++)
{
if (summaryDataRange.RowCount() > 0)
{
for (int j = 0; j < summaryDataRange.RowCount() - 1; j++)
{
if (summaryDataRange.Cell(j + 1, 1).Value.Equals(raportElement[i].Nazwa) && summaryDataRange.Cell(j + 1, 2).Value.Equals(raportElement[i].Wydawnictwo))
{
int tmp = Convert.ToInt32(summaryDataRange.Cell(j + 1, 3).Value);
tmp += raportElement[j].Ilosc;
summaryDataRange.Cell(j + 1, 3).Value = tmp;
merge = true;
raportElement.RemoveAt(i);
j = 1000;
}
}
}
if (!merge)
{
summaryDataRange.Cell(i + 1, 1).Value = raportElement[i].Nazwa;
summaryDataRange.Cell(i + 1, 2).Value = raportElement[i].Wydawnictwo;
summaryDataRange.Cell(i + 1, 3).Value = raportElement[i].Ilosc;
}
else
{
merge = !merge;
}
}
summaryDataRange.Cell(raportElement.Count, 1).Value = raportElement[raportElement.Count - 1].Nazwa;
summaryDataRange.Cell(raportElement.Count, 2).Value = raportElement[raportElement.Count - 1].Wydawnictwo;
summaryDataRange.Cell(raportElement.Count, 3).Value = raportElement[raportElement.Count - 1].Ilosc;
worksheetSummary.Columns().AdjustToContents();
using (var dlg = new SaveFileDialog())
{
dlg.Filter = "(*.xlsx) Excel | *.xlsx";
dlg.AddExtension = true;
dlg.ShowDialog();
try
{
if (!String.IsNullOrWhiteSpace(dlg.FileName))
{
workbook.SaveAs(dlg.FileName);
MessageBox.Show("Raport wygenerowany", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (IOException ex)
{
MessageBox.Show(ex.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
}
public Excel(String path, int sheet)
{
this.path = path;
this.wb = new XLWorkbook(path);
this.ws = wb.Worksheet(sheet);
}
private static int GetNextEmptyRowNumber(IXLWorksheet sheet)
{
return(sheet.LastRowUsed().RowNumber() + 1);
}
/// <summary>
/// データ変換ツールからPathLink.txtを作成する。とりあえずそのまま移植してその後リファクタリング
/// </summary>
public void DataConvert(string filePath)
{
IXLWorksheet sh = null;
List <string> filePathList = new List <string>();
try
{
//ﾃﾞｰﾀ変換ツールを開いてリストを作成します。
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
XLWorkbook wb = new XLWorkbook(fs, XLEventTracking.Disabled);
sh = wb.Worksheet("変換設定");
//ファイルパスを抜く ｾﾙの検索が無いのでC1からC下限まで逐次検索
for (int i = 1; i <= sh.LastRowUsed().RowNumber(); i++)
{
string path = sh.Cell($"D{i}").Value.ToString();
if (sh.Cell($"C{i}").Value.ToString() == "変換ファイル名（フルパス）" && path != "")
{
filePathList.Add(path);
}
}
}
catch (System.IO.FileNotFoundException)
{
MessageBox.Show(filePath + "が見つかりません", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}catch (System.IO.IOException)
{
MessageBox.Show("ﾃﾞｰﾀ変換ツールを一旦閉じてください", "Error", MessageBoxButtons.OK);
return;
}
catch (System.Exception)
{
MessageBox.Show("変換設定のシートが見つかりませんでした", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//ファイルパルの取得完了したので生死判定と拡張子判定
if (!DeadOrAlive(filePathList))
{
return;
}
progress prg = new progress();
prg.Show();
Stopwatch stp = new Stopwatch();
prg.wholeProgress.Maximum = filePathList.Count;
prg.wholeProgress.Minimum = 0;
prg.wholeProgress.Value = 0;
//ﾃﾞｰﾀ取得処理を行う。
foreach (string list in filePathList)
{
stp.Start();
FileStream fs = new FileStream(list, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
XLWorkbook wb = new XLWorkbook(fs, XLEventTracking.Disabled);
prg.wholeLabel.Text = new FileInfo(list).Name;
prg.wholeProgress.Value++;
prg.singleProgress.Maximum = wb.Worksheets.Count;
prg.singleProgress.Minimum = 0;
prg.singleProgress.Value = 0;
Application.DoEvents();
foreach (IXLWorksheet sheet in wb.Worksheets)
{
prg.singleLabel.Text = sheet.Name;
prg.singleProgress.Value++;
prg.Update();
//System.Threading.Thread.Sleep(1000); //1秒エミュレート
switch (sheet.Name)
{
case "ランプ部品":
break;
case "サウンド部品":
break;
case "モータ部品":
break;
case "選択テーブル部品":
break;
case "ＳＥＬ":
break;
case "パターン":
break;
case "ＰＡＴ":
break;
case "ＴＢ":
break;
case "関数部品":
break;
case "サウンドフレーズ部品":
break;
}
//System.Console.WriteLine("ｼｰﾄの処理：" + sheet.Name);
}
}
prg.Dispose();
stp.Start();
System.Console.WriteLine("処理時間：" + stp.Elapsed);
}
public static Bio2DA ReadExcelTo2DA(IExportEntry export, string Filename)
{
var Workbook = new XLWorkbook(Filename);
IXLWorksheet iWorksheet = null;
if (Workbook.Worksheets.Count() > 1)
{
try
{
iWorksheet = Workbook.Worksheet("Import");
}
catch
{
MessageBox.Show("Import Sheet not found");
return(null);
}
}
else
{
iWorksheet = Workbook.Worksheet(1);
}
//Do we want to limit user to importing same column structure as existing? Who would be stupid enough to do something else??? ME.
// - Kinkojiro, 2019
//STEP 1 Clear existing data
Bio2DA bio2da = new Bio2DA();
bio2da.export = export;
//STEP 2 Read columns and row names
//Column names
IXLRow hRow = iWorksheet.Row(1);
foreach (IXLCell cell in hRow.Cells(hRow.FirstCellUsed().Address.ColumnNumber, hRow.LastCellUsed().Address.ColumnNumber))
{
if (cell.Address.ColumnNumber > 1) //ignore excel column 1
{
bio2da.ColumnNames.Add(cell.Value.ToString());
}
}
//Row names
IXLColumn column = iWorksheet.Column(1);
foreach (IXLCell cell in column.Cells())
{
if (cell.Address.RowNumber > 1) //ignore excel row 1
{
bio2da.RowNames.Add(cell.Value.ToString());
}
}
//Populate the Bio2DA now that we know the size
bio2da.Cells = new Bio2DACell[bio2da.RowCount, bio2da.ColumnCount];
//Step 3 Populate the table.
//indices here are excel based. Subtract two to get Bio2DA based.
for (int rowIndex = 2; rowIndex < (bio2da.RowCount + 2); rowIndex++)
{
for (int columnIndex = 2; columnIndex < bio2da.ColumnCount + 2; columnIndex++)
{
IXLCell xlCell = iWorksheet.Cell(rowIndex, columnIndex);
string xlCellContents = xlCell.Value.ToString();
if (!string.IsNullOrEmpty(xlCellContents))
{
Bio2DACell newCell = new Bio2DACell();
if (int.TryParse(xlCellContents, out int intVal))
{
newCell.Type = Bio2DACell.Bio2DADataType.TYPE_INT;
newCell.Data = BitConverter.GetBytes(intVal);
}
else if (float.TryParse(xlCellContents, out float floatVal))
{
newCell.Type = Bio2DACell.Bio2DADataType.TYPE_FLOAT;
newCell.Data = BitConverter.GetBytes(floatVal);
}
else
{
newCell.Type = Bio2DACell.Bio2DADataType.TYPE_NAME;
newCell.Pcc = export.FileRef; //for displaying, if this displays before the export is reloaded and 2da is refreshed
newCell.Data = BitConverter.GetBytes((long)export.FileRef.FindNameOrAdd(xlCellContents)); //long because names are 8 bytes not 4
}
bio2da[rowIndex - 2, columnIndex - 2] = newCell;
}
else
{
bio2da.IsIndexed = true; //Null cells = indexing
}
}
}
return(bio2da);
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_InvRecordModel>(x => x.PartId, "物料编码");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.QTY, "数量");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.InvId, "库房编码");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.SubInvId, "");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.BillId, "单据ID");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.SourceBill, "单据来源");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.OperateDate, "操作时间");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.Lot, "");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.Type, "出入库类型");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.OperateMan, "操作人");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.Stock_InvId, "备料库存");
excelFile.AddMapping <WMS_InvRecordModel>(x => x.StockStatus, "备料状态：1-不适用（直接修改库存现有量）；2-已备料；3-无效备料（取消备料后将2改成3）；4-取消备料（当前操作是取消备料）");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_InvRecordModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_InvRecordModel();
model.Id = row.Id;
model.PartId = row.PartId;
model.QTY = row.QTY;
model.InvId = row.InvId;
model.SubInvId = row.SubInvId;
model.BillId = row.BillId;
model.SourceBill = row.SourceBill;
model.OperateDate = row.OperateDate;
model.Lot = row.Lot;
model.Type = row.Type;
model.OperateMan = row.OperateMan;
model.Stock_InvId = row.Stock_InvId;
model.StockStatus = row.StockStatus;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_InvRecord entity = new WMS_InvRecord();
entity.Id = model.Id;
entity.PartId = model.PartId;
entity.QTY = model.QTY;
entity.InvId = model.InvId;
entity.SubInvId = model.SubInvId;
entity.BillId = model.BillId;
entity.SourceBill = model.SourceBill;
entity.OperateDate = model.OperateDate;
entity.Lot = model.Lot;
entity.Type = model.Type;
entity.OperateMan = model.OperateMan;
entity.Stock_InvId = model.Stock_InvId;
entity.StockStatus = model.StockStatus;
db.WMS_InvRecord.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
private void btnExportExcel_Click(object sender, RoutedEventArgs e)
{
try
{
if (allProduct_DG.Items.Count > 0)
{
Stream myStream;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "EXCELL FILE (*.xlsx)|*.xlsx";
saveFileDialog1.RestoreDirectory = true;
string filename;
if (saveFileDialog1.ShowDialog() == true)
{
if ((myStream = saveFileDialog1.OpenFile()) != null)
{
// Code to write the stream goes here.
filename = saveFileDialog1.FileName;
myStream.Close();
var workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Sheet1");
worksheet.Columns().AdjustToContents();
worksheet.Cell(1, 1).Value = "Все продукты. Дата: " + DateTime.Now;
worksheet.Cell(1, 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Range(worksheet.Cell(1, 1), worksheet.Cell(1, 6)).Merge();
worksheet.Cell(2, 1).Value = "Штрих-код";
worksheet.Cell(2, 2).Value = "Товар";
worksheet.Cell(2, 3).Value = "Тип";
worksheet.Cell(2, 4).Value = "Массовый тип";
worksheet.Cell(2, 5).Value = "Кол-во";
int kk = 3;
foreach (InfoAllProduct rv in allProduct_DG.Items)
{
worksheet.Cell(kk, 1).Value = rv.Shtrix;
worksheet.Cell(kk, 2).Value = rv.NameProduct;
worksheet.Cell(kk, 3).Value = rv.TypeName;
worksheet.Cell(kk, 4).Value = rv.MassaName;
worksheet.Cell(kk, 5).Value = rv.Qoldiq;
kk++;
}
worksheet.Columns("A", "Z").AdjustToContents();
// string sql = "SELECT Dorilars.Id as id,Partiyas.Id as t_id, TovarNomi as Nomi, IshlabChiqaruvchi as Ishlab_chiqaruvchi,Nomi as Turi,Doza,(BazaviyNarx) as Olish_Narxi,SotiladiganNarx as Sotiladigan_Narx,NechtaKeldiDona/PachkadaNechta as Necha_Pachka,NechtaKeldiDona%PachkadaNechta as Necha_Dona,PachkadaNechta as Pachkada_soni, Shtrix as Shtrix_Kod, CONVERT(varchar, KelganSana, 3) as kelgan_sana From Dorilars,Partiyas,DorilarTuris where Dorilars.DorilarTuriId=DorilarTuris.Id and Dorilars.id=Partiyas.DorilarId;";
workbook.SaveAs(filename);
Process cmd = new Process();
cmd.StartInfo.FileName = "cmd.exe";
cmd.StartInfo.RedirectStandardInput = true;
cmd.StartInfo.RedirectStandardOutput = true;
cmd.StartInfo.CreateNoWindow = true;
cmd.StartInfo.UseShellExecute = false;
cmd.Start();
cmd.StandardInput.WriteLine(filename);
cmd.StandardInput.Flush();
cmd.StandardInput.Close();
cmd.WaitForExit();
// workbook.
}
}
}
}
catch (Exception err)
{
MessageBox.Show("Error15");
}
}
private void MetroButton2_Click(object sender, EventArgs e)
{
var filee = openFileDialog1.FileName;
try
{
var filePath = Path.GetTempFileName();
if (Path.GetExtension(filee) != ".xlsx" && Path.GetExtension(filee) != ".xls")
{
MessageBox.Show("This is not a valid excel file", "Invalid File", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
bool firstRow = true; bool firstRow2 = true;
DataTable dt = new DataTable();
int count = 0;
using (XLWorkbook workBook = new XLWorkbook(filee))
{
var cats = ctx.Categories.ToList();
var categories = cats.Select(x => x.CategoryName);
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
firstRow = false;
}
else
{
var i = row.Cell(6).Value.ToString();
if (!categories.Contains(row.Cell(6).Value.ToString().Trim()))
{
MessageBox.Show("Ensure that all category names listed in the excel sheet are already registered on the system. click on the Category drop down to see registered categories, or add a new category in the Create Category section above.", "Unable to Upload!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
}
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow2)
{
firstRow2 = false;
}
else
{
string title = row.Cell(2).Value.ToString();
if (ctx.Books.Any(c => c.Title == title))
{
MessageBox.Show($"A book with the Title {title} already exists", "Sorry", MessageBoxButtons.OK);
}
else
{
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
string qty = row.Cell(5).Value.ToString();
ctx.Books.Add(new Book()
{
Author = row.Cell(4).Value.ToString(),
ISBN = row.Cell(3).Value.ToString(),
QtyEntered = Int32.Parse(qty),
QtyAvailable = Int32.Parse(qty),
Title = row.Cell(2).Value.ToString(),
CategoryId = cats.Where(x => x.CategoryName == row.Cell(6).Value.ToString().Trim()).FirstOrDefault().Id
});
ctx.SaveChanges();
count++;
}
}
}
}
LoadGrid();
MessageBox.Show(count + " Records Saved Successfully", "Good Job", MessageBoxButtons.OK);
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("The process cannot access the file"))
{
MessageBox.Show("Close the excel file and try again", "Unable to Upload", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
MessageBox.Show("An error occured while uploading the file, Ensure the excel sheet is properly formatted or contact the vendor", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
protected TagTests()
{
_wb = new XLWorkbook();
_ws = _wb.AddWorksheet("Sheet1");
}
public static int SheetIndex(IXLWorksheet sheet)
{
return(sheet.Position);
}
public ClosedXmlReader(Stream stream)
{
_workbook = new XLWorkbook(stream, XLEventTracking.Disabled);
_worksheet = _workbook.Worksheet(1); //Get first worksheet
}
private static void SetSolidPhaseOxide(
IXLWorksheet ws,
OxidesByTemperatureModel model,
int currentRow,
ref int currentColumn)
{
foreach (var oxide in model.OxidesResult)
{
ws.Cell(currentRow, currentColumn).Value = oxide.Percentage;
currentColumn++;
}
}
public static bool ReadFileExcel()
{
using (XLWorkbook workBook = new XLWorkbook(PathFileExcel))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(6);
//Create a new DataTable.
//DataTable dt = new DataTable();
List <ExcelRow> dataRows = new List <ExcelRow>();
//Loop through the Worksheet rows.
var A = ""; var B = ""; var C = ""; var D = ""; var E = "";
var F = ""; var G = ""; var H = ""; var I = "";
bool firstRow = true;
#region
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
if (cell.Address.ToString().Contains("A"))
{
A = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("B"))
{
B = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("C"))
{
C = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("D"))
{
D = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("E"))
{
E = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("F"))
{
F = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("G"))
{
G = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("H"))
{
H = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("I"))
{
I = cell.Value.ToString();
}
}
firstRow = false;
dataRows.Add(new ExcelRow
{
Menu = B,
DocumentType = C,
KeyXml = RemoveAllSpace(D),
View = ChangeSpecial(E),
Process = ChangeSpecial(F),
Create = ChangeSpecial(G),
Delete = ChangeSpecial(H),
Update = ChangeSpecial(I)
});
}
else
{
foreach (IXLCell cell in row.Cells())
{
if (cell.Address.ToString().Contains("A"))
{
A = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("B"))
{
B = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("C"))
{
C = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("D"))
{
D = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("E"))
{
E = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("F"))
{
F = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("G"))
{
G = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("H"))
{
H = cell.Value.ToString();
}
if (cell.Address.ToString().Contains("I"))
{
I = cell.Value.ToString();
}
}
dataRows.Add(new ExcelRow
{
Menu = B,
DocumentType = C,
KeyXml = RemoveAllSpace(D),
View = ChangeSpecial(E),
Process = ChangeSpecial(F),
Create = ChangeSpecial(G),
Delete = ChangeSpecial(H),
Update = ChangeSpecial(I)
});
}
}
#endregion
File.WriteAllText(PathFileExport, String.Empty);
GenXML(dataRows);
return(true);
}
}
public void changeSheet(int sheet)
{
this.ws = wb.Worksheet(sheet);
}
/// <summary>
/// Apply formatting to the worksheet
/// </summary>
/// <param name="worksheet"></param>
private void FormatWorksheet(ref IXLWorksheet worksheet)
{
worksheet.ColumnsUsed().AdjustToContents();
worksheet.Column(2).Width = 30;
worksheet.Column(2).Style.Alignment.WrapText = true;
}
public void OpenSelectDbf()
{
var ll = new DialogForm.DialogForm();
ll.ShowDialog();
if (ll.DialogResult == DialogResult.OK)
{
var a = _owner.listView6.FindItemWithText(ll.textBox1.Text + ".xlsx");
if (a == null)
{
_owner.toolStripStatusLabel1.Text = @"ОТЧЕТ СОБИРАЕТСЯ!!!";
var con = new OleDbConnection(Pathing.ConnectString.Connection);
try
{
var lvl = _owner.listView4.SelectedItems[0]; //Переменная имени Файла
string file = Path.GetFullPath(Pathing.PathName.Path2 + lvl.Text);
using (var workbook = new XLWorkbook(file))
{
IXLWorksheet workSheet = workbook.Worksheet(1);
var dt = new DataTable("NDFLIF");
var dt1 = new DataTable("NDFLNULLNOT");
dt1.Columns.Add("Имя файла");
dt1.Columns.Add("Статус");
dt1.Columns.Add("Количество");
var dt2 = new DataTable("NDFLNOTNULLNOT");
dt2.Columns.Add("Имя файла");
dt2.Columns.Add("Статус");
var dt3 = new DataTable("Detalization");
var proc = (100.0f / workSheet.Rows().Count());
foreach (var row in workSheet.Rows())
{
foreach (var cell in row.Cells("C1"))
{
string sql = "select ИМЯФАЙЛАВЫ, * from Kvitan Where КОДНООТПР = '9965' and ИМЯФАЙЛАВЫ = '" + cell.Value + "'";
using (var cmd = new OleDbCommand(sql, con))
{
con.Open();
using (var da = new OleDbDataAdapter(cmd))
{
da.Fill(dt);
da.Fill(dt3);
if (dt.Rows.Count > 0)
{
dt1.Rows.Add(cell.Value.ToString(), "Квитанция принята", dt.Rows.Count);
}
else
{
if (cell.Value.ToString() != "")
{
string sql1 = "select Statistics.ИМЯФАЙЛАВЫ, ИМЯФАЙЛАПР, NA.НАИМОРГ, NA.ИНН, Statistics.ИНФПОЛЕ,'Нет файла квитанции!!!' as 'Состояние' from Statistics left Join NA on NA.ID_BOSS = Statistics.ID_BOSS Where Statistics.ИМЯФАЙЛАВЫ ='" + cell.Value + "'";
using (var cmd1 = new OleDbCommand(sql1, con))
{
using (var da1 = new OleDbDataAdapter(cmd1))
{
da1.Fill(dt);
if (dt.Rows.Count > 0)
{
da1.Fill(dt2);
}
else
{
dt2.Rows.Add(cell.Value.ToString(), "Файл отсутствует в таблице");
}
}
}
}
}
dt.Clear();
}
con.Close();
_owner.backgroundWorker2.ReportProgress((int)(proc * 100.0f)); //В нее можно только integer (не double с плавующей точкой) подумать!!!!!
}
}
}
var workbook1 = new XLWorkbook();
var worksheet1 = workbook1.Worksheets.Add("Отчет отработаных.");
worksheet1.Cell("A1").InsertTable(dt1).Worksheet.Columns().AdjustToContents();
var worksheet2 = workbook1.Worksheets.Add("Отчет нет квитанций");
worksheet2.Cell("A1").InsertTable(dt2).Worksheet.Columns().AdjustToContents();
var worksheet3 = workbook1.Worksheets.Add("Детализация");
worksheet3.Cell("A1").InsertTable(dt3).Worksheet.Columns().AdjustToContents();
workbook1.SaveAs(Pathing.PathName.Path3 + ll.textBox1.Text + ".xlsx");
string path = Path.GetFullPath(Pathing.PathName.Path3);
if (Directory.Exists(path))
{
String[] dirarr = Directory.GetFiles(path, "*.xlsx").Select(Path.GetFileName).ToArray();
_owner.listView6.Items.Clear();
foreach (String item in dirarr)
{
_owner.listView6.Items.Add(item);
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
else
{
MessageBox.Show(@"Имя файла совпадает с конечным!");
}
}
}
/// -----------------------------------------------------------------------------
/// <summary>
/// DataTableをもとにxlsxファイルを作成しPDF化</summary>
/// <param name="dtShohinList">
/// 仕入実績確認のデータテーブル</param>
/// -----------------------------------------------------------------------------
public string dbToPdf(DataTable dtShohinList, List <string> lstItem)
{
string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"];
string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss");
string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
try
{
CreatePdf pdf = new CreatePdf();
// ワークブックのデフォルトフォント、フォントサイズの指定
XLWorkbook.DefaultStyle.Font.FontName = "ＭＳ 明朝";
XLWorkbook.DefaultStyle.Font.FontSize = 9;
// excelのインスタンス生成
XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled);
IXLWorksheet worksheet = workbook.Worksheets.Add("Header");
IXLWorksheet headersheet = worksheet; // ヘッダーシート
IXLWorksheet currentsheet = worksheet; // 処理中シート
//Linqで必要なデータをselect
var outDataAll = dtShohinList.AsEnumerable()
.Select(dat => new
{
daibunrui = dat["大分類名"],
chubunrui = dat["中分類名"],
maker = dat["メーカー名"],
kataban = dat["品名型式"],
teika = (decimal)dat["定価"],
baika = (decimal)dat["標準売価"],
siireTanak = (decimal)dat["仕入単価"],
hyokaTanka = (decimal)dat["評価単価"],
tateneTanka = (decimal)dat["建値仕入単価"],
tanabanHonsha = dat["棚番本社"],
tanabanGifu = dat["棚番岐阜"],
}).ToList();
// リストをデータテーブルに変換
DataTable dtShohin = pdf.ConvertToDataTable(outDataAll);
int maxRowCnt = dtShohin.Rows.Count;
int maxColCnt = dtShohin.Columns.Count;
int pageCnt = 0; // ページ(シート枚数)カウント
int rowCnt = 1; // datatable処理行カウント
int xlsRowCnt = 4; // Excel出力行カウント（開始は出力行）
int maxPage = 0; // 最大ページ数
// ページ数計算
double page = 1.0 * maxRowCnt / 35;
double decimalpart = page % 1;
if (decimalpart != 0)
{
// 小数点以下が0でない場合、+1
maxPage = (int)Math.Floor(page) + 1;
}
else
{
maxPage = (int)page;
}
// ClosedXMLで1行ずつExcelに出力
foreach (DataRow drShohin in dtShohin.Rows)
{
// 1ページ目のシート作成
if (rowCnt == 1)
{
pageCnt++;
// タイトル出力（中央揃え、セル結合）
IXLCell titleCell = headersheet.Cell("A1");
titleCell.Value = "商品単価一覧";
titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
titleCell.Style.Font.FontSize = 14.5;
headersheet.Range("A1", "K1").Merge();
// ヘッダー出力（3行目のセル）
headersheet.Cell("A3").Value = "大分類";
headersheet.Cell("B3").Value = "中分類";
headersheet.Cell("C3").Value = "メーカー名";
headersheet.Cell("D3").Value = "品名・型番";
headersheet.Cell("E3").Value = "定価";
headersheet.Cell("F3").Value = "標準売価";
headersheet.Cell("G3").Value = "仕入単価";
headersheet.Cell("H3").Value = "評価単価";
headersheet.Cell("I3").Value = "建値単価";
headersheet.Cell("J3").Value = "本社棚番";
headersheet.Cell("K3").Value = "岐阜棚番";
// ヘッダー列
headersheet.Range("A3", "K3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// セルの周囲に罫線を引く
headersheet.Range("A3", "K3").Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetBottomBorder(XLBorderStyleValues.Thin)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
// 列幅の指定
headersheet.Column(1).Width = 14;
headersheet.Column(2).Width = 14;
headersheet.Column(3).Width = 14;
headersheet.Column(4).Width = 40;
headersheet.Column(5).Width = 12;
headersheet.Column(6).Width = 12;
headersheet.Column(7).Width = 12;
headersheet.Column(8).Width = 12;
headersheet.Column(9).Width = 12;
headersheet.Column(10).Width = 10;
headersheet.Column(11).Width = 10;
// 印刷体裁（B4横、印刷範囲）
headersheet.PageSetup.PaperSize = XLPaperSize.B4Paper;
headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
// ヘッダー部の指定（番号）
headersheet.PageSetup.Header.Left.AddText("（№115）");
// ヘッダーシートのコピー、ヘッダー部の指定
pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow);
}
// 1セルずつデータ出力
for (int colCnt = 1; colCnt <= maxColCnt; colCnt++)
{
string str = drShohin[colCnt - 1].ToString();
// 品名・型式セルの処理
if (colCnt == 4)
{
currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
// 金額セルの処理
if (colCnt >= 5 && colCnt <= 6)
{
// 3桁毎に","を挿入する
str = string.Format("{0:#,0}", decimal.Parse(str));
currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
// 単価セルの処理
if (colCnt >= 7 && colCnt <= 9)
{
// 3桁毎に","を挿入する、小数点第2位まで
currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("#,##0.00");
currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
// 棚番セルの処理
if (colCnt == 10 || colCnt == 11)
{
currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
currentsheet.Cell(xlsRowCnt, colCnt).Value = str;
}
// 1行分のセルの周囲に罫線を引く
currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 11).Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetBottomBorder(XLBorderStyleValues.Thin)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
// 35行毎（ヘッダーを除いた行数）にシート作成
if (xlsRowCnt == 39)
{
pageCnt++;
if (pageCnt <= maxPage)
{
xlsRowCnt = 3;
// ヘッダーシートのコピー、ヘッダー部の指定
pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow);
}
}
rowCnt++;
xlsRowCnt++;
}
// ヘッダーシート削除
headersheet.Delete();
// workbookを保存
string strOutXlsFile = strWorkPath + strDateTime + ".xlsx";
workbook.SaveAs(strOutXlsFile);
// workbookを解放
workbook.Dispose();
// PDF化の処理
return(pdf.createPdf(strOutXlsFile, strDateTime, 1));
}
catch
{
throw;
}
finally
{
// Workフォルダの全ファイルを取得
string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories);
// Workフォルダ内のファイル削除
foreach (string filepath in files)
{
//File.Delete(filepath);
}
}
}
public XlsxRowWriter(IXLWorksheet worksheet, int startRowNumber, int startColumnNumber)
{
_worksheet = worksheet;
_currentRowNumber = startRowNumber;
_startColumnNumber = startColumnNumber;
}
public XLPivotTableDestination(string tableName, IXLWorksheet targetWorksheet, IXLCell targetCell)
{
TableName = tableName;
TargetWorksheet = targetWorksheet;
TargetCell = targetCell;
}
private static void CopyRowAsRange(IXLWorksheet originalSheet, int originalRowNumber, IXLWorksheet destSheet, int destRowNumber)
{
{
var destinationRow = destSheet.Row(destRowNumber);
destinationRow.Clear();
var originalRow = originalSheet.Row(originalRowNumber);
int columnNumber = originalRow.LastCellUsed(true).Address.ColumnNumber;
var originalRange = originalSheet.Range(originalRowNumber, 1, originalRowNumber, columnNumber);
var destRange = destSheet.Range(destRowNumber, 1, destRowNumber, columnNumber);
originalRange.CopyTo(destRange);
}
}
public Excel(String path)
{
this.path = path;
wb = new XLWorkbook();
ws = wb.AddWorksheet("Products");
}
public IEnumerable <ListResult> LeerExcel(string archivo, bool archivook, string descripcion)
{
//Save the uploaded Excel file.
string filePath = archivo;
List <ListResult> respuesta = null;
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
//Loop through the Worksheet rows.
int i = 1;
bool movOk = false;
string RowEmbosado = "", RowNombre = "", RowNomina = "";
List <ListExcelResult> RowsExcel = new List <ListExcelResult>();
foreach (IXLRow row in workSheet.Rows())
{
/*
* if (i == 3 && RowEmbosado == "")
* RowEmbosado = row.Cell("B").GetString();
* if (i == 4 && RowNombre == "")
* RowNombre = row.Cell("B").GetString();
* if (i == 5 && RowNomina == "")
* RowNomina = row.Cell("B").GetString();
*/
if (i > 1)
{
try
{
string RowTarjeta = "";
RowTarjeta = row.Cell("A").GetString().Replace("'", "");
if (RowTarjeta != "")
{
var ImporteOk = row.Cell("E").Value.ToString();
if (ImporteOk != "")
{
double RowImporte = Convert.ToDouble(ImporteOk);
if (RowImporte > 0)
{
string RowDescripcion = row.Cell("D").GetString();
if ((RowDescripcion.Trim() != "MOV.REVERSION RECARGA EFECTIVO" && RowDescripcion.Trim() != "DECREMENTO ON LINE" && RowDescripcion.Trim() != "DECREMENTO ONLINE") &&
RowDescripcion.Trim() != "")
{
string RowTipo = row.Cell("B").GetString();
string RowFecha = row.Cell("C").GetString();//.ToString("dd/MM/yyyy");
ListExcelResult ColsExcel = new ListExcelResult
{
Tarjeta = RowTarjeta,
Tipo = RowTipo,
Fecha = RowFecha,
Descripcion = RowDescripcion,
Importe = RowImporte
};
RowsExcel.Add(ColsExcel);
movOk = true;
}
}
}
}
}
catch (Exception err)
{
ListExcelResult ColsExcel = new ListExcelResult
{
Tarjeta = "Error",
Tipo = "Row",
Fecha = null,
Descripcion = err.ToString(),
Importe = 0
};
RowsExcel.Add(ColsExcel);
}
}
i++;
}
bool ArcOk = movOk == true ? archivook : movOk;
ListResult valores = new ListResult
{
ArchivoOk = ArcOk,
Descripcion = descripcion,
Embosado = RowEmbosado,
Nombre = RowNombre,
Nomina = RowNomina,
RowExcel = RowsExcel
};
respuesta = new List <ListResult>
{
valores
};
}
File.Delete(filePath);
return(respuesta);
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelWorksheet"/> class.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
/// <param name="readOnly">if set to <c>true</c> [read only].</param>
public ExcelWorksheet(IXLWorksheet worksheet, bool readOnly)
{
this.worksheet = worksheet;
this.readOnly = readOnly;
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
worksheet = null;
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.SaleBillNum, "销售单号（业务）(必输)");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.SellBillNum, "销售单号（系统）");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.PlanDeliveryDate, "计划发货日期");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.CustomerShortName, "客户名称(必输)");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.PartCode, "物料编码(必输)");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.Qty, "数量(必输)");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.BoxQty, "箱数");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.InvName, "库房");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.SubInvId, "子库存");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.Lot, "批次号(格式：YYYY-MM-DD)");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.Volume, "体积");
excelFile.AddMapping <WMS_Sale_OrderModel>(x => x.Remark, "备注");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.PrintStaus, "打印状态");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.PrintDate, "打印日期");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.PrintMan, "打印人");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.ConfirmStatus, "确认状态");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.ConfirmMan, "确认人");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.ConfirmDate, "确认时间");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.Attr1, "");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.Attr2, "");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.Attr3, "");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.Attr4, "");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.Attr5, "");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_Sale_OrderModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_Sale_OrderModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_Sale_OrderModel();
model.Id = row.Id;
model.SaleBillNum = row.SaleBillNum;
//model.SellBillNum = row.SellBillNum;
model.PlanDeliveryDate = row.PlanDeliveryDate;
model.CustomerShortName = row.CustomerShortName;
model.PartCode = row.PartCode;
model.Qty = row.Qty;
model.BoxQty = row.BoxQty;
model.InvName = row.InvName;
//model.SubInvId = row.SubInvId;
model.Lot = row.Lot;
model.Volume = row.Volume;
model.Remark = row.Remark;
//model.PrintStaus = row.PrintStaus;
//model.PrintDate = row.PrintDate;
//model.PrintMan = row.PrintMan;
//model.ConfirmStatus = row.ConfirmStatus;
//model.ConfirmMan = row.ConfirmMan;
//model.ConfirmDate = row.ConfirmDate;
//model.Attr1 = row.Attr1;
//model.Attr2 = row.Attr2;
//model.Attr3 = row.Attr3;
//model.Attr4 = row.Attr4;
//model.Attr5 = row.Attr5;
//model.CreatePerson = row.CreatePerson;
//model.CreateTime = row.CreateTime;
//model.ModifyPerson = row.ModifyPerson;
//model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_Sale_Order entity = new WMS_Sale_Order();
entity.Id = model.Id;
entity.SaleBillNum = model.SaleBillNum;
//entity.SellBillNum = "XS" + DateTime.Now.ToString("yyyyMMddHHmmssff");打印时生成
entity.PlanDeliveryDate = model.PlanDeliveryDate;
entity.CustomerId = model.CustomerId;
entity.PartId = model.PartId;
entity.Qty = model.Qty;
entity.BoxQty = model.BoxQty;
entity.InvId = model.InvId;
entity.SubInvId = model.SubInvId;
entity.Lot = model.Lot;
entity.Remark = model.Remark;
entity.Volume = model.Volume;
entity.PrintStaus = "未打印";
//entity.PrintDate = model.PrintDate;
//entity.PrintMan = model.PrintMan;
entity.ConfirmStatus = "未确认";
//entity.ConfirmMan = model.ConfirmMan;
//entity.ConfirmDate = model.ConfirmDate;
//entity.Attr1 = model.Attr1;
//entity.Attr2 = model.Attr2;
//entity.Attr3 = model.Attr3;
//entity.Attr4 = model.Attr4;
//entity.Attr5 = model.Attr5;
//entity.CreatePerson = model.CreatePerson;
//entity.CreateTime = model.CreateTime;
//entity.ModifyPerson = model.ModifyPerson;
//entity.ModifyTime = model.ModifyTime;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_Sale_Order.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
/// <summary>
/// Backgroundworker Main Function
/// </summary>
/// <param name="sender">Backgroundworker Instanz</param>
/// <param name="e">Backgroundworker Argumente</param>
void worker_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bgworker = sender as BackgroundWorker;
Encoding enc = Encoding.GetEncoding("iso-8859-1");
int[] CoulumnsToDelete;
if (Import.ExpKmpgColumns == true)
{
CoulumnsToDelete = new int[]
{
33, 32, 31, 29, 25, 23, 22, 20, 19, 17, 16, 15, 13, 12, 11, 9, 7, 6, 5, 4, 2, 1
};
}
else
{
CoulumnsToDelete = new int[]
{
35, 34, 33, 32, 31, 29, 25, 23, 22, 20, 19, 17, 16, 15, 13, 12, 11, 9, 7, 6, 5, 4, 2, 1
};
}
int[] IndexToRename = new int[]
{
1, 2, 3, 5, 6, 8, 10, 4
};
string[] ColumnNames = new string[]
{
"Buchungstyp",
"Filiale",
"Warengruppe",
"Bezeichnung",
"Summe",
"Eingabe Artikel Nr. EAN",
"Einzelpreis",
"Buchungs Datum"
};
List <string[]> ErrStrLst = new List <string[]>();
List <CSVImportModel> recList;
List <string> HeaderList = new List <string>();
bgworker.ReportProgress(iProgress = 3, "Starten des Datenimport. Einlesen der CSV Datei ...");
if (!CSVImportReadFile(out HeaderList, out recList) || bgworker.CancellationPending)
{
e.Cancel = true;
}
//Fehler, wenn keine Daten gelesen wurden
if (recList == null || recList.Count == 0)
{
bgworker.ReportProgress(100, "Fehler beim Daten Extrahieren");
if (recList != null)
{
recList.Clear();
recList = null;
}
if (HeaderList != null)
{
HeaderList.Clear();
HeaderList = null;
}
e.Cancel = true; //bgw abruch signalisieren
}
if (!e.Cancel && !bgworker.CancellationPending)
{
bgworker.ReportProgress(iProgress += 7, "Filialen Extrahieren und sortieren");
List <string> Filialen = GetFilialListe(recList);
List <CSVImportModel> FilialenExport = new List <CSVImportModel>();
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("de-DE");
using (var workbook = new XLWorkbook(new LoadOptions()
{
EventTracking = XLEventTracking.Enabled
}))
{
int fi = 1;
IXLWorksheet worksheet = null;
//foreach (var filiale in Filialen)
{
{
bgworker.ReportProgress(iProgress++, "Export zu Excel");
if (!import.OneSheetOnly)
{
foreach (var filiale in Filialen)
{
FilialenExport = GetFilialDataForExport(recList, filiale);
if (FilialenExport != null && FilialenExport.Count > 0)
{
bgworker.ReportProgress(iProgress++, $"Filtern der Daten für Filiale {filiale} - {fi}/{Filialen.Count()}");
worksheet = workbook.Worksheets.Add(filiale);
if (worksheet is null)
{
bgworker.ReportProgress(iProgress++, $"Fehler beim anlegen der Excel Daten : {filiale}");
//e.Cancel = true;
//CleanupLists(ref recList, ref Filialen, ref FilialenExport, ref HeaderList);
//return;
//break;
continue;
}
SaveFilialDataToWorksheet(HeaderList, FilialenExport, worksheet);
bgworker.ReportProgress(iProgress++, $"Anpassen der Exportierten Daten: {filiale} - {fi} / {Filialen.Count()}");
if (DeleteUnusedColoumns(worksheet, CoulumnsToDelete))
{
RenameCoulumns(worksheet, IndexToRename, ColumnNames);
SortAndFormatXlsSheet(worksheet);
}
}
else if (FilialenExport != null && FilialenExport.Count == 0)
{
bgworker.ReportProgress(iProgress++, $"Keine Daten für Filiale {filiale} für Export gefunden");
}
}
if (workbook != null)
{
SaveWorkBookToFile(e, bgworker, enc, ErrStrLst, HeaderList, workbook);
}
CleanupLists(ref recList, ref Filialen, ref FilialenExport, ref HeaderList);
return;
}
if (import.OneSheetOnly)
{
worksheet = workbook.Worksheets.Add("Datenexport");
}
for (int i = 0; i < Filialen.Count; i++)
{
FilialenExport.AddRange(GetFilialDataForExport(recList, Filialen[i]));
}
if (FilialenExport == null || FilialenExport.Count == 0)
{
bgworker.ReportProgress(iProgress++, $"Filtern der Daten für Filialen");
CleanupLists(ref recList, ref Filialen, ref FilialenExport, ref HeaderList);
e.Cancel = true;
return;
}
SaveFilialDataToWorksheet(HeaderList, FilialenExport, worksheet);
//bgworker.ReportProgress(iProgress++, $"Anpassen der Exportierten Daten: {filiale} - {fi} / {Filialen.Count()}");
if (DeleteUnusedColoumns(worksheet, CoulumnsToDelete))
{
RenameCoulumns(worksheet, IndexToRename, ColumnNames);
SortAndFormatXlsSheet(worksheet);
}
FilialenExport.Clear();
//worksheet = null;
fi++;
}
//if (FilialenExport != null && FilialenExport.Count == 0)
//{
// bgworker.ReportProgress(iProgress++, $"Keine Daten für Filiale {filiale} für Export gefunden");
//}
//else
//{
// e.Cancel = true;
// //break;
//}
if (e.Cancel || bgworker.CancellationPending)
{
e.Cancel = true;
SaveWorkBookToFile(e, bgworker, enc, ErrStrLst, HeaderList, workbook);
return;
//break;
}
}
/*
* Aufräumen der Objecte und freigeben von Speicher
*/
CleanupLists(ref recList, ref Filialen, ref FilialenExport, ref HeaderList);
if (workbook != null)
{
SaveWorkBookToFile(e, bgworker, enc, ErrStrLst, HeaderList, workbook);
}
}
}
ErrStrLst.Clear();
ErrStrLst = null;
}
public Boolean TryGetWorksheet(String name, out IXLWorksheet worksheet)
{
return(Worksheets.TryGetWorksheet(name, out worksheet));
}
private static void SetPhasesData(IXLWorksheet ws, List<PhaseModel> phases, int currentRow, ref int currentColumn)
{
foreach (var phase in phases)
{
ws.Cell(currentRow, currentColumn).Value = phase.Percentage;
currentColumn++;
}
}
public void AddWorksheet(IXLWorksheet worksheet)
{
worksheet.CopyTo(this, worksheet.Name);
}
private static void SetRows(IXLWorksheet ws)
{
ws.Cell(10, 1).Value = Model.NameOfMaterial;
ws.Cell(20, 43).Value = Model.NameOfMaterial;
var rowCount = Model.ResultTemperaturesModels.Count;
if (rowCount > 1)
{
ws.Row(20).InsertRowsBelow(rowCount - 1);
ws.Range(20, 43, 19 + rowCount, 44).Merge();
ws.Row(10).InsertRowsBelow(rowCount - 1);
ws.Range(10, 1, 9 + rowCount, 1).Merge();
}
}
private void createAllDataWorkheet(IXLWorksheet worksheet)
{
worksheet.Row(1).Style.Alignment.WrapText = true;
worksheet.Row(1).Height = 15;
worksheet.Row(1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
worksheet.Row(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Row(1).Style.Font.Bold = true;
worksheet.Row(2).Style.Alignment.WrapText = true;
worksheet.Row(2).Height = 31;
worksheet.Row(2).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
worksheet.Row(2).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Row(2).Style.Font.Bold = true;
worksheet.Column("A").Width = 13;
worksheet.Column("B").Width = 13;
worksheet.Column("C").Width = 13;
worksheet.Cell(2, 1).Value = "DATE";
addRightBottomBorder(worksheet.Cell(2, 1));
worksheet.Cell(2, 2).Value = "TIME";
addRightBottomBorder(worksheet.Cell(2, 2));
worksheet.Cell(2, 3).Value = "DURATION";
addRightBottomBorder(worksheet.Cell(2, 3));
worksheet.Cell(2, 4).Value = "LAeq";
addRightBottomBorder(worksheet.Cell(2, 4));
worksheet.Cell(2, 5).Value = "LAMax";
addRightBottomBorder(worksheet.Cell(2, 5));
worksheet.Cell(2, 6).Value = "LAMin";
addRightBottomBorder(worksheet.Cell(2, 6));
worksheet.Cell(2, 7).Value = "LZMax";
addRightBottomBorder(worksheet.Cell(2, 7));
worksheet.Cell(2, 8).Value = "LZMin";
addRightBottomBorder(worksheet.Cell(2, 8));
int oneThirdStart = 9;
int col = oneThirdStart;
Type oneThird = typeof(ReadingData.OctaveBandOneThird);
foreach (var propertyInfo in oneThird.GetProperties())
{
worksheet.Cell(2, col).Value = propertyInfo.Name.Replace("_", ".").Replace("Hz", "") + "Hz";
addRightBottomBorder(worksheet.Cell(2, col));
col++;
}
worksheet.Cell(1, oneThirdStart).Value = "1/3 Octave Band LZeq,t";
worksheet.Range(worksheet.Cell(1, oneThirdStart), worksheet.Cell(1, col - 1)).Merge();
int oneOneStart = col;
Type oneOne = typeof(ReadingData.OctaveBandOneOne);
foreach (var propertyInfo in oneOne.GetProperties())
{
worksheet.Cell(2, col).Value = propertyInfo.Name.Replace("_", ".").Replace("Hz", "") + "Hz";
addRightBottomBorder(worksheet.Cell(2, col));
col++;
}
worksheet.Cell(1, oneOneStart).Value = "1/1 Octave Band LZeq,t";
worksheet.Range(worksheet.Cell(1, oneOneStart), worksheet.Cell(1, col - 1)).Merge();
worksheet.Row(1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Row(1).Style.Border.BottomBorderColor = XLColor.Black;
int index = 3;
foreach (var r in readings.OrderBy(x => x.Time))
{
worksheet.Cell(index, 1).Value = r.Time.ToString("dd/MM/yyyy");
addRightBottomBorder(worksheet.Cell(index, 1));
string vale = r.Time.ToString("HH:mm:ss");
worksheet.Cell(index, 2).Value = r.Time.ToString("HH:mm:ss");
addRightBottomBorder(worksheet.Cell(index, 2));
worksheet.Cell(index, 3).Value = r.Major ? project.MajorInterval : project.MinorInterval;
addRightBottomBorder(worksheet.Cell(index, 3));
worksheet.Cell(index, 4).Value = oneDig(r.Data.LAeq);
addRightBottomBorder(worksheet.Cell(index, 4));
worksheet.Cell(index, 5).Value = oneDig(r.Data.LAMax);
addRightBottomBorder(worksheet.Cell(index, 5));
worksheet.Cell(index, 6).Value = oneDig(r.Data.LAMin);
addRightBottomBorder(worksheet.Cell(index, 6));
worksheet.Cell(index, 7).Value = oneDig(r.Data.LZMax);
addRightBottomBorder(worksheet.Cell(index, 7));
worksheet.Cell(index, 8).Value = oneDig(r.Data.LZMin);
addRightBottomBorder(worksheet.Cell(index, 8));
col = 9;
foreach (var propertyInfo in oneThird.GetProperties())
{
worksheet.Cell(index, col).Value = oneDig((Double)propertyInfo.GetValue(r.Data.LAeqOctaveBandOneThird));
addRightBottomBorder(worksheet.Cell(index, col));
col++;
}
foreach (var propertyInfo in oneOne.GetProperties())
{
worksheet.Cell(index, col).Value = oneDig((Double)propertyInfo.GetValue(r.Data.LAeqOctaveBandOneOne));
addRightBottomBorder(worksheet.Cell(index, col));
col++;
}
worksheet.Row(index).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
index++;
}
}
private static void SetSystemsPhasesHeader(IXLWorksheet ws)
{
var startColumnFirstSystemOrig = 6;
var startColumnFirstSystemCalc = 12;
var countPhases = 5;
var startColumnSecondSystemOrig = 22;
var startColumnSecondSystemCalc = 28;
var firstSystemPhasesCount = Model.FirstSystem.Phases.Count;
var secondSystemPhasesCount = Model.SecondSystem.Phases.Count;
var countToInsert = secondSystemPhasesCount - countPhases;
if (countToInsert > 0)
{
ws.Column(startColumnSecondSystemCalc).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnSecondSystemCalc, 8, startColumnSecondSystemCalc + countToInsert + countPhases - 1).Merge();
ws.Column(startColumnSecondSystemOrig).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnSecondSystemOrig, 8, startColumnSecondSystemOrig + countToInsert + countPhases - 1).Merge();
ws.Range(5, 20, 6, 35 + countToInsert + countToInsert).Merge();
}
countToInsert = firstSystemPhasesCount - countPhases;
if (countToInsert > 0)
{
ws.Column(startColumnFirstSystemCalc).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnFirstSystemCalc, 8, startColumnFirstSystemCalc + countToInsert + countPhases - 1).Merge();
ws.Column(startColumnFirstSystemOrig).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnFirstSystemOrig, 8, startColumnFirstSystemOrig + countToInsert + countPhases - 1).Merge();
ws.Range(5, 2, 6, 19 + countToInsert + countToInsert).Merge();
}
var firstSystemPhases = Model.FirstSystem.Phases.Select(x => x.Formula);
var secondSystemPhases = Model.SecondSystem.Phases.Select(x => x.Formula);
var currentCol = 6;
foreach (var phase in firstSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
currentCol++;
foreach (var phase in firstSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
currentCol += 5;
foreach (var phase in secondSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
currentCol++;
foreach (var phase in secondSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
}
public void FillOutGoingPerWeeks(List <DataStructsForPrintCalls.CallPerWeek> CallsPerWeek, bool DS = false) //2. нужно вдеть исходящие звонки которе сделаны всего один раз за 2 недели или за неделю( в зависимости от специфики)
{
worksheet = wbout.Worksheets.Add("Сделанные раз за 3,4 недели"); //2. нужно вдеть исходящие звонки которе сделаны всего один раз за 2 недели или за неделю( в зависимости от специфики)
//создадим заголовки у столбцов
int curCol = 1;
worksheet.Cell(1, curCol++).Value = "Клиент";
if (!DS)
{
worksheet.Cell(1, curCol++).Value = "3 неделя";
worksheet.Cell(1, curCol++).Value = "4 неделя";
}
else
{
worksheet.Cell(1, curCol++).Value = "60 дней";
}
worksheet.Cell(1, curCol++).Value = "Ответственный";
worksheet.Cell(1, curCol++).Value = "Примечание";
worksheet.Cell(1, curCol++).Value = "Примечание по CRM";
worksheet.Cell(1, curCol++).Value = "В работе или закрыт";
worksheet.Cell(1, curCol++).Value = "Дата назначенного контакта или дата закрытия сделки";
int curRow = 1;
foreach (DataStructsForPrintCalls.CallPerWeek phone in CallsPerWeek)
{
curRow++;
curCol = 1;
worksheet.Cell(curRow, curCol).Value = phone.phoneNumber;
worksheet.Cell(curRow, curCol++).Hyperlink = phone.Link;
worksheet.Cell(curRow, curCol++).Value = phone.FirstWeek;
if (!DS)
{
worksheet.Cell(curRow, curCol++).Value = phone.SecondWeek;
}
//worksheet.Cell(curRow, 4).Value = phone.ThirdWeek;
worksheet.Cell(curRow, curCol++).Value = phone.Manager;
worksheet.Cell(curRow, curCol).Value = phone.comment;
if (phone.SecondWeek == "-" && !DS)
{
worksheet.Cell(curRow, curCol).Style.Fill.BackgroundColor = XLColor.Red;
}
curCol++;
if (phone.DealState != "" && phone.DealState != null)
{
worksheet.Cell(curRow, curCol + 1).Value = phone.DealState;
worksheet.Cell(curRow, curCol + 1).Style.Font.Italic = true;
worksheet.Cell(curRow, curCol + 2).SetValue <string>(phone.DateDeal);
worksheet.Cell(curRow, curCol).Style.Font.FontColor = XLColor.Black;
worksheet.Cell(curRow, curCol++).Value = phone.NoticeCRM;
worksheet.Cell(curRow, curCol).Style.Font.Italic = true;
curCol++;
}
else
{
worksheet.Cell(curRow, curCol++).Value = phone.call.NoticeCRM;
if (phone.call.ClientState != "")
{
worksheet.Cell(curRow, curCol).Value = phone.call.ClientState;
worksheet.Cell(curRow, curCol).Style.Font.FontColor = XLColor.Red;
}
curCol++;
if (phone.call.StartDateAnalyze.Year > 2000)
{
worksheet.Cell(curRow, curCol).SetValue <string>(String.Format("{0:dd.MM.yyyy}", phone.call.StartDateAnalyze));
}
}
//worksheet.Cell(curRow, curCol).Style.NumberFormat.NumberFormatId = 14;
}
RangeSheets(curRow, curCol);
}
