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;
}
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 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 void TestParseTotalCells()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 7);
range.AddToNamed("Test", XLScope.Worksheet);
ws.Cell(1, 1).Value = "Plain text";
ws.Cell(1, 2).Value = "{Sum(di:Amount)}";
ws.Cell(1, 3).Value = "{ Custom(DI:Amount, CustomFunc) }";
ws.Cell(1, 4).Value = "{Min(di:Value, CustomFunc, PostFunc)}";
ws.Cell(1, 5).Value = "Text {count(di:Number)} {p:Text} {AVG( di:Value, , PostFunc )} {Text} Text {Max(di:Val)} {Min(val)}";
ws.Cell(1, 6).Value = "{Mix(di:Amount)}";
ws.Cell(1, 7).Value = "Text {Plain Text} Sum(di:Count) {sf:Format(Sum(di:Amount,,PostAggregation), #,,0.00)} {p:Text} {Max(di:Count)} {m:Meth(1, Avg( di : Value ), Min(di:Amount, CustomAggregation, PostAggregation), \"Str\"} {sv:RenderDate} m:Meth2(Avg(di:Value))";
ws.Cell(1, 8).Value = "{Sum(di:Amount)}";
var panel = new ExcelTotalsPanel("Stub", ws.NamedRange("Test"), report, report.TemplateProcessor);
MethodInfo method = panel.GetType().GetMethod("ParseTotalCells", BindingFlags.Instance | BindingFlags.NonPublic);
var result = (IDictionary <IXLCell, IList <ExcelTotalsPanel.ParsedAggregationFunc> >)method.Invoke(panel, null);
Assert.AreEqual(5, result.Count);
Assert.AreEqual("Plain text", ws.Cell(1, 1).Value);
Assert.AreEqual("{Mix(di:Amount)}", ws.Cell(1, 6).Value);
Assert.AreEqual("{Sum(di:Amount)}", ws.Cell(1, 8).Value);
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 2).Value.ToString(), @"{di:AggFunc_[0-9a-f]{32}}"));
Assert.AreEqual(1, result[ws.Cell(1, 2)].Count);
Assert.AreEqual(AggregateFunction.Sum, result[ws.Cell(1, 2)].First().AggregateFunction);
Assert.AreEqual("di:Amount", result[ws.Cell(1, 2)].First().ColumnName);
Assert.IsNull(result[ws.Cell(1, 2)].First().CustomFunc);
Assert.IsNull(result[ws.Cell(1, 2)].First().PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 2)].First().Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 2)].First().UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 3).Value.ToString(), @"{ di:AggFunc_[0-9a-f]{32} }"));
Assert.AreEqual(1, result[ws.Cell(1, 3)].Count);
Assert.AreEqual(AggregateFunction.Custom, result[ws.Cell(1, 3)].First().AggregateFunction);
Assert.AreEqual("DI:Amount", result[ws.Cell(1, 3)].First().ColumnName);
Assert.AreEqual("CustomFunc", result[ws.Cell(1, 3)].First().CustomFunc);
Assert.IsNull(result[ws.Cell(1, 3)].First().PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 3)].First().Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 3)].First().UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 4).Value.ToString(), @"{di:AggFunc_[0-9a-f]{32}}"));
Assert.AreEqual(1, result[ws.Cell(1, 4)].Count);
Assert.AreEqual(AggregateFunction.Min, result[ws.Cell(1, 4)].First().AggregateFunction);
Assert.AreEqual("di:Value", result[ws.Cell(1, 4)].First().ColumnName);
Assert.AreEqual("CustomFunc", result[ws.Cell(1, 4)].First().CustomFunc);
Assert.AreEqual("PostFunc", result[ws.Cell(1, 4)].First().PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 4)].First().Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 4)].First().UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 5).Value.ToString(), @"Text {di:AggFunc_[0-9a-f]{32}} {p:Text} {di:AggFunc_[0-9a-f]{32}} {Text} Text {di:AggFunc_[0-9a-f]{32}} {Min\(val\)}"));
Assert.AreEqual(3, result[ws.Cell(1, 5)].Count);
Assert.AreEqual(AggregateFunction.Count, result[ws.Cell(1, 5)][0].AggregateFunction);
Assert.AreEqual("di:Number", result[ws.Cell(1, 5)][0].ColumnName);
Assert.IsNull(result[ws.Cell(1, 5)][0].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 5)][0].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 5)][0].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 5)][0].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Avg, result[ws.Cell(1, 5)][1].AggregateFunction);
Assert.AreEqual("di:Value", result[ws.Cell(1, 5)][1].ColumnName);
Assert.IsNull(result[ws.Cell(1, 5)][1].CustomFunc);
Assert.AreEqual("PostFunc", result[ws.Cell(1, 5)][1].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 5)][1].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 5)][1].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Max, result[ws.Cell(1, 5)][2].AggregateFunction);
Assert.AreEqual("di:Val", result[ws.Cell(1, 5)][2].ColumnName);
Assert.IsNull(result[ws.Cell(1, 5)][2].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 5)][2].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 5)][2].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 5)][2].UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 7).Value.ToString(), @"Text {Plain Text} Sum\(di:Count\) {sf:Format\(di:AggFunc_[0-9a-f]{32}, #,,0.00\)} {p:Text} {di:AggFunc_[0-9a-f]{32}} {m:Meth\(1, di:AggFunc_[0-9a-f]{32}, di:AggFunc_[0-9a-f]{32}, ""Str""} {sv:RenderDate} m:Meth2\(Avg\(di:Value\)\)"));
Assert.AreEqual(4, result[ws.Cell(1, 7)].Count);
Assert.AreEqual(AggregateFunction.Sum, result[ws.Cell(1, 7)][0].AggregateFunction);
Assert.AreEqual("di:Amount", result[ws.Cell(1, 7)][0].ColumnName);
Assert.IsNull(result[ws.Cell(1, 7)][0].CustomFunc);
Assert.AreEqual("PostAggregation", result[ws.Cell(1, 7)][0].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][0].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][0].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Max, result[ws.Cell(1, 7)][1].AggregateFunction);
Assert.AreEqual("di:Count", result[ws.Cell(1, 7)][1].ColumnName);
Assert.IsNull(result[ws.Cell(1, 7)][1].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 7)][1].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][1].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][1].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Avg, result[ws.Cell(1, 7)][2].AggregateFunction);
Assert.AreEqual("di : Value", result[ws.Cell(1, 7)][2].ColumnName);
Assert.IsNull(result[ws.Cell(1, 7)][2].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 7)][2].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][2].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][2].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Min, result[ws.Cell(1, 7)][3].AggregateFunction);
Assert.AreEqual("di:Amount", result[ws.Cell(1, 7)][3].ColumnName);
Assert.AreEqual("CustomAggregation", result[ws.Cell(1, 7)][3].CustomFunc);
Assert.AreEqual("PostAggregation", result[ws.Cell(1, 7)][3].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][3].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][3].UniqueName, out _));
}
/// -----------------------------------------------------------------------------
/// <summary>
/// DataTableをもとにxlsxファイルを作成しPDF化</summary>
/// <param name="dtSetCd_B_Input">
/// メーカーのデータテーブル</param>
/// -----------------------------------------------------------------------------
public string dbToPdf(DataTable dtSetCd_B_Input)
{
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 = dtSetCd_B_Input.AsEnumerable()
.Select(dat => new
{
makerCd = (String)dat["メーカーコード"],
makerName = dat["メーカー名"],
}).ToList();
//リストをデータテーブルに変換
DataTable dtChkList = pdf.ConvertToDataTable(outDataAll);
int maxRowCnt = dtChkList.Rows.Count + 1;
int maxColCnt = dtChkList.Columns.Count;
int pageCnt = 0; // ページ(シート枚数)カウント
int rowCnt = 1; // datatable処理行カウント
int xlsRowCnt = 4; // Excel出力行カウント（開始は出力行）
int maxPage = 0; // 最大ページ数
//ページ数計算
double page = 1.0 * maxRowCnt / 44;
double decimalpart = page % 1;
if (decimalpart != 0)
{
//小数点以下が0でない場合、+1
maxPage = (int)Math.Floor(page) + 1;
}
else
{
maxPage = (int)page;
}
//ClosedXMLで1行ずつExcelに出力
foreach (DataRow drSiireCheak in dtChkList.Rows)
{
//1ページ目のシート作成
if (rowCnt == 1)
{
pageCnt++;
//タイトル出力（中央揃え、セル結合）
IXLCell titleCell = headersheet.Cell("B1");
titleCell.Value = "メーカーマスタリスト";
titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
titleCell.Style.Font.FontSize = 16;
//ヘッダー出力(表ヘッダー)
headersheet.Cell("A3").Value = "コード";
headersheet.Cell("B3").Value = "メーカー名";
//ヘッダー列
headersheet.Range("A3", "B3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// 列幅の指定
headersheet.Column(1).Width = 10;
headersheet.Column(2).Width = 38;
// セルの周囲に罫線を引く
headersheet.Range("A3", "B3").Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetBottomBorder(XLBorderStyleValues.Thin)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
// 印刷体裁（A4横、印刷範囲）
headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper;
headersheet.PageSetup.PageOrientation = XLPageOrientation.Default;
// ヘッダー部の指定（番号）
headersheet.PageSetup.Header.Left.AddText("（№102）");
//ヘッダーシートのコピー、ヘッダー部の指定
pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow);
}
// 1セルずつデータ出力
for (int colCnt = 1; colCnt <= maxColCnt; colCnt++)
{
string str = drSiireCheak[colCnt - 1].ToString();
//二桁の0パディングをさせる
currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("0000");
currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentsheet.Cell(xlsRowCnt, colCnt).Value = str;
}
// 1行分のセルの周囲に罫線を引く
currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 2).Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetBottomBorder(XLBorderStyleValues.Thin)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
// 47行毎（ヘッダーを除いた行数）にシート作成
if (xlsRowCnt == 47)
{
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 (Exception ex)
{
throw;
}
finally
{
// Workフォルダの全ファイルを取得
string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories);
// Workフォルダ内のファイル削除
foreach (string filepath in files)
{
//File.Delete(filepath);
}
}
}
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;
}
}
private void btnExportExcel_Click(object sender, RoutedEventArgs e)
{
DateTime second = new DateTime(Convert.ToInt32(date2.SelectedDate.Value.ToString("yyyy")), Convert.ToInt32(date2.SelectedDate.Value.ToString("MM")), Convert.ToInt32(date2.SelectedDate.Value.ToString("dd")), 23, 59, 59);
try
{
if (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 = "Отчет приход продукта с " + date1.SelectedDate + " до " + second;
worksheet.Cell(1, 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Range(worksheet.Cell(1, 1), worksheet.Cell(1, 10)).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 = "Цена зак.";
worksheet.Cell(2, 6).Value = "Цена про.";
worksheet.Cell(2, 7).Value = "Приход";
worksheet.Cell(2, 8).Value = "Остаток";
worksheet.Cell(2, 9).Value = "Поставщик";
worksheet.Cell(2, 10).Value = "Дата";
int kk = 3;
foreach (InfoPartiyaKirim rv in DG.Items)
{
worksheet.Cell(kk, 1).Value = rv.Shtrix.ToString();
worksheet.Cell(kk, 2).Value = rv.TovarNomi;
worksheet.Cell(kk, 3).Value = rv.TovarTuri;
worksheet.Cell(kk, 4).Value = rv.TovarMassaTuri;
worksheet.Cell(kk, 5).Value = rv.BazaviyNarxi;
worksheet.Cell(kk, 6).Value = rv.SotishNarxi;
worksheet.Cell(kk, 7).Value = rv.OlibKelingan;
worksheet.Cell(kk, 8).Value = rv.Qolgan;
worksheet.Cell(kk, 9).Value = rv.YetkazibBeruvchi;
worksheet.Cell(kk, 10).Value = rv.Chislo;
kk++;
}
worksheet.Columns("A", "Z").AdjustToContents();
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("Error12");
}
}
static void OutputTitleLine(IXLWorksheet sheet,
ref int nRowIndex,
string strTitle,
XLColor text_color,
int nStartIndex,
int nColumnCount)
{
// 读者序号
int nColIndex = nStartIndex;
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(strTitle);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = text_color;
//cell.Style.Font.FontName = strFontName;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// cell.Style.Fill.BackgroundColor = XLColor.LightGray;
nColIndex++;
}
// 合并格子
{
var rngData = sheet.Range(nRowIndex, nStartIndex, nRowIndex, nStartIndex + nColumnCount - 1);
rngData.Merge();
// rngData.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Hair;
}
nRowIndex++;
}
private void prepDashboardSheets(IXLWorksheet sheet)
{
/*.
* can use htis for both types of sheets.
* Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name)
* Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one)
*/
_worksheet = sheet;
//use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly
Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>();
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value != null)
{
foreach (string provider in providers)
{
if (value.Contains(provider))
{
if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets
{
newRow = _worksheet_range.Row(i + 3);
newRow.InsertRowsBelow(1);
var blankRow = _worksheet_range.Row(i + 4);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive
{
newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above
newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row
var blankRow = _worksheet_range.Row(i + 3);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead
}
}
}
}
MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc.
}
}
}
private void DrawBorder(IXLWorksheet ws, IEnumerable<Tuple<string, string>> allKeys, int langCount)
{
var range = ws.Range(DataRow, DataColumn, DataRow + allKeys.Count() - 1, DataColumn + langCount - 1);
range.Style.Border.SetRightBorder(DottedBorder)
.Border.SetBottomBorder(DottedBorder);
// Namespaceの区切りごとに直線を引く
// プロパティ名完全一致の項目同士でグルーピング
var groupedByNS = allKeys.GroupBy(o => o.Item1);
var pos = DataRow;
foreach (var item in groupedByNS)
{
var count = item.Count();
pos += count;
ws.Range(pos, OffsetColumn, pos, OffsetColumn + 2 + langCount - 1)
.Style.Border.SetTopBorder(NamespaceSeparatorBorder);
}
}
public IXLRange GetRange(IXLAddress startAddr, IXLAddress endAddr)
{
return(_sheet.Range(startAddr, endAddr));
}
private void CreateHeader(IXLWorksheet ws, int langCount)
{
// Namespace/Keyのヘッダを装飾
var nsRange = ws.Range(OffsetRow, OffsetColumn, OffsetRow + 1, OffsetColumn + 1);
nsRange.Style.Border.SetBottomBorder(MidBorder);
ws.Cell(OffsetRow + 1, OffsetRow).Value = "Namespace";
ws.Cell(OffsetRow + 1, OffsetColumn + 1).Value = "Key";
// 言語のヘッダを装飾
var langRange = ws.Range(OffsetRow, DataColumn, OffsetRow + 1, DataColumn + langCount - 1);
langRange.Style.Border.SetBottomBorder(MidBorder)
.Border.SetOutsideBorder(MidBorder);
ws.Range(OffsetRow, DataColumn, OffsetRow, DataColumn + langCount - 1)
.Merge()
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell(OffsetRow, DataColumn).Value = "Languages";
ws.Range(LanguageTitleRow, DataColumn, LanguageTitleRow, DataColumn + langCount - 1)
.Style.Border.SetRightBorder(DottedBorder);
// ヘッダ全体を装飾
var range = ws.Range(OffsetRow, OffsetColumn, LanguageTitleRow, DataColumn + langCount - 1);
range.Style.Fill.SetBackgroundColor(XLColor.LightBlue)
.Border.SetBottomBorder(MidBorder)
.Border.SetOutsideBorder(MidBorder)
.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
private void CreateKeyColumns(IXLWorksheet ws, IEnumerable<Tuple<string, string>> allKeys)
{
// プロパティ名などの列を作成
var keysColumn = ws.Cell(DataRow, OffsetColumn);
keysColumn.Value = allKeys.Select(o => new { o.Item1, o.Item2 });
// プロパティ名の装飾
var propRange = ws.Range(DataRow, OffsetColumn, DataRow + allKeys.Count() - 1, 2);
propRange.Style.Fill.SetBackgroundColor(XLColor.LightYellow)
.Border.SetOutsideBorder(MidBorder);
}
void OutputOverdues(IXLWorksheet sheet,
XmlDocument dom,
ref int nRowIndex,
ref List<int> column_max_chars)
{
XmlNodeList nodes = dom.DocumentElement.SelectNodes("overdues/overdue");
if (nodes.Count == 0)
return;
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 费用 --- " + nodes.Count,
XLColor.DarkRed,
2,
6);
int nRet = 0;
List<IXLCell> cells = new List<IXLCell>();
// 栏目标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("说明");
titles.Add("金额");
titles.Add("ID");
#if NO
titles.Add("以停代金情况");
titles.Add("起点日期");
titles.Add("期限");
titles.Add("终点日期");
#endif
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
int nItemIndex = 0;
foreach (XmlElement borrow in nodes)
{
string strItemBarcode = borrow.GetAttribute("barcode");
string strReason = borrow.GetAttribute("reason");
string strPrice = borrow.GetAttribute("price");
string strID = borrow.GetAttribute("id");
string strRecPath = borrow.GetAttribute("recPath");
string strSummary = borrow.GetAttribute("summary");
if (string.IsNullOrEmpty(strItemBarcode) == false
&& string.IsNullOrEmpty(strSummary) == true)
{
string strError = "";
nRet = this.MainForm.GetBiblioSummary(strItemBarcode,
strRecPath, // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
}
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strReason);
cols.Add(strPrice);
cols.Add(strID);
int nColIndex = 2;
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
nColIndex++;
cells.Add(cell);
}
nItemIndex++;
nRowIndex++;
}
// 标题行下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
sheet.Rows(nStartRow + 1, nRowIndex - 1).Group();
}
void OutputBorrows(IXLWorksheet sheet,
XmlDocument dom,
ref int nRowIndex,
ref List<int> column_max_chars)
{
XmlNodeList nodes = dom.DocumentElement.SelectNodes("borrows/borrow");
if (nodes.Count == 0)
return;
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 在借 --- " + nodes.Count,
XLColor.DarkGreen,
2,
7);
List<IXLCell> cells = new List<IXLCell>();
// 册信息若干行的标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("借阅时间");
titles.Add("借期");
titles.Add("应还时间");
titles.Add("是否超期");
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
int nItemIndex = 0;
foreach (XmlElement borrow in nodes)
{
string strItemBarcode = borrow.GetAttribute("barcode");
string strBorrowDate = ToLocalTime(borrow.GetAttribute("borrowDate"), "yyyy-MM-dd HH:mm");
string strBorrowPeriod = GetDisplayTimePeriodString(borrow.GetAttribute("borrowPeriod"));
string strReturningDate = ToLocalTime(borrow.GetAttribute("returningDate"), "yyyy-MM-dd");
string strRecPath = borrow.GetAttribute("recPath");
string strIsOverdue = borrow.GetAttribute("isOverdue");
bool bIsOverdue = DomUtil.IsBooleanTrue(strIsOverdue, false);
string strOverdueInfo = borrow.GetAttribute("overdueInfo1");
string strSummary = borrow.GetAttribute("summary");
#if NO
nRet = this.MainForm.GetBiblioSummary(strItemBarcode,
strRecPath, // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
#endif
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strBorrowDate);
cols.Add(strBorrowPeriod);
cols.Add(strReturningDate);
if (bIsOverdue)
cols.Add(strOverdueInfo);
else
cols.Add("");
int nColIndex = 2;
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
// 超期的行为黄色背景
if (bIsOverdue)
{
var line = sheet.Range(nRowIndex, 2, nRowIndex, 2 + cols.Count - 1);
line.Style.Fill.BackgroundColor = XLColor.Yellow;
}
nItemIndex++;
nRowIndex++;
}
// 册信息标题下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
#if NO
// 第一行上面的横线
rngData = sheet.Range(cell_no, cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Medium;
#endif
sheet.Rows(nStartRow + 1, nRowIndex-1).Group();
}
void OutputReaderInfo(IXLWorksheet sheet,
XmlDocument dom,
int nReaderIndex,
ref int nRowIndex,
ref List<int> column_max_chars)
{
string strReaderBarcode = DomUtil.GetElementText(dom.DocumentElement,
"barcode");
string strName = DomUtil.GetElementText(dom.DocumentElement,
"name");
string strDepartment = DomUtil.GetElementText(dom.DocumentElement,
"department");
string strState = DomUtil.GetElementText(dom.DocumentElement,
"state");
string strCreateDate = ToLocalTime(DomUtil.GetElementText(dom.DocumentElement,
"createDate"), "yyyy/MM/dd");
string strExpireDate = ToLocalTime(DomUtil.GetElementText(dom.DocumentElement,
"expireDate"), "yyyy/MM/dd");
string strReaderType = DomUtil.GetElementText(dom.DocumentElement,
"readerType");
string strComment = DomUtil.GetElementText(dom.DocumentElement,
"comment");
List<IXLCell> cells = new List<IXLCell>();
// 读者序号
// IXLCell cell_no = null;
int nColIndex = 2;
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nReaderIndex + 1);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontSize = 20;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
// cell.Style.Fill.BackgroundColor = XLColor.LightGray;
cells.Add(cell);
// cell_no = cell;
nColIndex++;
}
// 最大字符数
SetMaxChars(ref column_max_chars, 1, (nReaderIndex + 1).ToString().Length * 2);
// 序号的右边竖线
{
var rngData = sheet.Range(nRowIndex, 2, nRowIndex + 3, 2);
rngData.Merge();
rngData.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Hair;
// 第一行上面的横线
rngData = sheet.Range(nRowIndex, 2, nRowIndex, 2 + 7 - 1);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Medium;
}
#if NO
// 特殊状态时的整个底色
if (string.IsNullOrEmpty(strState) == false)
{
var rngData = sheet.Range(nRowIndex, 2, nRowIndex + 3, 2 + 7 - 1);
rngData.Style.Fill.BackgroundColor = XLColor.LightBrown;
}
#endif
int nFirstRow = nRowIndex;
{
List<string> subtitles = new List<string>();
subtitles.Add("姓名");
subtitles.Add("证条码号");
subtitles.Add("部门");
subtitles.Add("联系方式");
List<string> subcols = new List<string>();
subcols.Add(strName);
subcols.Add(strReaderBarcode);
subcols.Add(strDepartment);
subcols.Add(GetContactString(dom));
for (int line = 0; line < subtitles.Count; line++)
{
nColIndex = 3;
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(subtitles[line]);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
// cell.Style.Fill.BackgroundColor = XLColor.LightGray;
nColIndex++;
cells.Add(cell);
}
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(subcols[line]);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
if (line == 0)
{
cell.Style.Font.FontName = "微软雅黑";
cell.Style.Font.FontSize = 20;
}
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
//
//var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
//rngData.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
}
nRowIndex = nFirstRow;
{
List<string> subtitles = new List<string>();
subtitles.Add("状态");
subtitles.Add("有效期");
subtitles.Add("读者类别");
subtitles.Add("注释");
List<string> subcols = new List<string>();
subcols.Add(strState);
subcols.Add(strCreateDate+"~"+strExpireDate);
subcols.Add(strReaderType);
subcols.Add(strComment);
for (int line = 0; line < subtitles.Count; line++)
{
nColIndex = 7;
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(subtitles[line]);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
// cell.Style.Fill.BackgroundColor = XLColor.LightGray;
nColIndex++;
cells.Add(cell);
}
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(subcols[line]);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
if (line == 0)
{
cell.Style.Font.FontName = "微软雅黑";
cell.Style.Font.FontSize = 20;
if (string.IsNullOrEmpty(strState) == false)
{
cell.Style.Font.FontColor = XLColor.White;
cell.Style.Fill.BackgroundColor = XLColor.DarkRed;
}
}
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
}
}
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++;
}
}
public void GenerarExcel_Cero_a_DosAnios()
{
Console.WriteLine("INICIO");
Estudiante objEstudiante = new Estudiante();
List <Estudiante> objListEstudaintes = objEstudiante.GetEstudiantes();
Areas objAreas = new Areas();
List <Areas> objListaAreas = objAreas.GetAreas();
//---------------- crear u obtener la plantilla que con la que se va a trabajar --------------
string pathServerTempPlantilla = GetPlantilla();
//----------------- Trabajar con la plantilla seleccionada --------------------------------
using (XLWorkbook workbook = new XLWorkbook(pathServerTempPlantilla))
{
//------- GENERALIDADES: Cargando los datos a la pestaña Generalidades
IXLWorksheet workSheetGeneral = workbook.Worksheets.Where(x => x.Name == "Generalidades").FirstOrDefault();
workSheetGeneral.Cell("E5").Value = "0567420" + "-" + "0"; //entity.Perfil.IdInstitucion + entity.Perfil.Anexo;
workSheetGeneral.Cell("H5").Value = "Primaria"; //entity.Perfil.IdNivelInstitucion;
workSheetGeneral.Cell("C6").Value = "JESUS"; //entity.Perfil.NombreInstitucion.Replace("'", "");
workSheetGeneral.Cell("D8").Value = "2018"; // entity.AnioAcademico;
workSheetGeneral.Cell("D9").Value = "CURRICULA NACIONAL 2017"; //entity.DisenioCurricular;
workSheetGeneral.Cell("C10").Value = "PRIMERO"; //entity.DescGradoIe;
workSheetGeneral.Cell("F10").Value = "A"; //entity.DescSeccionIe;
//-------- GENERALIDADES:Cargando las areas en la pestaña Generalidades
workSheetGeneral.Cell("B12").Value = "AREAS";
int nroRowArea = 14;
foreach (var area in objListaAreas)
{
workSheetGeneral.Cell(nroRowArea, 2).Value = area.AbrArea;
workSheetGeneral.Cell(nroRowArea, 3).Value = area.DescArea;
nroRowArea++;
}
//-------- Agregando hojas de trabajo por Area
foreach (var area in objListaAreas)
{
//--------Agregamos la hoja de trabajo
IXLWorksheet worksheet = workbook.Worksheets.Add(area.AbrArea);
//-------- HEAD: Generamos la cabecera parte 1: datos sin notas
worksheet.Cell("A1").Value = "ID";
worksheet.Cell("B1").Value = "CodEstudiante";
worksheet.Cell("C1").Value = "Nombres";
//-------- HEAD: Generamos la cabecera parte 2: datos con notas
worksheet.Cell("D1").Value = "01 - Descripción del nivel de logro alcanzado por el niño(a)";
worksheet.Cell("E1").Value = "02 - Descripción del nivel de logro alcanzado por el niño(a)";
worksheet.Cell("F1").Value = "03 - Descripción del nivel de logro alcanzado por el niño(a)";
//--------HEAD: Le damos el formato a la cabacera -------------------------
IXLRange rango = worksheet.Range("A1:F1");
rango.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rango.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
rango.Style.Border.BottomBorderColor = XLColor.Black;
rango.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rango.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
rango.Style.Fill.BackgroundColor = XLColor.FromArgb(180, 180, 180);
//rango.Style.Font.FontName = "Courier New";
//rango.Style.Alignment.WrapText = true;
//--------BODY: Generar la tabla de estudiantes con sus notas ----------------------
int nroRow = 2;
foreach (var estudiante in objListEstudaintes)
{
worksheet.Cell(nroRow, 1).Value = estudiante.Id;
worksheet.Cell(nroRow, 2).Style.NumberFormat.Format = "@";
worksheet.Cell(nroRow, 2).Value = estudiante.CodEstudiante.ToString();
worksheet.Cell(nroRow, 3).Value = estudiante.Nombres;
nroRow++;
}
////--------BODY: Data Validation -------------------------------------------------------
//var options = new List<string>() { "AD", "A", "B", "C" };
//var validOptions = $"\"{String.Join(",", options)}\"";
//nroRow = 2;
//foreach (var estudiante in objListEstudaintes)
//{
// worksheet.Cell(nroRow, 4).DataValidation.List(validOptions, true);
// worksheet.Cell(nroRow, 5).DataValidation.List(validOptions, true);
// worksheet.Cell(nroRow, 6).DataValidation.List(validOptions, true);
// worksheet.Cell(nroRow, 7).DataValidation.List(validOptions, true);
// worksheet.Cell(nroRow, 8).DataValidation.List(validOptions, true);
// nroRow++;
//}
//-------FOOTER: Leyenda --------------------------------------------------------
worksheet.Cell(nroRow + 2, 2).Value = "LEYENDA";
//worksheet.Cell(nroRow + 3, 3).Value = new[]
//{
// "01 = Se comunica oralmente en su lengua materna",
// "02 = Lee diversos tipos de texto en su lengua materna lengua materna",
// "03 = Escribe diversos tipos de textos en su lengua materna",
// "04 = Crea Proyectos desde los lenguajes del arte"
//};
worksheet.Cell(nroRow + 3, 2).Value = "01 = Se comunica oralmente en su lengua materna";
worksheet.Range(nroRow + 3, 2, nroRow + 3, 3).Merge();
worksheet.Cell(nroRow + 4, 2).Value = "02 = Lee diversos tipos de texto en su lengua materna lengua materna";
worksheet.Range(nroRow + 4, 2, nroRow + 4, 3).Merge();
worksheet.Cell(nroRow + 5, 2).Value = "03 = Escribe diversos tipos de textos en su lengua materna";
worksheet.Range(nroRow + 5, 2, nroRow + 5, 3).Merge();
//---------------------------- Le damos formato a la tabla en general --------------------------
worksheet.Columns(1, 9).AdjustToContents(); // Ajustamos el ancho de las columnas para que se muestren todos los contenidos
//worksheet.Column(9).AdjustToContents(); // Ajustamos el ancho de una columna de acuerdo a su contenido
worksheet.Column(9).Width = 100;
worksheet.Columns(4, 8).Width = 6;
}
Console.WriteLine("ANTES DE GUARDAR");
workbook.SaveAs(pathServerTempPlantilla);
Console.WriteLine("DESPUES DE GUARDAR");
}
Console.WriteLine("FIN");
Console.ReadLine();
}
// 构造表格标题和标题行
int BuildTextPageTop(PrintOption option,
Hashtable macro_table,
StreamWriter sw,
// ref ExcelDocument doc
IXLWorksheet sheet
)
{
// 表格标题
string strTableTitleText = option.TableTitle;
if (String.IsNullOrEmpty(strTableTitleText) == false)
{
strTableTitleText = StringUtil.MacroString(macro_table,
strTableTitleText);
if (sw != null)
{
sw.WriteLine(strTableTitleText);
sw.WriteLine("");
}
if (sheet != null)
{
#if NO
doc.WriteExcelTitle(0,
option.Columns.Count, // nTitleCols,
strTableTitleText,
6);
#endif
var header = sheet.Range(1, 1,
1, option.Columns.Count).Merge();
header.Value = strTableTitleText;
header.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// header.Style.Font.FontName = "微软雅黑";
header.Style.Font.Bold = true;
header.Style.Font.FontSize = 16;
}
}
string strColumnTitleLine = "";
List<int> widths = new List<int>();
for (int i = 0; i < option.Columns.Count; i++)
{
Column column = option.Columns[i];
widths.Add(column.WidthChars);
string strCaption = column.Caption;
// 如果没有caption定义，就挪用name定义
if (String.IsNullOrEmpty(strCaption) == true)
strCaption = column.Name;
// string strClass = Global.GetLeft(column.Name);
if (i != 0)
strColumnTitleLine += "\t";
strColumnTitleLine += strCaption;
if (sheet != null)
{
#if NO
doc.WriteExcelCell(
2,
i,
strCaption,
true);
#endif
var cell = sheet.Cell(2+1, i+1);
cell.Value = strCaption;
// cell.Style.Font.FontName = "微软雅黑";
cell.Style.Font.Bold = true;
if (column.WidthChars != -1)
sheet.Column(i + 1).Width = column.WidthChars;
}
}
if (sw != null)
sw.WriteLine(strColumnTitleLine);
#if NO
if (doc != null)
SetColumnWidth(doc, widths);
#endif
return 0;
}
public void WriteReportLines(IXLWorksheet ws, Tuple <int, List <ProductReportModel> > reportData)
{
int writingRow = reportData.Item1;
List <ProductReportModel> productReportModel = reportData.Item2;
DateTime currentInvoiceDate = productReportModel[0].Data;
string currentInvoiceNumber = productReportModel[0].Numero;
ws.Cell(writingRow++, 1).RichText
.AddText("Fatt." + currentInvoiceNumber + " del " + currentInvoiceDate.ToString("dd/MM/yyyy"))
.SetItalic()
.SetFontSize(10);
ws.Range("A7:G7").Style.Border.TopBorder = XLBorderStyleValues.Thick;
var colA = ws.Column("A");
colA.Width = 30;
var colBD = ws.Columns("B:D");
colBD.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
var colE = ws.Column("E");
colE.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
var colF = ws.Column("F");
colF.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colF.AdjustToContents();
var colG = ws.Column("G");
colG.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colG.AdjustToContents();
var rangeValues = ws.Range("B1:B4");
rangeValues.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
foreach (var p in productReportModel)
{
if (p.Data != currentInvoiceDate || p.Numero != currentInvoiceNumber)
{
currentInvoiceDate = p.Data;
currentInvoiceNumber = p.Numero;
ws.Cell(writingRow, 1).RichText
.AddText("Fatt." + currentInvoiceNumber + " del " + currentInvoiceDate.ToString("dd/MM/yyyy"))
.SetItalic()
.SetFontSize(10);
ws.Range("A" + writingRow + ":G" + writingRow).Style.Border.TopBorder = XLBorderStyleValues.Thick;
writingRow++;
}
ws.Cell(writingRow, 1).SetValue(p.Descrizione);
ws.Cell(writingRow, 2).SetValue(p.UnitaMisura);
ws.Cell(writingRow, 3).SetValue(p.Quantita);
ws.Cell(writingRow, 4).SetValue(p.PrezzoUnitario);
ws.Cell(writingRow, 5).SetValue(p.PrezzoTotale);
if (p.DataDDT != DateTime.MinValue)
{
ws.Cell(writingRow, 6).SetValue(p.NumeroDDT);
ws.Cell(writingRow, 7).SetValue(p.DataDDT.ToString("dd/MM/yyyy"));
}
ws.Range("A" + writingRow + ":G" + writingRow).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
writingRow++;
}
}
public void GenerarExcel_Segundo_a_QuintoDeSecundaria()
{
Console.WriteLine("INICIO");
Estudiante objEstudiante = new Estudiante();
List <Estudiante> objListEstudaintes = objEstudiante.GetEstudiantes();
Areas objAreas = new Areas();
List <Areas> objListaAreas = objAreas.GetAreas();
//---------------- crear u obtener la plantilla que con la que se va a trabajar --------------
string pathServerTempPlantilla = GetPlantilla();
//----------------- Trabajar con la plantilla seleccionada --------------------------------
using (XLWorkbook workbook = new XLWorkbook(pathServerTempPlantilla))
{
//------- GENERALIDADES: Cargando los datos a la pestaña Generalidades
IXLWorksheet workSheetGeneral = workbook.Worksheets.Where(x => x.Name == "Generalidades").FirstOrDefault();
workSheetGeneral.Cell("E5").Value = "0567420" + "-" + "0"; //entity.Perfil.IdInstitucion + entity.Perfil.Anexo;
workSheetGeneral.Cell("H5").Value = "Primaria"; //entity.Perfil.IdNivelInstitucion;
workSheetGeneral.Cell("C6").Value = "JESUS"; //entity.Perfil.NombreInstitucion.Replace("'", "");
workSheetGeneral.Cell("D8").Value = "2018"; // entity.AnioAcademico;
workSheetGeneral.Cell("D9").Value = "CURRICULA NACIONAL 2017"; //entity.DisenioCurricular;
workSheetGeneral.Cell("C10").Value = "PRIMERO"; //entity.DescGradoIe;
workSheetGeneral.Cell("F10").Value = "A"; //entity.DescSeccionIe;
//-------- GENERALIDADES:Cargando las areas en la pestaña Generalidades
workSheetGeneral.Cell("B12").Value = "AREAS";
int nroRowArea = 14;
foreach (var area in objListaAreas)
{
workSheetGeneral.Cell(nroRowArea, 2).Value = area.AbrArea;
workSheetGeneral.Cell(nroRowArea, 3).Value = area.DescArea;
nroRowArea++;
}
//--------Agregamos la hoja de trabajo
IXLWorksheet worksheet = workbook.Worksheets.Add("NF");
//-------- HEAD: Generar la tabla de estudiantes con sus notas --------------------
int nroRowHead = 1;
int nroColHead = 3;
worksheet.Cell(nroRowHead, 1).Value = "ID";
worksheet.Cell(nroRowHead, 2).Value = "CodEstudiante";
worksheet.Cell(nroRowHead, 3).Value = "Nombres";
foreach (var area in objListaAreas)
{
nroColHead++;
worksheet.Cell(nroRowHead, nroColHead).Value = area.AbrArea;
}
//--------HEAD: Le damos el formato a la cabecera ---------------------------------
IXLRange rango = worksheet.Range(nroRowHead, 1, nroRowHead, nroColHead);
rango.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rango.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
rango.Style.Border.BottomBorderColor = XLColor.Black;
rango.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rango.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
rango.Style.Fill.BackgroundColor = XLColor.FromArgb(180, 180, 180);
//--------BODY: Generar la tabla de estudiantes con sus notas ----------------------
int nroRowBody = 2;
int nroColBody;
foreach (Estudiante estudiante in objListEstudaintes)
{
worksheet.Cell(nroRowBody, 1).Value = estudiante.Id;
worksheet.Cell(nroRowBody, 2).Style.NumberFormat.Format = "@";
worksheet.Cell(nroRowBody, 2).Value = estudiante.CodEstudiante.ToString();
worksheet.Cell(nroRowBody, 3).Value = estudiante.Nombres;
nroColBody = 4;
foreach (var area in objListaAreas)
{
worksheet.Cell(nroRowBody, nroColBody).Value = "";
//--------BODY: Data Validation -------------------------------------------------------
worksheet.Cell(nroRowBody, nroColBody).DataValidation.Decimal.Between(0, 20);
nroColBody++;
}
nroRowBody++;
}
worksheet.Columns(nroRowHead, nroColHead).AdjustToContents();
Console.WriteLine("ANTES DE GUARDAR");
workbook.SaveAs(pathServerTempPlantilla);
Console.WriteLine("DESPUES DE GUARDAR");
}
Console.WriteLine("FIN");
Console.ReadLine();
}
// parameters:
// bAdvanceXml 是否为 AdvanceXml 情况
static void OutputBorrowHistory(
IXLWorksheet sheet,
XmlDocument reader_dom,
ChargingHistoryLoader history_loader,
CacheableBiblioLoader summary_loader,
// Delegate_GetBiblioSummary procGetBiblioSummary,
ref int nRowIndex,
ref List<int> column_max_chars)
{
int nStartRow = nRowIndex;
OutputTitleLine(sheet,
ref nRowIndex,
"--- 借阅历史 --- " + history_loader.GetCount(),
XLColor.DarkGreen,
2,
7);
List<IXLCell> cells = new List<IXLCell>();
// 册信息若干行的标题
{
List<string> titles = new List<string>();
titles.Add("序号");
titles.Add("册条码号");
titles.Add("书目摘要");
titles.Add("借阅时间");
titles.Add("期限");
titles.Add("借阅操作者");
titles.Add("还书时间");
titles.Add("还书操作者");
int nColIndex = 2;
foreach (string s in titles)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
nRowIndex++;
}
List<string> item_barcodes = new List<string>();
List<Point> points = new List<Point>();
int nItemIndex = 0;
foreach (ChargingItemWrapper wrapper in history_loader)
{
ChargingItem item = wrapper.Item;
ChargingItem rel = wrapper.RelatedItem;
string strItemBarcode = item.ItemBarcode;
string strBorrowDate = rel == null ? "" : rel.OperTime;
string strBorrowPeriod = GetDisplayTimePeriodString(rel == null ? "" : rel.Period);
string strReturnDate = item.OperTime;
//string strRecPath = borrow.GetAttribute("recPath");
//string strIsOverdue = borrow.GetAttribute("isOverdue");
//bool bIsOverdue = DomUtil.IsBooleanTrue(strIsOverdue, false);
//string strOverdueInfo = borrow.GetAttribute("overdueInfo1");
string strSummary = "";
#if NO
if (string.IsNullOrEmpty(strItemBarcode) == false
&& string.IsNullOrEmpty(strSummary) == true)
{
string strError = "";
int nRet = procGetBiblioSummary(strItemBarcode,
"", // strConfirmItemRecPath,
false,
out strSummary,
out strError);
if (nRet == -1)
strSummary = strError;
}
#endif
item_barcodes.Add("@itemBarcode:" + strItemBarcode);
List<string> cols = new List<string>();
cols.Add((nItemIndex + 1).ToString());
cols.Add(strItemBarcode);
cols.Add(strSummary);
cols.Add(strBorrowDate);
cols.Add(strBorrowPeriod);
cols.Add(rel == null ? "" : rel.Operator);
cols.Add(strReturnDate);
cols.Add(item.Operator);
int nColIndex = 2;
points.Add(new Point(nColIndex + 2, nRowIndex));
foreach (string s in cols)
{
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(s));
IXLCell cell = null;
if (nColIndex == 2)
{
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nItemIndex + 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else
cell = sheet.Cell(nRowIndex, nColIndex).SetValue(s);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
cells.Add(cell);
}
#if NO
// 超期的行为黄色背景
if (bIsOverdue)
{
var line = sheet.Range(nRowIndex, 2, nRowIndex, 2 + cols.Count - 1);
line.Style.Fill.BackgroundColor = XLColor.Yellow;
}
#endif
nItemIndex++;
nRowIndex++;
}
// 加入书目摘要
summary_loader.RecPaths = item_barcodes;
int i = 0;
foreach (BiblioItem biblio in summary_loader)
{
Point point = points[i];
int nColIndex = point.X;
// 统计最大字符数
SetMaxChars(ref column_max_chars, nColIndex - 1, GetCharWidth(biblio.Content));
IXLCell cell = null;
cell = sheet.Cell(point.Y, nColIndex).SetValue(biblio.Content);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
i++;
}
// 册信息标题下的虚线
var rngData = sheet.Range(cells[0], cells[cells.Count - 1]);
rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Dotted;
sheet.Rows(nStartRow + 1, nRowIndex - 1).Group();
}
public void WriteReportLines(IXLWorksheet ws, Tuple <int, List <PaymentsReportModel>, PaymentsReportInputModel> reportData)
{
int writingRow = reportData.Item1;
List <PaymentsReportModel> paymentsReportModel = reportData.Item2;
PaymentsReportInputModel input = reportData.Item3;
foreach (var p in paymentsReportModel)
{
var tabellaPagamento = new ModalitaPagamento().List;
var paymentMethod = tabellaPagamento
.Where(x => x.Codice == p.ModalitaPagamento)
.Select(x => x.Nome)
.FirstOrDefault();
ws.Cell(writingRow, 1).SetValue(p.NumeroFattura);
ws.Cell(writingRow, 2).SetValue(p.DataFattura);
ws.Cell(writingRow, 3).SetValue(paymentMethod);
ws.Cell(writingRow, 4).SetValue(p.IstitutoFinanziario);
ws.Cell(writingRow, 5).SetValue(p.IBAN);
ws.Cell(writingRow, 6).SetValue(p.TRNCode);
if (p.DataScadenzaPagamento != DateTime.MinValue)
{
ws.Cell(writingRow, 7).SetValue(p.DataScadenzaPagamento.ToString("dd/MM/yyyy"));
}
if (p.PaymentDate != DateTime.MinValue)
{
ws.Cell(writingRow, 8).SetValue(p.PaymentDate.ToString("dd/MM/yyyy"));
}
ws.Cell(writingRow, 9).SetValue(p.ImportoPagamento);
ws.Range("A" + writingRow + ":I" + writingRow).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
writingRow++;
}
writingRow++;
var due = paymentsReportModel
.Select(x => x.ImportoPagamento)
.Sum();
var paid = paymentsReportModel
.Where(x => x.PaymentDate != DateTime.MinValue)
.Select(x => x.ImportoPagamento)
.Sum();
var difference = paymentsReportModel
.Where(x => x.PaymentDate == DateTime.MinValue)
.Select(x => x.ImportoPagamento)
.Sum();
ws.Cell(writingRow, 1).RichText
.AddText("Totale fatture:")
.SetBold();
ws.Cell(writingRow, 2).SetValue(due);
ws.Cell(writingRow + 1, 1).RichText
.AddText("Totale pagato:")
.SetBold();
ws.Cell(writingRow + 1, 2).SetValue(paid);
ws.Cell(writingRow + 2, 1).RichText
.AddText("Residuo:")
.SetBold();
ws.Cell(writingRow + 2, 2).SetValue(difference);
var colA = ws.Column("A");
colA.AdjustToContents();
var colBD = ws.Columns("B:D");
colBD.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colBD.AdjustToContents();
var colE = ws.Column("E");
colE.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colE.AdjustToContents();
var colF = ws.Column("F");
colF.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colF.AdjustToContents();
var colG = ws.Column("G");
colG.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colG.AdjustToContents();
var colH = ws.Column("H");
colH.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
colH.AdjustToContents();
var colI = ws.Column("I");
colI.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
colI.AdjustToContents();
var rangeValues = ws.Range("B1:B4");
rangeValues.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
private IXLCell CreateXLSXContentLines(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
IXLWorksheet workSheet, IXLCell StartCell,
StatistikRootClass.StatistikDataSelectionTypes writeOutType,
bool StandardContent = true, bool ProcessOrte = true)
{
int ActuallRow = StartCell.WorksheetRow().RowNumber() + 1;
int FirstCellNumber = StartCell.WorksheetColumn().ColumnNumber();
IXLCell LastCell = null;
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
if (StandardContent)
{
workSheet.Cell(ActuallRow, FirstCellNumber).Value = agCounter.ArbeitsGruppeDaten.NameId;
workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value
= agCounter.ArbeitsGruppeDaten.Beschreibung;
}
ActuallRow++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
if (StandardContent)
{
workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value
= ortsTeilCounter.OrtsTeilDaten.NameId;
}
if (ProcessOrte)
{
ActuallRow++;
if (StandardContent)
{
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
workSheet.Cell(ActuallRow, FirstCellNumber + 2).Value
= ortsCounter.OrtDaten.Bezeichnung;
LastCell = ShowStandardDataPerEntry(workSheet, ortsCounter.Counter,
ActuallRow, 6);
ActuallRow = LastCell.Address.RowNumber + 1;
}
if (ProcessOrte)
{
IXLRange OrtsTeilLine = workSheet.Range(ActuallRow, FirstCellNumber + 2, ActuallRow, FirstCellNumber + 3).Merge();
OrtsTeilLine.Style.Font.SetBold();
OrtsTeilLine.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
OrtsTeilLine.Value = ortsTeilCounter.OrtsTeilDaten.NameId + " - Summe";
LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter,
ActuallRow, 6);
ActuallRow = LastCell.Address.RowNumber;
}
}
else
{
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
ActuallRow = LastCell.Address.RowNumber + 1;
}
if (ProcessOrte)
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
ActuallRow = LastCell.Address.RowNumber;
}
}
ActuallRow++;
}
else
{
if (StandardContent)
{
LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter,
ActuallRow, 6);
}
else
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
}
ActuallRow = LastCell.Address.RowNumber + 1;
}
}
}
return LastCell;
}
private void GenerarDatosUnidades(SUBPROYECTO sub)
{
IXLWorksheet _worksheetProyecto = _xlworkbook.Worksheet("Template Dispositivo");
IXLWorksheet _worksheetSoporte = _xlworkbook.Worksheet("Soporte");
IXLWorksheet sheetUnidad = _worksheetProyecto.CopyTo(sub.nombre.Length > 31 - sub.ot.Value.ToString().Length - 3 ? sub.ot + " - " + sub.nombre.Substring(0, 31 - sub.ot.Value.ToString().Length - 3) : sub.ot + " - " + sub.nombre, _worksheetProyecto.Position);
sheetUnidad.Unhide();
List <HORA_TIPO_SUBPROYECTO> _HORAS = ControladorSubproyecto.ObtenerHorasTipoUnidad();
//Formato
var dataV = sheetUnidad.Range("'+" + sheetUnidad.Name + "'!C9:C68").SetDataValidation();
dataV.List(_worksheetSoporte.Range(_worksheetSoporte.Cell(2, 5), _worksheetSoporte.Cell(_HORAS.Count + 2, 5)));
dataV.IgnoreBlanks = true;
dataV.InCellDropdown = true;
dataV.ShowInputMessage = true;
dataV.ShowErrorMessage = true;
dataV.ErrorStyle = ClosedXML.Excel.XLErrorStyle.Stop;
sheetUnidad.Cell(1, 3).Value = sub.ot;
sheetUnidad.Cell(2, 3).Value = sub.ot_cliente;
sheetUnidad.Cell(3, 3).Value = sub.horas_orden_compra.HasValue ? sub.horas_orden_compra : null;
int fila = 9;
List <SUBPROYECTO> subproyectos = ControladorSubproyecto.ObtenerSubproyectosHijos(sub);
List <Actividad_proyecto> actividades;
foreach (var subproyecto in subproyectos)
{
sheetUnidad.Cell(fila, 1).Value = subproyecto.id_subproyecto;
sheetUnidad.Cell(fila, 2).Value = subproyecto.nombre;
HORA_TIPO_SUBPROYECTO hora =
ControladorSubproyecto.ObtenerHoraTipoSubproyecto(subproyecto.id_hora_tipo_subproyecto);
if (hora != null)
{
sheetUnidad.Cell(fila, 3).Value = hora.nombre;
//sheetUnidad.Cell(fila, 4).Value = hora.horas;
}
else
{
sheetUnidad.Cell(fila, 3).Value = "";
sheetUnidad.Cell(fila, 4).Value = ControladorSubproyecto.CalcularHorasOrdenCompra(subproyecto);
}
sheetUnidad.Cell(fila, 5).Value = decimal.Round((decimal)ControladorSubproyecto.CalcularHorasOrdenCompra(subproyecto), 2);
actividades = ControladorSubproyecto.ObtenerActividades(subproyecto);
int columna = 0;
foreach (var acti in actividades)
{
switch (ControladorActividades.ObtenerActividad(acti).id_tipo_actividad)
{
//Concepto
case 2:
columna = 9;
break;
case 3:
columna = 22;
break;
case 4:
columna = 35;
break;
default:
columna = -1;
break;
}
if (columna > 0)
{
decimal horaConsumida = (decimal)(Math.Round(ControladorActividades.ObtenerHorasConsumidas(acti, "prod"), 2));
decimal horaAsignada = (decimal)ControladorActividades.ObtenerHorasAsignadasPorOrdenCompra(acti) * (decimal)0.85;
USUARIO responsablePro =
ControladorActividades.ObtenerResponsableProduccion(acti.id_actividad_proyecto);
sheetUnidad.Cell(fila, columna + 1).DataType = XLCellValues.Number;
sheetUnidad.Cell(fila, columna + 1).Value = horaConsumida;
sheetUnidad.Cell(fila, columna + 2).Value = decimal.Round(horaAsignada, 2);
sheetUnidad.Cell(fila, columna + 3).Value = horaAsignada > 0 ? decimal.Round(horaConsumida / horaAsignada * 100, 2) + "%" : "0%";
sheetUnidad.Cell(fila, columna + 4).Value = responsablePro != null ? responsablePro.nombre + " " +
responsablePro.apellido : "Sin asignar";
sheetUnidad.Cell(fila, columna + 4).WorksheetColumn().AdjustToContents();
columna += 4;
//Control
horaConsumida = (decimal)(Math.Round(ControladorActividades.ObtenerHorasConsumidas(acti, "calidad"), 2));
horaAsignada = (decimal)(ControladorActividades.ObtenerHorasAsignadasPorOrdenCompra(acti) * 0.10);
responsablePro =
ControladorActividades.ObtenerResponsableControln(acti.id_actividad_proyecto);
sheetUnidad.Cell(fila, columna + 1).DataType = XLCellValues.Number;
sheetUnidad.Cell(fila, columna + 1).Value = horaConsumida;
sheetUnidad.Cell(fila, columna + 2).Value = decimal.Round(horaAsignada, 2);
sheetUnidad.Cell(fila, columna + 3).Value = horaAsignada > 0 ? decimal.Round(horaConsumida / horaAsignada * 100, 2) + "%" : "0%";
sheetUnidad.Cell(fila, columna + 4).Value = responsablePro != null ? responsablePro.nombre + " " +
responsablePro.apellido : "Sin asignar";
sheetUnidad.Cell(fila, columna + 4).WorksheetColumn().AdjustToContents();
columna += 4;
//Correccion
horaConsumida = (decimal)(Math.Round(ControladorActividades.ObtenerHorasConsumidas(acti, "correc"), 2));
horaAsignada = (decimal)(ControladorActividades.ObtenerHorasAsignadasPorOrdenCompra(acti) * 0.05);
responsablePro =
ControladorActividades.ObtenerResponsableCorreccion(acti.id_actividad_proyecto);
sheetUnidad.Cell(fila, columna + 1).DataType = XLCellValues.Number;
sheetUnidad.Cell(fila, columna + 1).Value = horaConsumida;
sheetUnidad.Cell(fila, columna + 2).Value = decimal.Round(horaAsignada, 2);
sheetUnidad.Cell(fila, columna + 3).Value = horaAsignada > 0 ? decimal.Round(horaConsumida / horaAsignada * 100, 2) + "%" : "0%";
sheetUnidad.Cell(fila, columna + 4).Value = responsablePro != null ? responsablePro.nombre + " " +
responsablePro.apellido : "Sin asignar";
sheetUnidad.Cell(fila, columna + 4).WorksheetColumn().AdjustToContents();
}
}
fila++;
}
//Otras actividades
fila = 9;
List <Actividad_proyecto> listaActividades = ControladorSubproyecto.ObtenerActividades(sub);
foreach (Actividad_proyecto actividad in listaActividades)
{
sheetUnidad.Cell(fila, 7).Value = ControladorActividades.ObtenerActividad(actividad).nombre_actividad;
fila++;
}
}
private void CreateGroupHeader(IXLWorksheet workSheet, string HeadLineText,
int LineNumber, int StartColumnIndex, int NumberOfColumns)
{
IXLRange groupHeaderRange = workSheet.Range(LineNumber, StartColumnIndex,
LineNumber, StartColumnIndex + NumberOfColumns - 1).Merge();
groupHeaderRange.Style
.Font.SetBold()
.Fill.SetBackgroundColor(XLColor.AliceBlue)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.WrapText = true;
groupHeaderRange.Value = HeadLineText;
}
public int WriteOutNumberOfEntriesPerOrtsTeil(IXLWorksheet workSheet,
StatistikRootClass.DistributionCountTyp DistTyp)
{
IXLRange headerRng = workSheet.Range("B2:F2").Merge();
headerRng.Style
.Font.SetBold()
.Fill.SetBackgroundColor(XLColor.AliceBlue)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.WrapText = true;
headerRng.Value = "Vorschläge / Ideen Anzahlen " + DistTyp.GetDescription();
switch (DistTyp)
{
case StatistikRootClass.DistributionCountTyp.AllTogether:
{
workSheet.Column("A").Width = 2;
workSheet.Column("G").Width = 2;
IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text);
CounterHeaderRange.Value = "Anzahlen";
CounterHeaderRange.Style
.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.WrapText = true;
int ColumnNumber = 0;
foreach (string Col in ProjektCounterColumns(DistTyp))
workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col;
workSheet.Column("D").Width = 40;
return 7;
}
break;
case StatistikRootClass.DistributionCountTyp.PerAG:
{
workSheet.Column("A").Width = 2;
workSheet.Column("G").Width = 2;
IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text);
CounterHeaderRange.Value = "Anzahlen";
CounterHeaderRange.Style
.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.WrapText = true;
int ColumnNumber = 0;
foreach (string Col in ProjektCounterColumns(DistTyp))
workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col;
workSheet.Column("D").Width = 40;
return 7;
}
break;
case StatistikRootClass.DistributionCountTyp.PerOrtsTeil:
{
workSheet.Column("A").Width = 2;
workSheet.Column("G").Width = 2;
IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text);
CounterHeaderRange.Value = "Anzahlen";
CounterHeaderRange.Style
.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.WrapText = true;
int ColumnNumber = 0;
foreach (string Col in ProjektCounterColumns(DistTyp))
workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col;
workSheet.Column("D").Width = 40;
return 7;
}
break;
default:
return 7;
}
}
public void WriteOutNumberOfEntriesPerOrtsTeil(IXLWorksheet workSheet,
ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
StatistikRootClass.DistributionCountTyp DistTyp, int StartLine)
{
switch (DistTyp)
{
case StatistikRootClass.DistributionCountTyp.AllTogether:
{
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
workSheet.Cell($"B{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId;
IXLRange AGNameRange = workSheet.Range($"C{StartLine}:D{StartLine}").Merge();
AGNameRange.Style.Alignment.WrapText = true;
AGNameRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Justify);
AGNameRange.Value = agCounter.ArbeitsGruppeDaten.Beschreibung;
StartLine++;
StartLine++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
workSheet.Cell($"C{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId;
StartLine++;
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
workSheet.Cell($"D{StartLine}").Value = ortsCounter.OrtDaten.Bezeichnung;
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Value = ortsCounter.Counter.NumberOfVorschlaege;
workSheet.Cell($"F{StartLine}").Value = ortsCounter.Counter.NumberOfIdeen;
StartLine++;
}
workSheet.Cell($"D{StartLine}").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
workSheet.Cell($"D{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId + " Summen";
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = ortsTeilCounter.Counter.NumberOfVorschlaege;
workSheet.Cell($"F{StartLine}").Value = ortsTeilCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
StartLine++;
workSheet.Cell($"D{StartLine}").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
workSheet.Cell($"D{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId + " Summen";
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = agCounter.Counter.NumberOfVorschlaege;
workSheet.Cell($"F{StartLine}").Value = agCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
StartLine++;
}
break;
case StatistikRootClass.DistributionCountTyp.PerAG:
{
StartLine++;
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
workSheet.Cell($"B{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId;
IXLRange AGNameRange = workSheet.Range($"C{StartLine}:D{StartLine}").Merge();
AGNameRange.Style.Alignment.WrapText = true;
AGNameRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Justify);
AGNameRange.Value = agCounter.ArbeitsGruppeDaten.Beschreibung;
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = agCounter.Counter.NumberOfProjekte;
workSheet.Cell($"F{StartLine}").Value = agCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
StartLine++;
}
break;
case StatistikRootClass.DistributionCountTyp.PerOrtsTeil:
{
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
StartLine++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
workSheet.Cell($"C{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId;
workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number);
workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray;
workSheet.Cell($"E{StartLine}").Value = ortsTeilCounter.Counter.NumberOfProjekte;
workSheet.Cell($"F{StartLine}").Value = ortsTeilCounter.Counter.NumberOfIdeen;
StartLine++;
StartLine++;
}
}
StartLine++;
}
break;
}
}
private void btnExportExcel_Click(object sender, RoutedEventArgs e)
{
DateTime second = new DateTime(Convert.ToInt32(date2.SelectedDate.Value.ToString("yyyy")), Convert.ToInt32(date2.SelectedDate.Value.ToString("MM")), Convert.ToInt32(date2.SelectedDate.Value.ToString("dd")), 23, 59, 59);
try
{
if (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 = "Отчет с " + date1.SelectedDate + " по " + second + "по просроченные товаровы";
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 = "Цена покуплен";
worksheet.Cell(2, 6).Value = "кг/шт.";
worksheet.Cell(2, 7).Value = "Дате";
int kk = 3;
foreach (InvalidGoods rv in DG.Items)
{
worksheet.Cell(kk, 1).Value = rv.Shtrix;
worksheet.Cell(kk, 2).Value = rv.NameOfProduct;
worksheet.Cell(kk, 3).Value = rv.TypeName;
worksheet.Cell(kk, 4).Value = rv.MassaName;
worksheet.Cell(kk, 5).Value = rv.BuyPrice;
worksheet.Cell(kk, 6).Value = rv.CountProduct;
worksheet.Cell(kk, 7).Value = rv.Sana;
kk++;
}
worksheet.Columns("A", "Z").AdjustToContents();
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(err.Message);
}
}
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");
}
}
public override object GetExternalObject(string identifier)
{
if (identifier.Contains("!") && _wb != null)
{
var referencedSheetNames = identifier.Split(':')
.Select(part =>
{
if (part.Contains("!"))
{
return(part.Substring(0, part.IndexOf('!')).ToLower());
}
else
{
return(null);
}
})
.Where(sheet => sheet != null)
.Distinct();
if (!referencedSheetNames.Any())
{
return(GetCellRangeReference(_ws.Range(identifier)));
}
else if (referencedSheetNames.Count() > 1)
{
throw new ArgumentOutOfRangeException(referencedSheetNames.Last(), "Cross worksheet references may references no more than 1 other worksheet");
}
else
{
if (!_wb.TryGetWorksheet(referencedSheetNames.Single(), out IXLWorksheet worksheet))
{
throw new ArgumentOutOfRangeException(referencedSheetNames.Single(), "The required worksheet cannot be found");
}
identifier = identifier.ToLower().Replace(string.Format("{0}!", worksheet.Name.ToLower()), "");
return(GetCellRangeReference(worksheet.Range(identifier)));
}
}
else if (_ws != null)
{
if (TryGetNamedRange(identifier, _ws, out IXLNamedRange namedRange))
{
var references = (namedRange as XLNamedRange).RangeList.Select(r =>
XLHelper.IsValidRangeAddress(r)
? GetCellRangeReference(_ws.Workbook.Range(r))
: new XLCalcEngine(_ws).Evaluate(r.ToString())
);
if (references.Count() == 1)
{
return(references.Single());
}
return(references);
}
return(GetCellRangeReference(_ws.Range(identifier)));
}
else
{
return(identifier);
}
}
/// <summary>
/// Gets the range of the contents in an excel worksheet.
/// </summary>
/// <returns>Range of the contents in an excel worksheet.</returns>
private IXLRange GetContentsRange()
{
return(worksheet.Range($"A2:{worksheet.RangeUsed().RangeAddress.LastAddress}"));
}
public ResultModels Export(ref IXLWorksheet wsexcel, UsageManagementRequest request)
{
var result = new ResultModels();
try
{
using (var cxt = new NuWebContext())
{
wsexcel.Cell("A" + 1).Value = "Usage Management";
wsexcel.Row(1).Style.Font.SetBold(true);
wsexcel.Row(1).Height = 25;
wsexcel.Range(1, 1, 1, 5).Merge();
//Date
string date = string.Format("Date: {0}", request.DateFrom.ToString("MM/dd/yyyy"));
if (request.DateFrom.Date != request.DateTo.Date)
{
date = string.Format("Date from {0} to {1}", request.DateFrom.ToString("MM/dd/yyyy"), request.DateTo.ToString("MM/dd/yyyy"));
}
wsexcel.Cell("A" + 2).Value = date;
wsexcel.Row(2).Style.Font.SetBold(true);
wsexcel.Row(2).Height = 16;
wsexcel.Range(2, 1, 2, 5).Merge();
string[] lstHeaders = new string[] {
"Index", "Ingredient Code", "Ingredient Name", "Base UOMs", "Usage"
};
int row = 3;
//Add header to excel
for (int i = 1; i <= lstHeaders.Length; i++)
{
wsexcel.Cell(row, i).Value = lstHeaders[i - 1];
wsexcel.Row(row).Style.Font.SetBold(true);
}
int cols = lstHeaders.Length;
row = 4;
//Get list data
var lstData = _usageManagementFactory.GetUsageManagement(request); //CalUsageManagementwithoutDetail(request);
if (lstData != null && lstData.Count > 0)
{
foreach (var item in lstData)
{
wsexcel.Cell("A" + row).Value = item.Index;
wsexcel.Cell("B" + row).Value = item.Code;
wsexcel.Cell("C" + row).Value = item.Name;
wsexcel.Cell("D" + row).Value = item.UOMName;
wsexcel.Cell("E" + row).Value = item.Usage;
row++;
}
}
wsexcel.Range("E2", "E" + row).Style.NumberFormat.Format = "#,##0.0000";
BaseFactory.FormatExcelExport(wsexcel, row, cols);
result.IsOk = true;
}
}
catch (Exception ex)
{
result.IsOk = false;
result.Message = ex.Message;
_logger.Error(ex);
}
return(result);
}
public void ExportData(string sIncID, string sOprtID, string sFacID, string sYear)
{
HttpResponse httpResponse = Response;
XLWorkbook wb = new XLWorkbook();
int nRow = 1;
int nCol = 1;
#region Action
Action <IXLWorksheet, string, int, int, int, bool, XLAlignmentHorizontalValues, XLAlignmentVerticalValues, bool, int?, double?> SetTbl = (sWorkSheet, sTxt, row, col, FontSize, Bold, Horizontal, Vertical, wraptext, dec, width) =>
{
if (sTxt == null)
{
sTxt = "";
}
sWorkSheet.Cell(row, col).Value = sTxt;
sWorkSheet.Cell(row, col).Style.Font.FontSize = FontSize;
sWorkSheet.Cell(row, col).Style.Font.Bold = Bold;
sWorkSheet.Cell(row, col).Style.Alignment.WrapText = true;
sWorkSheet.Cell(row, col).Style.Alignment.Horizontal = Horizontal;
sWorkSheet.Cell(row, col).Style.Alignment.Vertical = Vertical;
if (width != null)
{
sWorkSheet.Column(col).Width = width.Value;
}
if (dec != null && sTxt != "0")
{
string sformate = "#,##";
sWorkSheet.Cell(row, col).Style.NumberFormat.Format = sformate;
}
var nIndex = sTxt.Split('/').Length;
if (nIndex == 3)
{
sWorkSheet.Cell(row, col).Style.DateFormat.Format = "dd/MM/yyyy";
}
};
#endregion
#region QUERY
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <cReport> lstProduct = new List <cReport>();
string[] arrShortMonth = new string[12] {
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
};
int nIndID = SystemFunction.GetIntNullToZero(sIncID);
int nOprtID = SystemFunction.GetIntNullToZero(sOprtID);
int nFacID = SystemFunction.GetIntNullToZero(sFacID);
string sIncName = db.mTIndicator.Any(w => w.ID == nIndID) ? db.mTIndicator.FirstOrDefault(w => w.ID == nIndID).Indicator : "";
string sOprtName = db.mOperationType.Any(w => w.ID == nOprtID) ? db.mOperationType.FirstOrDefault(w => w.ID == nOprtID).Name : "";
string sFacName = db.mTFacility.Any(w => w.ID == nFacID) ? db.mTFacility.FirstOrDefault(w => w.ID == nFacID).Name : "";
var lstComplaintType = db.TM_ComplaintType.Where(w => w.cDel != "Y").ToList();
bool IsNew = true;
var itemEPI_FORM = db.TEPI_Forms.FirstOrDefault(w => w.sYear == sYear && w.IDIndicator == nIndID && w.OperationTypeID == nOprtID && w.FacilityID == nFacID);
int EPI_FORMID = 0;
if (itemEPI_FORM != null)
{
IsNew = false;
EPI_FORMID = itemEPI_FORM.FormID;
}
var lstImpactInFROM = db.TComplaint_Impact.Where(w => w.FormID == EPI_FORMID).ToList();
string sProductName = "";
var dataComplaint = db.mTProductIndicator.FirstOrDefault(w => w.IDIndicator == 2);
if (dataComplaint != null)
{
sProductName = dataComplaint.ProductName;
}
lstProduct = db.TComplaint.Where(w => w.FormID == EPI_FORMID).OrderBy(o => o.ComplaintDate).Select(s => new cReport
{
nComplaintID = s.nComplaintID,
ComplaintDate = s.ComplaintDate.HasValue ? s.ComplaintDate.Value : (DateTime?)null,
sProductName = sProductName,
nMonth = s.ComplaintDate.HasValue ? s.ComplaintDate.Value.Month : 0,
sIssueBy = s.sIssueBy,
sSubject = s.sSubject,
sDetail = s.sDetail,
nComplaintTypeID = s.nComplaintTypeID,
nComplaintByID = s.nComplaintByID,
sComplaintByOther = s.sComplaintByOther,
sCorrectiveAction = s.sCorrectiveAction,
}).ToList();
lstProduct.ForEach(f =>
{
var dataComplaintType = lstComplaintType.FirstOrDefault(w => w.nType == 1 && w.nID == f.nComplaintTypeID);
var dataComplaintBy = lstComplaintType.FirstOrDefault(w => w.nType == 3 && w.nID == f.nComplaintByID);
f.sIssueDate = f.ComplaintDate.HasValue ? f.ComplaintDate.Value.ToString("dd/MM/yyyy") : "";
f.sComplaintBy = dataComplaintBy != null ? dataComplaintBy.sName + (!string.IsNullOrEmpty(f.sComplaintByOther) ? " (" + f.sComplaintByOther + ")" : "") : "";
lstImpactInFROM.Where(w => w.nComplaintID == f.nComplaintID).ForEach(ipct =>
{
var dataImpact = lstComplaintType.FirstOrDefault(w => w.nType == 2 && w.nID == ipct.nImpactTypeID);
f.sImpact += dataImpact != null ? " ," + dataImpact.sName + (!string.IsNullOrEmpty(ipct.sOther) ? " (" + ipct.sOther + ")" : "") : "";
});
f.sImpact = f.sImpact.Length > 0 ? f.sImpact.Remove(0, 2) : f.sImpact;
f.sComplaintType = dataComplaintType != null ? dataComplaintType.sName : "";
});
var lstDeviate = SystemFunction.GetDeviate(nIndID, nOprtID, nFacID, sYear);
#endregion
#region BIND DATA
IXLWorksheet ws1 = wb.Worksheets.Add("Data");
ws1.PageSetup.Margins.Top = 0.2;
ws1.PageSetup.Margins.Bottom = 0.2;
ws1.PageSetup.Margins.Left = 0.1;
ws1.PageSetup.Margins.Right = 0;
ws1.PageSetup.Margins.Footer = 0;
ws1.PageSetup.Margins.Header = 0;
ws1.Style.Font.FontName = "Cordia New";
nRow = 1;
nCol = 1;
SetTbl(ws1, "Indicator : " + sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Operation : " + sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Facility : " + sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Year : " + sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Product", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 45);
nCol++;
SetTbl(ws1, "Month", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Complaint Type", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Impact Type", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Issue date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 12);
nCol++;
SetTbl(ws1, "Issued by", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 15);
nCol++;
SetTbl(ws1, "Subject", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
nCol++;
SetTbl(ws1, "Complaint by", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Detail", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
nCol++;
SetTbl(ws1, "Corrective action", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
ws1.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#9cb726");
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
foreach (var item in lstProduct)
{
nRow++;
nCol = 1;
SetTbl(ws1, "'" + item.sProductName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + arrShortMonth[item.nMonth.Value - 1], nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sComplaintType, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sImpact, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sIssueDate, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sIssueBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sSubject, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sComplaintBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sDetail, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sCorrectiveAction, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
#endregion
#region BIND DATA DEVIATE
IXLWorksheet ws2 = wb.Worksheets.Add("Deviate");
ws2.PageSetup.Margins.Top = 0.2;
ws2.PageSetup.Margins.Bottom = 0.2;
ws2.PageSetup.Margins.Left = 0.1;
ws2.PageSetup.Margins.Right = 0;
ws2.PageSetup.Margins.Footer = 0;
ws2.PageSetup.Margins.Header = 0;
ws2.Style.Font.FontName = "Cordia New";
nRow = 1;
nCol = 1;
SetTbl(ws2, "Indicator : " + sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Operation : " + sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Facility : " + sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Year : " + sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "No.", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 10);
nCol++;
SetTbl(ws2, "Month", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 13);
nCol++;
SetTbl(ws2, "Remark", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 50);
nCol++;
SetTbl(ws2, "Action By", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 20);
nCol++;
SetTbl(ws2, "Date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 15);
ws2.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#9cb726");
ws2.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws2.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
for (int i = 0; i < lstDeviate.Count(); i++)
{
nRow++;
nCol = 1;
SetTbl(ws2, "'" + (i + 1), nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sMonth, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sRemark, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sActionBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sDate, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws2.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
#endregion
#region CreateEXCEL
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string sName = "Input_" + sIncName + "_" + sFacName + "_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US"));
httpResponse.AddHeader("content-disposition", "attachment;filename=" + sName + ".xlsx");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
#endregion
}
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 ExpandDescription(IXLWorksheet ws, IXLRow row)
{
ws.Range(row.Cell((int)CustomsInvoiceHeaders.Description), row.Cell((int)CustomsInvoiceHeaders.Amount)).Merge();
}
private void ExcelStyle(IXLWorksheet xlSheet, int lastRow)
{
var range1 = xlSheet.Range("A1:D1");
var range2 = xlSheet.Range("E1:H1");
var range3 = xlSheet.Range("I1:L1");
var range4 = xlSheet.Range("M1:N1");
var range5 = xlSheet.Range("O1:R1");
var range6 = xlSheet.Range("S1:Y1");
range1.Merge().Style.Font.SetBold().Font.FontSize = 14;
range2.Merge().Style.Font.SetBold().Font.FontSize = 14;
range3.Merge().Style.Font.SetBold().Font.FontSize = 14;
range4.Merge().Style.Font.SetBold().Font.FontSize = 14;
range5.Merge().Style.Font.SetBold().Font.FontSize = 14;
range6.Merge().Style.Font.SetBold().Font.FontSize = 14;
var rangeString = $"A1:Y{lastRow}";
xlSheet.Range(rangeString).Style.Border.TopBorder = XLBorderStyleValues.Thin;
xlSheet.Range(rangeString).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
xlSheet.Range(rangeString).Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
xlSheet.Range(rangeString).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
xlSheet.Range(rangeString).Style.Border.RightBorder = XLBorderStyleValues.Thin;
xlSheet.Range(rangeString).Style.Border.TopBorder = XLBorderStyleValues.Thin;
}
protected void ExportExcel_Click(object sender, EventArgs e)
{
XLWorkbook wb = new XLWorkbook();
int nRow = 1;
int nCol = 1;
#region Action
Action <IXLWorksheet, string, int, int, int, bool, XLAlignmentHorizontalValues, XLAlignmentVerticalValues, bool, int?, double?> SetTbl = (sWorkSheet, sTxt, row, col, FontSize, Bold, Horizontal, Vertical, wraptext, dec, width) =>
{
if (sTxt == null)
{
sTxt = "";
}
sWorkSheet.Cell(row, col).Value = sTxt;
sWorkSheet.Cell(row, col).Style.Font.FontSize = FontSize;
sWorkSheet.Cell(row, col).Style.Font.Bold = Bold;
sWorkSheet.Cell(row, col).Style.Alignment.WrapText = true;
sWorkSheet.Cell(row, col).Style.Alignment.Horizontal = Horizontal;
sWorkSheet.Cell(row, col).Style.Alignment.Vertical = Vertical;
sWorkSheet.Cell(row, col).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
sWorkSheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
sWorkSheet.Cell(row, col).Style.Border.InsideBorderColor = XLColor.FromHtml("#343a40");
sWorkSheet.Cell(row, col).Style.Border.OutsideBorderColor = XLColor.FromHtml("#343a40");
if (width != null)
{
sWorkSheet.Column(col).Width = width.Value;
}
if (dec != null && sTxt != "0")
{
string sformate = "#,##";
sWorkSheet.Cell(row, col).Style.NumberFormat.Format = sformate;
}
var nIndex = sTxt.Split('/').Length;
if (nIndex == 3)
{
sWorkSheet.Cell(row, col).Style.DateFormat.Format = "dd/MM/yyyy";
}
};
#endregion
#region Query
IXLWorksheet ws1 = wb.Worksheets.Add("History");
ws1.PageSetup.Margins.Top = 0.2;
ws1.PageSetup.Margins.Bottom = 0.2;
ws1.PageSetup.Margins.Left = 0.1;
ws1.PageSetup.Margins.Right = 0;
ws1.PageSetup.Margins.Footer = 0;
ws1.PageSetup.Margins.Header = 0;
ws1.Style.Font.FontName = "Cordia New";
SetTbl(ws1, "No.", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 10);
nCol++;
SetTbl(ws1, "Username", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 20);
nCol++;
SetTbl(ws1, "Full Name", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 20);
nCol++;
SetTbl(ws1, "Head Menu Name", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 25);
nCol++;
SetTbl(ws1, "Menu Name", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 25);
nCol++;
SetTbl(ws1, "Action Date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 20);
ws1.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#e3e3f1");
ws1.Range(nRow, 1, nRow, nCol).Style.Font.FontColor = XLColor.Black;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorderColor = XLColor.Black;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorderColor = XLColor.Black;
nRow++;
string sEmp = "";
int i = 1;
sslstData.ForEach(f =>
{
nCol = 1;
SetTbl(ws1, "'" + i, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Top, true, null, null);
nCol++;
SetTbl(ws1, "'" + f.Username, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Top, true, null, null);
nCol++;
SetTbl(ws1, "'" + f.Firstname + " " + f.Lastname, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Top, true, null, null);
nCol++;
SetTbl(ws1, "'" + f.sMenuHead, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Top, true, null, null);
nCol++;
SetTbl(ws1, "'" + f.sMenu, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Top, true, null, null);
nCol++;
SetTbl(ws1, "'" + f.sAction, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Top, true, null, null);
nRow++;
i++;
});
#endregion
#region CreateEXCEL
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string sName = "History_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US"));
Response.AddHeader("content-disposition", "attachment;filename=" + sName + ".xlsx");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
memoryStream.Close();
}
Response.End();
#endregion
}
private async Task DrawStockInTable(IXLWorksheet ws)
{
ws.Cell("A7").Value = "DATE";
ws.Range("A7:B7").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("A7:B7").Style.Font.FontColor = XLColor.White;
ws.Range("A7:B7").Style.Font.SetBold(true).Font.SetFontName("Georgia").Font.SetFontSize(10);
ws.Range("A8:B8").Merge();
ws.Cell("C7").Value = "ITEM NAME";
ws.Range("C7:G7").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("C7:G7").Style.Font.FontColor = XLColor.White;
ws.Range("C7:G7").Style.Font.SetBold(true).Font.SetFontName("Georgia").Font.SetFontSize(10);
ws.Range("C8:G8").Merge();
ws.Cell("H7").Value = "QUANTITY";
ws.Range("H7:I7").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("H7:I7").Style.Font.FontColor = XLColor.White;
ws.Range("H7:I7").Style.Font.SetBold(true).Font.SetFontName("Georgia").Font.SetFontSize(10);
await Task.Delay(0);
}
public static void OutputBiblioTable(
string strBiblioRecPath,
string strXml,
int nBiblioIndex,
IXLWorksheet sheet,
int nColIndex,
ref int nRowIndex)
{
XmlDocument dom = new XmlDocument();
dom.LoadXml(strXml);
List <IXLCell> cells = new List <IXLCell>();
// 序号
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(nBiblioIndex + 1);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontSize = 20;
cells.Add(cell);
}
int nNameWidth = 2;
int nValueWidth = 4;
int nStartRow = nRowIndex;
XmlNodeList nodes = dom.DocumentElement.SelectNodes("line");
foreach (XmlElement line in nodes)
{
string strName = line.GetAttribute("name");
string strValue = line.GetAttribute("value");
string strType = line.GetAttribute("type");
if (strName == "_coverImage")
{
continue;
}
// name
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex + 1).SetValue(strName);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontColor = XLColor.DarkGray;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
cells.Add(cell);
{
var rngData = sheet.Range(nRowIndex, nColIndex + 1, nRowIndex, nColIndex + 1 + nNameWidth - 1);
rngData.Merge();
rngData.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Hair;
}
}
// value
{
{
var rngData = sheet.Range(nRowIndex, nColIndex + 1 + nNameWidth, nRowIndex, nColIndex + 1 + nNameWidth + nValueWidth - 1);
rngData.Merge();
}
IXLCell cell = sheet.Cell(nRowIndex, nColIndex + 1 + nNameWidth).SetValue(strValue);
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
#if NO
if (line == 0)
{
cell.Style.Font.FontName = "微软雅黑";
cell.Style.Font.FontSize = 20;
if (string.IsNullOrEmpty(strState) == false)
{
cell.Style.Font.FontColor = XLColor.White;
cell.Style.Fill.BackgroundColor = XLColor.DarkRed;
}
}
#endif
cells.Add(cell);
}
nRowIndex++;
}
// 序号的右边竖线
{
var rngData = sheet.Range(nStartRow, nColIndex, nRowIndex + nRowIndex - nStartRow, nColIndex);
rngData.Merge();
// rngData.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Hair;
// 第一行上面的横线
//rngData = sheet.Range(nRowIndex, 2, nRowIndex, 2 + 7 - 1);
//rngData.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Medium;
}
}
private void createCoverWorksheet(IXLWorksheet worksheet)
{
worksheet.Column("A").Width = 21;
worksheet.Column("B").Width = 26;
worksheet.Cell("A1").Value = "AUDIOVIEW EXPORT";
worksheet.Range("A1","B1").Merge();
addBottomBorder(worksheet.Cell("A1"));
addBottomBorder(worksheet.Cell("B1"));
worksheet.Cell("A2").Value = "PROJECT NAME";
addRightBottomBorder(worksheet.Cell("A2"));
worksheet.Cell("B2").Value = project.Name;
addBottomBorder(worksheet.Cell("B2"));
worksheet.Cell("A3").Value = "PROJECT NUMBER";
addRightBottomBorder(worksheet.Cell("A3"));
worksheet.Cell("B3").Value = project.Number;
addBottomBorder(worksheet.Cell("B3"));
worksheet.Cell("A4").Value = "DATE";
addRightBottomBorder(worksheet.Cell("A4"));
worksheet.Cell("B4").Value = project.Created.ToString();
addBottomBorder(worksheet.Cell("B4"));
worksheet.Cell("A5").Value = "MINOR INTERVAL PERIOD";
addRightBottomBorder(worksheet.Cell("A5"));
worksheet.Cell("B5").Value = project.MinorInterval.ToString();
addBottomBorder(worksheet.Cell("B5"));
worksheet.Cell("A6").Value = "MINOR INTERVAL LIMIT";
addRightBottomBorder(worksheet.Cell("A6"));
worksheet.Cell("B6").Value = project.MinorDBLimit;
addBottomBorder(worksheet.Cell("B6"));
worksheet.Cell("A7").Value = "MAJOR INTERVAL PERIOD";
addRightBottomBorder(worksheet.Cell("A7"));
worksheet.Cell("B7").Value = project.MajorInterval.ToString();
addBottomBorder(worksheet.Cell("B7"));
worksheet.Cell("A8").Value = "MAJOR INTERVAL LIMIT";
addRightBottomBorder(worksheet.Cell("A8"));
worksheet.Cell("B8").Value = project.MajorDBLimit;
addBottomBorder(worksheet.Cell("B8"));
}
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();
}
}
public void TestMove()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 4, 5);
range.AddToNamed("parentRange", XLScope.Worksheet);
IXLNamedRange namedParentRange = ws.NamedRange("parentRange");
IXLRange childRange1 = ws.Range(1, 1, 2, 5);
IXLRange childRange2 = ws.Range(3, 1, 4, 5);
childRange2.AddToNamed("childRange2", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("childRange2");
IXLRange childOfChildRange1 = ws.Range(2, 1, 2, 5);
childOfChildRange1.AddToNamed("childOfChildRange1", XLScope.Worksheet);
IXLNamedRange childOfChildNamedRange = ws.NamedRange("childOfChildRange1");
IXLRange childOfChildRange2 = ws.Range(4, 1, 4, 5);
var panel = new ExcelNamedPanel(namedParentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childRange1, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelDataSourcePanel("fn:DataSource:Method()", childOfChildNamedRange, excelReport, templateProcessor)
}
},
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childOfChildRange2, excelReport, templateProcessor)
}
},
}
};
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 8, 10), excelReport, templateProcessor);
panel.Children.First().Children.First().Parent = panel.Children.First();
panel.Children.Last().Children.First().Parent = panel.Children.Last();
panel.Children.ToList().ForEach(c => c.Parent = panel);
panel.Parent = globalParent;
panel.Move(ws.Cell(5, 6));
Assert.AreEqual(ws.Cell(5, 6), panel.Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Range.LastCell());
Assert.AreEqual("parentRange", ((IExcelNamedPanel)panel).Name);
Assert.AreSame(globalParent, panel.Parent);
Assert.AreEqual(2, panel.Children.Count());
Assert.AreEqual(ws.Cell(5, 6), panel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 10), panel.Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.First());
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(ws.Cell(7, 6), panel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Children.Last().Range.LastCell());
Assert.AreEqual("childRange2", ((IExcelNamedPanel)panel.Children.Last()).Name);
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(1, panel.Children.First().Children.Count());
Assert.AreEqual(ws.Cell(6, 6), panel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 10), panel.Children.First().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelDataSourcePanel>(panel.Children.First().Children.First());
Assert.AreEqual("childOfChildRange1", ((IExcelNamedPanel)panel.Children.First().Children.First()).Name);
Assert.AreSame(panel.Children.First(), panel.Children.First().Children.First().Parent);
Assert.AreEqual(1, panel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(8, 6), panel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Children.Last().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.Last().Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.Last().Children.First());
Assert.AreSame(panel.Children.Last(), panel.Children.Last().Children.First().Parent);
Assert.AreEqual(3, ws.NamedRanges.Count());
panel.Move(ws.Cell(6, 6));
Assert.AreEqual(ws.Cell(6, 6), panel.Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Range.LastCell());
Assert.IsNull(panel.Parent);
Assert.AreEqual(2, panel.Children.Count());
Assert.AreEqual(ws.Cell(6, 6), panel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 10), panel.Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.First());
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(ws.Cell(8, 6), panel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Children.Last().Range.LastCell());
Assert.AreEqual("childRange2", ((IExcelNamedPanel)panel.Children.Last()).Name);
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(1, panel.Children.First().Children.Count());
Assert.AreEqual(ws.Cell(7, 6), panel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 10), panel.Children.First().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelDataSourcePanel>(panel.Children.First().Children.First());
Assert.AreEqual("childOfChildRange1", ((IExcelNamedPanel)panel.Children.First().Children.First()).Name);
Assert.AreSame(panel.Children.First(), panel.Children.First().Children.First().Parent);
Assert.AreEqual(1, panel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(9, 6), panel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Children.Last().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.Last().Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.Last().Children.First());
Assert.AreSame(panel.Children.Last(), panel.Children.Last().Children.First().Parent);
Assert.AreEqual(3, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
public void TestRemoveName()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IExcelNamedPanel parentPanel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor);
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IExcelNamedPanel childPanel1 = new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor);
childPanel1.Parent = parentPanel;
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
IExcelPanel childPanel2 = new ExcelPanel(childRange2, excelReport, templateProcessor);
childPanel2.Parent = parentPanel;
parentPanel.Children = new List <IExcelPanel> {
childPanel1, childPanel2
};
IXLRange childOfChild1Range = ws.Range(1, 1, 1, 4);
childOfChild1Range.AddToNamed("ChildOfChild1", XLScope.Worksheet);
IXLNamedRange namedChildOfChild1RangeRange = ws.NamedRange("ChildOfChild1");
IExcelNamedPanel childOfChild1Panel = new ExcelNamedPanel(namedChildOfChild1RangeRange, excelReport, templateProcessor);
childOfChild1Panel.Parent = childPanel1;
childPanel1.Children = new List <IExcelPanel> {
childOfChild1Panel
};
IXLRange childOfChild2Range = ws.Range(3, 1, 3, 4);
childOfChild2Range.AddToNamed("ChildOfChild2", XLScope.Worksheet);
IXLNamedRange namedChildOfChild2RangeRange = ws.NamedRange("ChildOfChild2");
IExcelNamedPanel childOfChild2Panel = new ExcelNamedPanel(namedChildOfChild2RangeRange, excelReport, templateProcessor);
childOfChild2Panel.Parent = childPanel2;
childPanel2.Children = new List <IExcelPanel> {
childOfChild2Panel
};
parentPanel.RemoveName();
Assert.AreEqual(3, ws.NamedRanges.Count());
Assert.IsNull(ws.NamedRanges.SingleOrDefault(r => r.Name == "Parent"));
range.AddToNamed("Parent", XLScope.Worksheet);
Assert.AreEqual(4, ws.NamedRanges.Count());
Assert.IsNotNull(ws.NamedRanges.SingleOrDefault(r => r.Name == "Parent"));
parentPanel.RemoveName(true);
Assert.AreEqual(0, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
public void TestCopy()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange = ws.Range(2, 1, 3, 4);
childRange.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IXLRange childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange = ws.NamedRange("ChildOfChild");
var panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelDataSourcePanel("fn:DataSource:Method()", namedChildOfChildRange, excelReport, templateProcessor)
{
RenderPriority = 30,
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
BeforeRenderMethodName = "BeforeRenderMethod3",
AfterRenderMethodName = "AfterRenderMethod3",
BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName",
AfterDataItemRenderMethodName = "AfterDataItemRenderMethodName",
GroupBy = "2,4",
}
},
RenderPriority = 20,
ShiftType = ShiftType.Row,
BeforeRenderMethodName = "BeforeRenderMethod2",
AfterRenderMethodName = "AfterRenderMethod2",
}
},
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod1",
AfterRenderMethodName = "AfterRenderMethod1",
};
IExcelNamedPanel copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreEqual(10, copiedPanel.RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.ShiftType);
Assert.AreEqual("BeforeRenderMethod1", copiedPanel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod1", copiedPanel.AfterRenderMethodName);
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count);
Assert.AreSame(excelReport, copiedPanel.Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreEqual(20, copiedPanel.Children.First().RenderPriority);
Assert.AreEqual(PanelType.Vertical, copiedPanel.Children.First().Type);
Assert.AreEqual(ShiftType.Row, copiedPanel.Children.First().ShiftType);
Assert.AreEqual("BeforeRenderMethod2", copiedPanel.Children.First().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod2", copiedPanel.Children.First().AfterRenderMethodName);
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.AreEqual(1, copiedPanel.Children.First().Children.Count);
Assert.AreSame(excelReport, copiedPanel.Children.First().Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First().Children.First()).Name, @"Parent_[0-9a-f]{32}_Child_ChildOfChild"));
Assert.IsInstanceOf <ExcelDataSourcePanel>(copiedPanel.Children.First().Children.First());
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
Assert.AreEqual(30, copiedPanel.Children.First().Children.First().RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Children.First().Children.First().Type);
Assert.AreEqual(ShiftType.Row, copiedPanel.Children.First().Children.First().ShiftType);
Assert.AreEqual("BeforeRenderMethod3", copiedPanel.Children.First().Children.First().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod3", copiedPanel.Children.First().Children.First().AfterRenderMethodName);
Assert.AreEqual("BeforeDataItemRenderMethodName", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).BeforeDataItemRenderMethodName);
Assert.AreEqual("AfterDataItemRenderMethodName", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).AfterDataItemRenderMethodName);
Assert.AreEqual("2,4", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).GroupBy);
Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);
namedRange.Delete();
namedChildRange.Delete();
copiedPanel.Delete();
copiedPanel.Children.First().Delete();
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 20, 20), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
namedRange = ws.NamedRange("Parent");
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
namedChildRange = ws.NamedRange("Child");
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
namedChildOfChildRange = ws.NamedRange("ChildOfChild");
panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor),
new ExcelPanel(childRange2, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange, excelReport, templateProcessor)
},
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod",
AfterRenderMethodName = "AfterRenderMethod",
},
},
};
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreSame(globalParent, copiedPanel.Parent);
Assert.AreEqual(2, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.IsInstanceOf <ExcelPanel>(copiedPanel.Children.Last());
Assert.IsNotInstanceOf <ExcelNamedPanel>(copiedPanel.Children.Last());
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Range.LastCell());
Assert.AreEqual(10, copiedPanel.Children.Last().RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Children.Last().Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.Children.Last().ShiftType);
Assert.AreEqual("BeforeRenderMethod", copiedPanel.Children.Last().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod", copiedPanel.Children.Last().AfterRenderMethodName);
Assert.AreSame(copiedPanel, copiedPanel.Children.Last().Parent);
Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.Last().Children.First()).Name, @"ChildOfChild_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.Last(), copiedPanel.Children.Last().Children.First().Parent);
namedRange.Delete();
namedChildRange.Delete();
namedChildOfChildRange.Delete();
copiedPanel.Delete();
copiedPanel.Children.First().Delete();
copiedPanel.Children.Last().Children.First().Delete();
globalParent = new ExcelPanel(ws.Range(1, 1, 7, 7), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
namedRange = ws.NamedRange("Parent");
childRange = ws.Range(1, 1, 1, 4);
childRange.AddToNamed("Child", XLScope.Worksheet);
namedChildRange = ws.NamedRange("Child");
panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel> {
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
},
};
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5), false);
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(0, copiedPanel.Children.Count);
ExceptionAssert.Throws <ArgumentNullException>(() => panel.Copy(null));
//wb.SaveAs("test.xlsx");
}
public void TestCopyWithName()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange1 = ws.Range(1, 1, 2, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IXLRange childRange2 = ws.Range(3, 1, 3, 4);
IXLRange childOfChildRange1 = ws.Range(1, 1, 1, 4);
childOfChildRange1.AddToNamed("ChildOfChild1", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange1 = ws.NamedRange("ChildOfChild1");
IXLRange childOfChildRange2 = ws.Range(3, 1, 3, 4);
childOfChildRange2.AddToNamed("ChildOfChild2", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange2 = ws.NamedRange("ChildOfChild2");
var panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange1, excelReport, templateProcessor)
}
},
new ExcelPanel(childRange2, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange2, excelReport, templateProcessor)
}
}
}
};
IExcelNamedPanel copiedPanel = panel.Copy(ws.Cell(5, 5), "Copied");
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, "Copied"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(2, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, "Copied_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.IsInstanceOf <ExcelPanel>(copiedPanel.Children.Last());
Assert.IsNotInstanceOf <ExcelNamedPanel>(copiedPanel.Children.Last());
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.Last().Parent);
Assert.AreEqual(1, copiedPanel.Children.First().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First().Children.First()).Name, "Copied_Child_ChildOfChild1"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);
Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.Last().Children.First()).Name, "ChildOfChild2_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.Last(), copiedPanel.Children.Last().Children.First().Parent);
copiedPanel = panel.Copy(ws.Cell(5, 5), "Copied2", false);
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, "Copied2"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(0, copiedPanel.Children.Count);
ExceptionAssert.Throws <ArgumentNullException>(() => panel.Copy(null, "Copied"));
ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), null));
ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), string.Empty));
ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), " "));
//wb.SaveAs("test.xlsx");
}
private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
{
int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
//freeze panels
performanceSheet.SheetView.Freeze(1, 2);
//performance global styles
performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
//column specific formatting
//date columns
performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";
//percentage formatting (0.00%)
performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.NumberFormatId = 10;
//no decimal points
performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";
//decimal format (0.00)
performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "0.00";
//three decimal points (0.000)
performanceSheet.Range(string.Format("U2:U{0}",
lastRowUsed
)).Style.NumberFormat.Format = "0.000";
//money with two decimals ($ 0.00)
performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.00";
//money with three decimals ($ 0.000)
performanceSheet.Range(string.Format("V2:V{0}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.000";
// adjust to content
performanceSheet.Columns().AdjustToContents();
}
private async Task InsertStockInContent(IXLWorksheet ws, DataTable stocks)
{
int x = 8;
for (int i = 0; i < stocks.Rows.Count; i++)
{
ws.Cell($"A{x}").Value = stocks.Rows[i].Field <dynamic>(0);
ws.Range($"A{x}:B{x}").Merge();
ws.Range($"A{x}:B{x}").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin).Border
.SetOutsideBorderColor(XLColor.Black);
ws.Cell($"C{x}").Value = stocks.Rows[i].Field <dynamic>(1);
ws.Range($"C{x}:G{x}").Merge();
ws.Range($"C{x}:G{x}").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin).Border
.SetOutsideBorderColor(XLColor.Black);
ws.Cell($"H{x}").Value = stocks.Rows[i].Field <dynamic>(2);
ws.Range($"H{x}:I{x}").Merge();
ws.Range($"H{x}:I{x}").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin).Border
.SetOutsideBorderColor(XLColor.Black);
x++;
}
//Sum quantity and add row
ws.Cell($"A{x}").Value = "TOTAL";
ws.Range($"A{x}:B{x}").Merge();
ws.Range($"A{x}:B{x}").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin).Border
.SetOutsideBorderColor(XLColor.Black);
ws.Range($"C{x}:G{x}").Merge();
ws.Range($"C{x}:G{x}").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin).Border
.SetOutsideBorderColor(XLColor.Black);
var totalQ = stocks.AsEnumerable().Sum(i => i.Field <double>(2));
ws.Cell($"H{x}").Value = totalQ.ToString();
ws.Range($"H{x}:I{x}").Merge();
ws.Range($"H{x}:I{x}").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin).Border
.SetOutsideBorderColor(XLColor.Black);
await Task.Delay(0);
}
private async Task DrawStockOutTable(IXLWorksheet ws)
{
//string fontName = "Arial";
//double fontSize = 10;
ws.Row(8).Height = 30;
ws.Cell("A8").Value = "Order No";
ws.Range("A8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("A8").Style.Font.FontColor = XLColor.White;
ws.Cell("B8").Value = "Order Date";
ws.Range("B8:C8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("B8:C8").Style.Font.FontColor = XLColor.White;
ws.Range("B8:C8").Merge();
ws.Cell("D8").Value = "Item Name";
ws.Range("D8:G8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("D8:G8").Style.Font.FontColor = XLColor.White;
ws.Range("D8:G8").Merge();
ws.Cell("H8").Value = "Qty";
ws.Range("H8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("H8").Style.Font.FontColor = XLColor.White;
ws.Cell("I8").Value = "Price";
ws.Range("I8:J8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("I8:J8").Style.Font.FontColor = XLColor.White;
ws.Range("I8:J8").Merge();
ws.Cell("K8").Value = "Discount";
ws.Range("K8:L8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("K8:L8").Style.Font.FontColor = XLColor.White;
ws.Range("K8:L8").Merge();
ws.Cell("M8").Value = "Amount Paid";
ws.Range("M8:N8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("M8:N8").Style.Font.FontColor = XLColor.White;
ws.Range("M8:N8").Merge();
await Task.Delay(0);
}
public void AddContainerInfo(IXLWorksheet ws, Container container)
{
ws.Cell("A1").SetValue(container.ExporterName).Style.Font.FontSize = 20;
ws.Range("A1:E1").Merge();
ws.Cell("A2").SetValue(container.ExporterAddress).Style.Alignment.WrapText = true;
ws.Range("A2:B3").Merge();
ws.Cell("A5").SetValue("Shipped Per");
ws.Cell("B5").SetValue(container.ShippedPer);
ws.Range("B5:C5").Merge();
ws.Cell("A6").SetValue("On/About");
ws.Cell("B6").SetValue(container.OnAbout);
ws.Range("B6:C6").Merge();
ws.Cell("A7").SetValue("From");
ws.Cell("B7").SetValue(container.From);
// ws.Row(7)
// .Style
// .Alignment.SetVertical(XLAlignmentVerticalValues.Top)
// .Alignment.SetWrapText(true);
ws.Range("B7:C7").Merge();
ws.Row(7).Height = 70;
ws.Cell("A8").SetValue("Airway Bill No. \nor B/L No.");
ws.Cell("B8").SetValue(container.AirwayBillNumber);
ws.Range("B8:C8").Merge();
ws.Row(8).Height = 30;
// ws.Row(8).Style.Alignment.SetWrapText(true);
ws.Cell("A9").SetValue("Letter of\nCredit No.");
ws.Cell("B9").SetValue(container.LetterOfCreditNumber);
ws.Range("B9:C9").Merge();
ws.Row(9).Height = 30;
ws.Cell("A10").SetValue("Drawn Under");
ws.Cell("B10").SetValue(container.DrawnUnder)
.Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// .Alignment.SetWrapText(true);
ws.Range("B10:C10").Merge();
ws.Row(10).Height = 70;
// ws.Row(10).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Range("A1:A10").Style.Font.Bold = true;
ws.Range("B5:C10").Style.Border.BottomBorder = XLBorderStyleValues.Thin;
// ws.Range("B5:C10").Style.Alignment.WrapText = true;
ws.Rows("5:10").Style.Alignment.SetWrapText(true)
.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
//Importer
ws.Cell("E5").SetValue(container.ImporterName +
"\n" + container.ImporterAddress +
"\n" + "(TAX CERTIFICATE NO. " + container.ImporterTaxCertificateNumber + ")")
.Style
.Alignment.SetVertical(XLAlignmentVerticalValues.Top)
.Alignment.SetWrapText();
ws.Range("E5:H10").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
//Container Number + Container Date
ws.Cell("F2").SetValue("INVOICE NO:");
ws.Cell("G2").SetValue(container.CustomsInvoiceNumber);
ws.Cell("F3").SetValue("DATE:");
ws.Cell("G3").SetValue(container.Date);
ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range("G2:H2").Merge();
ws.Range("G3:H3").Merge();
ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Range("F2:H3").Style.Font.Bold = true;
}
public static void Start(List <Operation> operacije)
{
try
{
string filename = "test_" + Guid.NewGuid().ToString() + ".xlsx";
XLWorkbook xLWorkbook = new XLWorkbook();
IXLWorksheet s1 = xLWorkbook.Worksheets.Add("Operacije");
IXLWorksheet s2 = xLWorkbook.Worksheets.Add("Radni nalozi");
IXLWorksheet s3; // = xLWorkbook.Worksheets.Add("Kapaciteti");
int i = 0;
int?prethodniRN = null;
#region S1
//SHEET 1
int counter = 0;
foreach (Operation op in operacije)
{
s1.Cell(i + 1, 1).Value = op.WorkOrderId;
//if (op.ArtikalIdNad != null)
// s1.Cell(i + 1, 2).Value = "[" + op.ArtikalIdNad.ToString() + "/" + op.VerzijaIdNad.ToString() + "]" + Environment.NewLine;
//s1.Cell(i + 1, 2).Value += "[" + op.ArtikalId.ToString() + "/" + op.VerzijaId.ToString() + "]" + op.Artikal;
//s1.Cell(i + 1, 3).Value = op.RadniNalogNadredjeniId;
s1.Cell(i + 1, 4).Value = op.Id;
s1.Cell(i + 1, 5).Value = op.ResourceId;
s1.Cell(i + 1, 6).Value = op.Description;
s1.Cell(i + 1, 7).Value = op.ResourceName;
//s1.Cell(i + 1, 8).Value = op.Kolicina;
//s1.Cell(i + 1, 9).Value = op.PripremnoVreme;
//s1.Cell(i + 1, 10).Value = op.VremeZaSeriju;
s1.Cell(i + 1, 11).Value = op.StartDateTime;
s1.Cell(i + 1, 12).Value = op.EndDateTime;
s1.Cell(i + 1, 13).Style.DateFormat.Format = "dd.MM.yyyy HH:mm:ss";
s1.Cell(i + 1, 14).Style.DateFormat.Format = "dd.MM.yyyy HH:mm:ss";
s1.Cell(i + 1, 15).Value = op.Duration;
if (prethodniRN != op.WorkOrderId)
{
s1.Cell(i + 1, 11).Style.Font.Bold = true;
}
if (operacije.Count == i + 1 || operacije[i + 1].WorkOrderId != op.WorkOrderId)
{
s1.Cell(i + 1, 12).Style.Font.Bold = true;
s1.Row(i + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
s1.Cell(i - counter + 1, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
s1.Range(i - counter + 1, 2, i + 1, 2).Merge();
s1.Cell(i - counter + 1, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
s1.Range(i - counter + 1, 2, i + 1, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
counter = 0;
}
else
{
counter++;
}
s1.Row(i + 1).Style.Fill.BackgroundColor = colors[op.Level];
//if (op.Kolicina == 0)
// s1.Row(i + 1).Style.Font.Bold = true;
prethodniRN = op.WorkOrderId;
i++;
}
s1.Columns(1, 12).AdjustToContents();
#endregion S1
#region S2
DateTime?minDatum = operacije.Min(ope => ope.StartDateTime);
DateTime?maxDatum = operacije.Max(ope => ope.EndDateTime);
int diff = (int)maxDatum.Value.Date.Subtract(minDatum.Value.Date).TotalDays;
//SHEET 2
i = 0;
int rowIndex = 1;
for (int k = 0; k <= diff; k++)
{
s2.Cell(1, 2 + k).Value = minDatum.Value.Date.AddDays(k);
s2.Style.DateFormat.Format = "dd.MM.yyyy";
s2.Style.Font.Bold = true;
s2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
while (1 == 1)
{
if (operacije.Count == i)
{
break;
}
var op = operacije[i];
int j = 0;
while (1 == 1)
{
var opp = operacije[i + j];
if (opp.StartDateTime != null && opp.EndDateTime != null)
{
if (s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Value.ToString() != "")
{
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Value += Environment.NewLine +
opp.StartDateTime.Value.ToString("HH:mm") + "-" + opp.EndDateTime.Value.ToString("HH:mm") + " (" + opp.Id.ToString() + ")" + opp.Description;
}
else
{
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Value =
opp.StartDateTime.Value.ToString("HH:mm") + "-" + opp.EndDateTime.Value.ToString("HH:mm") + " (" + opp.Id.ToString() + ")" + opp.Description;
}
//s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Style.Fill.BackgroundColor = colors[op.Level];
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Style.Alignment.WrapText = true;
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).DataType = XLCellValues.Text;
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Style.Font.FontSize = 7;
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
s2.Cell(rowIndex + 1, 2 + (int)opp.StartDateTime.Value.Date.Subtract(minDatum.Value.Date).TotalDays).Style.Font.Bold = false;
}
j++;
if (operacije.Count == i + j || opp.WorkOrderId != operacije[i + j].WorkOrderId)
{
break;
}
}
i = i + j - 1;
var minope = operacije.Where(ope => ope.WorkOrderId == op.WorkOrderId && ope.StartDateTime != null).ToList();
int rnStart = 0;
if (minope.Count != 0)
{
rnStart = (int)minope.Min(ope => ope.StartDateTime).Value.Date.Subtract(minDatum.Value.Date).TotalDays;
}
var maxope = operacije.Where(ope => ope.WorkOrderId == op.WorkOrderId && ope.EndDateTime != null).ToList();
int rnEnd = 0;
if (maxope.Count != 0)
{
rnEnd = (int)maxope.Max(ope => ope.EndDateTime).Value.Date.Subtract(minDatum.Value.Date).TotalDays;
}
s2.Cell(rowIndex + 1, 1).Value = op.WorkOrderId;
//if (op.WorkOrderId == 91100)
//{
// int ss = 0;
//}
if (rnStart != 0 || rnEnd != 0)
{
s2.Range(rowIndex + 1, 2 + rnStart, rowIndex + 1, 2 + rnEnd).Style.Fill.BackgroundColor = colors[op.Level];
}
//s2.Cell(rowIndex + 1, 1).Style.Fill.BackgroundColor = colors[op.Level];
//s2.Range(rowIndex + 1, 2 + rnStart, rowIndex + 1, 2 + rnEnd).Merge();
//s2.Row(rowIndex + 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
i++;
rowIndex++;
}
s2.Rows(1, rowIndex).AdjustToContents();
s2.Columns(1, diff + 2).AdjustToContents();
#endregion S2
#region s3
//DateTime? minDatum = operacije.Min(ope => ope.StartDateTime);
//DateTime? maxDatum = operacije.Max(ope => ope.EndDateTime);
//int diff = (int)maxDatum.Value.Date.Subtract(minDatum.Value.Date).TotalDays;
//SHEET 3
List <Operation> masine = operacije
.GroupBy(p => p.ResourceId)
.Select(g => g.First()).OrderBy(m => m.ResourceId)
.ToList();
for (int l = 0; l < masine.Count; l++)
{
minDatum = operacije.Where(ope => ope.ResourceId == masine[l].ResourceId).Min(ope => ope.StartDateTime);
maxDatum = operacije.Where(ope => ope.ResourceId == masine[l].ResourceId).Max(ope => ope.EndDateTime);
if (minDatum != null && maxDatum != null)
{
diff = (int)maxDatum.Value.Date.Subtract(minDatum.Value.Date).TotalDays;
var ts = minDatum.Value.Date.Add(TimeSpan.FromMinutes(420));
s3 = xLWorkbook.Worksheets.Add(masine[l].ResourceId.ToString() + "-" + (masine[l].ResourceName != null ? masine[l].ResourceName : "").Replace("[", "").Replace("]", "").Replace("?", "").Replace("\\", "").Replace("/", ""));
for (int s = 1; s < 190; s++)
{
DateTime val = ts.Add(TimeSpan.FromMinutes(s * 5));
s3.Cell(s + 2, 1 + 0 * (diff + 1)).Value = val;
s3.Cell(s + 2, 1 + 0 * (diff + 1)).DataType = XLCellValues.DateTime;
s3.Column(1 + 0 * (diff + 1)).Style.DateFormat.Format = "HH:mm";
s3.Column(1 + 0 * (diff + 1)).Style.Font.Bold = true;
}
var masina = masine[l];
s3.Cell(1, 1 + 0 * (diff + 1)).Value = "[" + masina.ResourceId.ToString() + "]" + masina.ResourceName;
s3.Cell(1, 1 + 0 * (diff + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
i = 0;
rowIndex = 1;
for (int k = 0; k <= diff; k++)
{
s3.Cell(2, 2 + k + 0 * (diff + 1)).Value = minDatum.Value.Date.AddDays(k);
s3.Cell(2, 2 + k + 0 * (diff + 1)).Style.DateFormat.Format = "dd.MM.yyyy";
s3.Cell(2, 2 + k + 0 * (diff + 1)).Style.Font.Bold = true;
s3.Cell(2, 2 + k + 0 * (diff + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ts = minDatum.Value.Date.AddDays(k).Date.Add(TimeSpan.FromMinutes(420));
var operacijeMasine = operacije.Where(ope => ope.EndDateTime != null).Where(o => o.ResourceId == masina.ResourceId && ((o.StartDateTime.Value.Date <minDatum.Value.Date.AddDays(k).Date&& o.EndDateTime.Value.Date> minDatum.Value.Date.AddDays(k).Date) || o.StartDateTime.Value.Date == minDatum.Value.Date.AddDays(k).Date || o.EndDateTime.Value.Date == minDatum.Value.Date.AddDays(k).Date)).OrderBy(op => op.StartDateTime).ToList();
//if (operacijeMasine.Count > 0)
//{
// int jj = 0;
//}
for (int s = 1; s < 190; s++)
{
DateTime val = ts.Add(TimeSpan.FromMinutes(s * 5));
var opp = operacijeMasine.Where(o => o.StartDateTime <= val && o.EndDateTime >= val).ToList();
if (opp.Count() > 0)
{
s3.Cell(s + 2, 2 + k + 0 * (diff + 1)).Style.Fill.BackgroundColor = colors[1];
s3.Cell(s + 2, 2 + k + 0 * (diff + 1)).Value = opp.First().WorkOrderId.ToString();
//s3.Cell(s + 2, 2 + k + 0 * (diff + 1)).Comment.AddText(val.ToString("dd.MM.yyyy HH:mm:ss"));
s3.Cell(s + 2, 2 + k + 0 * (diff + 1)).Style.Alignment.WrapText = true;
}
}
//s3.Column(2 + k + l * (diff + 1)).Style.Fill.BackgroundColor = colors[l % 2];
s3.Column(2 + k + 0 * (diff + 1)).AdjustToContents();
}
s3.Range(1, 1 + (diff + 1) * 0, 1, (diff + 1) * (0 + 1)).Merge();
////////s3.Range(1, 1 + (diff + 1) * l, 1, (diff + 1) * (l + 1)).Style.Fill.BackgroundColor = colors[1];
s3.Range(1, 1 + (diff + 1) * 0, 1, (diff + 1) * (0 + 1)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
//s3.Range(1, 15, 1, 27).Merge();
//s3.Range(1, 28, 1, 40).Merge();
while (1 == 1)
{
if (operacije.Count == i)
{
break;
}
var op = operacije[i];
i = i + 1;
}
}
//dr.Close();
}
#endregion s3
MemoryStream ms = new MemoryStream();
xLWorkbook.SaveAs(ms);
FileStream file = new FileStream(filename, FileMode.Create, FileAccess.Write);
ms.WriteTo(file);
file.Close();
ms.Close();
System.Diagnostics.Process.Start(filename);
//conn.Close();
}
catch (Exception e)
{
//MessageBox.Show(e.Message);
}
}
public XLWorkbook ExportExcel(BaseReportModel model, List <DateTime> listDates, List <DetailItemizedSalesAnalysisReportHeaderModels> data, List <StoreModels> lstStore)
{
XLWorkbook wb = new XLWorkbook();
XLColor backgroundTitle = XLColor.FromHtml("#d9d9d9");
XLColor outsideBorderColor = XLColor.FromHtml("#000000");
XLColor insideBorderColor = XLColor.FromHtml("#000000");
//Create worksheet
IXLWorksheet ws = wb.Worksheets.Add("Detail_Itemized_Sales_Analysis" /*_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Detail_Itemized_Sales_Analysis")*/);
int startRow = 3;
int row = startRow;
int maxCol = 3 + listDates.Count;
int startCol = 4;
List <int> listCenterStyles = new List <int>();
listCenterStyles.Add(1);
CreateReportHeader(ws, maxCol, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Detail Itemized Sales Analysis"));
//List<DetailItemizedSalesAnalysisReportHeaderModels> listStores = (from s in data
// group s by s.StoreId into g
// select new DetailItemizedSalesAnalysisReportHeaderModels
// {
// StoreId = g.Key,
// }).ToList();
if (model.ListStores.Count == 0)
{
ws.Cell("A4").Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("NO DATA"));
return(wb);
}
//table
row++;
ws.Cell("A" + row).Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Item Name"));
ws.Cell("B" + row).Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total"));
ws.Cell("C" + row).Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Price"));
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = backgroundTitle;
ws.Range(row, 1, row, maxCol).Style.Font.SetBold(true);
ws.Range(row, 1, row, maxCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
for (int i = 0; i < listDates.Count; i++)
{
ws.Cell(row, 4 + i).SetValue(listDates[i]);
ws.Cell(row, 4 + i).Style.DateFormat.Format = "mm/dd";
}
row++;
string storeName = string.Empty, storeId = string.Empty;
//table content
foreach (StoreModels store in lstStore)
{
//store name
storeName = store.Name;
storeId = store.Id;
//category name
List <DetailItemizedSalesAnalysisReportHeaderModels> ListChilds = (from c in data
where c.StoreId == storeId
orderby c.ItemTypeId, c.CategoryName
group c by new
{
CategoryId = c.CategoryId,
CategoryName = c.CategoryName,
ItemTypeId = c.ItemTypeId // trongntn
} into g
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CategoryId = g.Key.CategoryId,
CategoryName = g.Key.CategoryName,
ItemTypeId = g.Key.ItemTypeId
}).ToList();
if (ListChilds.Count > 0)
{
ws.Range(row, 1, row, maxCol).Merge().Value = string.Format("{0}", storeName);
ws.Range(row, 1, row, maxCol).Style.Font.SetBold(true);
ws.Range(row, 1, row, maxCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row++;
}
foreach (DetailItemizedSalesAnalysisReportHeaderModels cate in ListChilds /*store.ListChilds.OrderBy(c => c.TypeID).ThenBy(c => c.CateName)*/)
{
if (cate.ItemTypeId != 4) // Category for Dish
{
ws.Range(row, 1, row, maxCol).Merge().Value = string.Format(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Category") + ": {0}", cate.CategoryName);
ws.Range(row, 1, row, maxCol).Style.Font.SetBold(true);
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
row++;
}
//=====
List <DetailItemizedSalesAnalysisReportHeaderModels> ListdisSet = (from ds in data
where ds.StoreId == storeId && ds.CategoryId == cate.CategoryId
orderby ds.ItemTypeId, ds.ItemName
group ds by new
{
ItemId = ds.ItemId,
ItemName = ds.ItemName,
Price = ds.Price,
ItemTypeId = ds.ItemTypeId
} into g
select new DetailItemizedSalesAnalysisReportHeaderModels
{
ItemId = g.Key.ItemId,
ItemName = g.Key.ItemName,
Qty = g.Sum(x => x.Qty),
Price = g.Key.Price,
ItemTypeId = g.Key.ItemTypeId
}).ToList();
foreach (DetailItemizedSalesAnalysisReportHeaderModels disSet in ListdisSet /*cate.ListChilds.OrderBy(c => c.TypeID).ThenBy(c => c.ItemName)*/)
{
//dish or setmenu data
if (disSet.ItemTypeId == 4)
{
//set font and background color for row setmenu
row++;
ws.Row(row).Style.Font.SetFontColor(XLColor.Red);
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
}
//item name
ws.Cell(row, 1).Value = string.Format("{0}", disSet.ItemName);
ws.Range(row, 1, row, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
//item price
ws.Cell(row, 3).SetValue(string.Format("${0:0.00}", disSet.Price));
ws.Range(row, 3, row, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
double total = 0;
//quantity
List <DetailItemizedSalesAnalysisReportHeaderModels> ListQty = (from q in data
where q.StoreId == storeId &&
q.CategoryId == cate.CategoryId &&
q.ItemId == disSet.ItemId &&
q.Price == disSet.Price
orderby q.CreatedDate
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CreatedDate = q.CreatedDate,
Qty = q.Qty
}).ToList();
foreach (DetailItemizedSalesAnalysisReportHeaderModels dateQty in ListQty /*disSet.ListChilds.OrderBy(item => item.DateCreated)*/)
{
ws.Cell(row, startCol + GetDateIndex(listDates, dateQty.CreatedDate)).Value = string.Format("{0}", dateQty.Qty);
ws.Range(row, 1, row, startCol + GetDateIndex(listDates, dateQty.CreatedDate)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
total += dateQty.Qty;
}
//total
ws.Cell(row, 2).Value = string.Format("{0}", total);
ws.Range(row, 1, row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row++;
//if set menu has child -> setmenu child
List <DetailItemizedSalesAnalysisReportHeaderModels> ListSetChilds = (from sc in data
where sc.StoreId == storeId &&
sc.CategoryId == cate.CategoryId &&
sc.ItemId == disSet.ItemId &&
sc.ItemTypeId == 4
orderby sc.CategoryName, sc.ItemName
select new DetailItemizedSalesAnalysisReportHeaderModels
{
ItemName = sc.ItemName,
Price = sc.Price
}).ToList();
if (disSet.ItemTypeId == 4 && /*disSet.ListSetChilds*/ ListSetChilds != null && /*disSet.ListSetChilds.Count*/ ListSetChilds.Count > 0)
{
foreach (DetailItemizedSalesAnalysisReportHeaderModels setChild in ListSetChilds /*disSet.ListSetChilds.OrderBy(c => c.CateName).ThenBy(c => c.ItemName)*/)
{
ws.Cell(row, 1).Value = string.Format("{0}", setChild.ItemName);
ws.Cell(row, 3).SetValue(string.Format("${0:0.00}", setChild.Price));
total = 0;
//=====
List <DetailItemizedSalesAnalysisReportHeaderModels> scListChilds = (from c in data
where c.StoreId == storeId &&
c.CategoryId == cate.CategoryId &&
c.ItemId == disSet.ItemId &&
c.ItemName == setChild.ItemName && //
c.ItemTypeId == 4
orderby c.CreatedDate
select new DetailItemizedSalesAnalysisReportHeaderModels
{
CreatedDate = c.CreatedDate,
Qty = c.Qty
}).ToList();
foreach (DetailItemizedSalesAnalysisReportHeaderModels dateQty in scListChilds /*setChild.ListChilds.OrderBy(item => item.DateCreated)*/)
{
ws.Cell(row, startCol + GetDateIndex(listDates, dateQty.CreatedDate)).Value = string.Format("{0}", dateQty.Qty);
total += dateQty.Qty;
}
ws.Cell(row, 2).Value = string.Format("{0}", total);
ws.Range(row, 1, row, maxCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
row++;
}
}
}
}
}
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.OutsideBorderColor = outsideBorderColor;
ws.Range(startRow, 1, row - 1, maxCol).Style.Border.InsideBorderColor = insideBorderColor;
//set width of cells
ws.Column(1).Width = 35;
ws.Columns(2, maxCol).Width = 10;
return(wb);
}
public bool insertTemplateFileErrorValue(string path, string newpath, DataTable dt, DataTable dtSCon, DataTable dtSOW, DataTable dtRegion)
{
bool result = false;
try
{
using (XLWorkbook workBook = new XLWorkbook(path))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
for (int i = 0; i < dt.Rows.Count; i++)
{
workSheet.Cell(i + 3, 1).Value = (i + 1).ToString();
workSheet.Cell(i + 3, 2).Value = dt.Rows[i]["workpackageid"].ToString();
workSheet.Cell(i + 3, 3).Value = dt.Rows[i]["Customer_PO"].ToString();
workSheet.Cell(i + 3, 4).Value = dt.Rows[i]["Customer_PO_Date"].ToString();
workSheet.Cell(i + 3, 5).Value = dt.Rows[i]["PO_Description"].ToString();
workSheet.Cell(i + 3, 6).Value = dt.Rows[i]["Region"].ToString();
workSheet.Cell(i + 3, 7).Value = dt.Rows[i]["Site_ID"].ToString();
workSheet.Cell(i + 3, 8).Value = dt.Rows[i]["ScopeOfWork"].ToString();
workSheet.Cell(i + 3, 9).Value = dt.Rows[i]["Subcone_Name"].ToString();
workSheet.Cell(i + 3, 10).Value = dt.Rows[i]["Site_Model"].ToString();
workSheet.Cell(i + 3, 11).Value = dt.Rows[i]["Remarks"].ToString();
}
IXLWorksheet workSheetSCon = workBook.Worksheet(2);
for (int i = 0; i < dtSCon.Rows.Count; i++)
{
workSheetSCon.Cell(i + 2, 1).Value = dtSCon.Rows[i]["SCon_Name"].ToString();
}
IXLWorksheet workSheetSOW = workBook.Worksheet(3);
string merge_start = "";
string merge_finish = "";
for (int i = 0; i < dtSOW.Rows.Count; i++)
{
workSheetSOW.Cell(i + 2, 1).Value = dtSOW.Rows[i]["general_sow"].ToString();
workSheetSOW.Cell(i + 2, 2).Value = dtSOW.Rows[i]["detail_sow"].ToString();
if (i > 0)
{
if (dtSOW.Rows[i]["general_sow"].ToString() == dtSOW.Rows[i - 1]["general_sow"].ToString())
{
merge_start = string.IsNullOrEmpty(merge_start) ? "A" + (i + 1).ToString() : merge_start;
merge_finish = ":A" + ((i + 2).ToString());
workSheetSOW.Range(merge_start + merge_finish).Merge();
workSheetSOW.Range(merge_start + merge_finish).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
}
else
{
merge_start = "";
}
}
}
IXLWorksheet workSheetRegion = workBook.Worksheet(4);
for (int i = 0; i < dtRegion.Rows.Count; i++)
{
workSheetRegion.Cell(i + 2, 1).Value = dtRegion.Rows[i]["RGNName"].ToString();
}
workBook.SaveAs(newpath);
result = true;
}
}
catch (Exception e)
{
}
finally
{
}
return(result);
}
private static void SetOxidesTable(IXLWorksheet ws)
{
var mainOxides = Model.OxidesResultModel.First().OxidesResult.Select(x => x.Oxide.Formula);
var oxides = Model.Oxides.Where(x => !mainOxides.Contains(x.Composition.Formula));
var countInsert = oxides.Count() - 3;
if (countInsert > 0)
{
ws.Column("AV").InsertColumnsAfter(oxides.Count() - 3);//44 - 51 template column
ws.Range(5, 44, 8, 51 + countInsert).Merge();
ws.Range(18, 44, 18, 50 + countInsert).Merge();
}
var currentCol = 48;
foreach (var oxide in oxides)
{
var currentCell = ws.Cell(9, currentCol);
currentCell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
currentCell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
SetChemicalFormula(currentCell.RichText, oxide.Composition.Formula);
var currentResCell = ws.Cell(19, currentCol);
SetChemicalFormula(currentResCell.RichText, oxide.Composition.Formula);
currentCol++;
}
}
/// <summary>
/// In thống kê các trường nhóm theo khối
/// </summary>
/// <param name="sheet">worksheet</param>
/// <param name="dt">Dữ liệu nguồn</param>
/// <param name="rowStart">chỉ số hành bắt đầu IN</param>
/// <param name="columns">danh sách các cột</param>
/// <returns>chỉ số hàng tiếp theo có thể được In tiếp dữ liệu</returns>
protected int SetGroup(IXLWorksheet sheet, DataTable dt, int rowStart, List <ColumnDetail> columns)
{
int colSum = columns.Count;
var rowInsert = sheet.Range(rowStart, 1, rowStart, colSum);
rowInsert.InsertRowsBelow(dt.Rows.Count);
int row = Convert.ToInt32(rowStart.ToString());
for (int ro = 0; ro < dt.Rows.Count; ro++)
{
DataRow item = dt.Rows[ro];
int i = 1;
foreach (var col in columns)
{
var cell = sheet.Cell(row, i);
if (ro == dt.Rows.Count - 1)
{
cell.Style.Font.Bold = true;
}
else
{
cell.Style.Font.Bold = false;
}
if (i == 1)
{
//tên trường
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
else //cột thường
{
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
#region Thiết lập cột
switch (col.FieldName)
{
case "DAT_TS": if (ro == dt.Rows.Count - 1)
{
cell.FormulaA1 = string.Format("=SUM({0}:{1})", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(row - 1, 3).Address.ToString());
}
else
{
cell.Value = item[col.FieldName];
}
break;
case "CHUADAT_TS":
if (ro == dt.Rows.Count - 1)
{
cell.FormulaA1 = string.Format("=SUM({0}:{1})", sheet.Cell(rowStart, 5).Address.ToString(), sheet.Cell(row - 1, 5).Address.ToString());
}
else
{
cell.Value = item[col.FieldName];
}
break;
case "SO_LUONGHS":
cell.FormulaA1 = string.Format("=SUM({0},{1})", sheet.Cell(row, 3).Address.ToString(), sheet.Cell(row, 5).Address.ToString());
break;
case "TEN_TRUONG":
cell.Value = item[col.FieldName];
if (ro == dt.Rows.Count - 1)
{
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
break;
case "DAT_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(row, 3).Address.ToString(), sheet.Cell(row, 2).Address.ToString());
break;
case "CHUADAT_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(row, 5).Address.ToString(), sheet.Cell(row, 2).Address.ToString());
break;
case "TBTROLEN_TS":
cell.FormulaA1 = string.Format("=SUM({0})", sheet.Cell(row, 3).Address.ToString());
break;
case "TBTROLEN_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(row, 7).Address.ToString(), sheet.Cell(row, 2).Address.ToString());
break;
case "TBTROLEN_XEP_HANG":
if (ro != dt.Rows.Count - 1)
{
cell.FormulaA1 = string.Format("=RANK({0},{1}:{2},0)", sheet.Cell(row, 8).Address.ToString(), this.GetAddressString(sheet.Cell(rowStart, 8), true), this.GetAddressString(sheet.Cell(rowStart + dt.Rows.Count - 2, 8), true));
}
break;
default: cell.Value = item[col.FieldName];
break;
}
#endregion
//set cell type
if (col.DataType == XLCellValues.Number)
{
cell.DataType = col.DataType;
cell.Style.NumberFormat.NumberFormatId = 10;
}
i++;
}
row++;
}
return(row);
}
/// <summary>
/// Create a worksheet
/// </summary>
/// <param name="worksheet">IXLWorksheet</param>
/// <param name="sheet">CExcelSheet</param>
private void CreateAWorkSheet(IXLWorksheet worksheet, CExcelSheet sheet)
{
//title
IXLAddress firstAdd = worksheet.Cell(1, COL_BEGIN).Address;
worksheet.Cell(1, 1).Value = sheet.Title;
worksheet.Cell(1, 1).Style.Font.Bold = true;
worksheet.Cell(1, 1).Style.Font.FontSize = 15;
worksheet.Cell(1, 1).Style.Font.FontColor = XLColor.White;
worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0x0066cc);
#region Export By
int rowIdx = ROW_BEGIN;
if (sheet.ExportBy != null)
{
foreach (var item in sheet.ExportBy)
{
// reset column
int colIdx = COL_BEGIN;
worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
worksheet.Cell(rowIdx, colIdx).Value = "'" + item.key;
worksheet.Range(
worksheet.Cell(rowIdx, colIdx).Address,
worksheet.Cell(rowIdx, colIdx + 2).Address).Merge();
colIdx += 3;
worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
worksheet.Cell(rowIdx, colIdx).Value = "'" + item.value;
// new row
rowIdx++;
}
}
#endregion
// Header
int col = COL_BEGIN;
#region Header
//Add No
if (sheet.IsRenderNo)
{
worksheet.Cell(rowIdx, col).Value = "No";
worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(rowIdx, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
col++;
}
foreach (string header in sheet.Header)
{
worksheet.Cell(rowIdx, col).Value = header;
worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
//Style for header
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.Bold = true;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.FontSize = 12;
worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
//merge title
IXLAddress secondAdd = worksheet.Cell(1, col - 1).Address;
worksheet.Range(firstAdd, secondAdd).Merge();
worksheet.Range(firstAdd, secondAdd).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Merge export by
for (int i = ROW_BEGIN; i < rowIdx; i++)
{
worksheet.Range(
worksheet.Cell(i, 4).Address,
worksheet.Cell(i, col - 1).Address).Merge();
}
#endregion
// detail
// Comment when adding export by, rowIdx is the number of export by
//int idx_row = ROW_BEGIN + 1;
int idx_row = rowIdx + 1;
string preGroup = string.Empty;
ArrayList beginSubList = new ArrayList();
ArrayList endSubList = new ArrayList();
//linh.quang.le: Freeze panels
worksheet.SheetView.FreezeRows(sheet.FreezeRow);
worksheet.SheetView.FreezeColumns(sheet.FreezeColumn);
//linh.quang.le number
int no = 1;
#region Detail
foreach (Object row in sheet.List)
{
int idx_col = COL_BEGIN;
int index = 0;
bool hasMainColumnValue = HasMainColumnValue(row, sheet);
//linh.quang.le
#region GroupName
if (sheet.IsGroup)
{
string groupName = string.Empty;
groupName = row.GetType().GetProperty(sheet.GroupName).GetValue(row, null).ToString();
if (!String.IsNullOrEmpty(groupName) && preGroup != groupName)
{
if (beginSubList.Count != 0)
endSubList.Add(idx_row - 1);
worksheet.Cell(idx_row, COL_BEGIN).Value = groupName;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.Bold = true;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontSize = 12;
worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontColor = XLColor.Black;
worksheet.Cell(idx_row, COL_BEGIN).Style.Fill.BackgroundColor = XLColor.FromArgb(0xD9D9D9);
IXLAddress firstGroupAddr = worksheet.Cell(idx_row, COL_BEGIN).Address;
IXLAddress secondGroupAddr = worksheet.Cell(idx_row, (COL_BEGIN + (sheet.IsRenderNo ? sheet.ColumnList.Length : sheet.ColumnList.Length - 1))).Address;
worksheet.Range(firstGroupAddr, secondGroupAddr).Merge();
worksheet.Range(firstGroupAddr, secondGroupAddr).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
preGroup = groupName;
no = 1;
idx_row++;
beginSubList.Add(idx_row);
}
if (sheet.IsRenderNo && hasMainColumnValue)
{
worksheet.Cell(idx_row, idx_col).Value = no.ToString();
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
no++;
idx_col++;
}
}
else
{
if (sheet.IsRenderNo)
{
worksheet.Cell(idx_row, idx_col).Value = (idx_row - ROW_BEGIN).ToString();
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
idx_col++;
}
}
#endregion
if (sheet.IsGroup)
{
if (hasMainColumnValue)
{
foreach (string header in sheet.ColumnList)
{
string[] headerArr = header.Split(Convert.ToChar(":"));
Object obj = null;
if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
else
{
string[] arr = (string[])row;
if (arr != null)
obj = arr[index++];
}
string strValue = string.Empty;
strValue = obj == null ? "" : obj.ToString();
#region Format item
if (headerArr.Count() == 2)
{
switch (headerArr[1].ToString().ToLower())
{
case "text":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = "'" + strValue;
break;
case "date":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
break;
case "datetime":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
break;
// Convert from Int of Hour and Minute to "Hour : Minute" string
// Using in Time Mangement Module
// @author : tai.pham
case "hour":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
break;
// Convert from location code to location string
// Using in Time Mangement Module
// @author : tai.pham
case "location":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
break;
case "gender":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
break;
case "married":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
break;
case "labor":
strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
break;
case "hhmm":
worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
break;
case "jr":
strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
break;
case "pr":
strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
break;
case "sr":
strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
break;
case "candidate":
strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
break;
case "actionsendmail":
strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
break;
case "jr_request":
strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
break;
case "dayofweek":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
break;
case "number":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case "duration":
strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
default:
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
}
worksheet.Cell(idx_row, idx_col).Value = strValue;
}
#endregion
else
{
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
}
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
idx_col++;
}
worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
idx_row++;
}
}
else
{
foreach (string header in sheet.ColumnList)
{
string[] headerArr = header.Split(Convert.ToChar(":"));
Object obj = null;
if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
else
{
string[] arr = (string[])row;
if (arr != null)
obj = arr[index++];
}
string strValue = string.Empty;
strValue = obj == null ? "" : obj.ToString();
#region Format item
if (headerArr.Count() == 2)
{
switch (headerArr[1].ToString().ToLower())
{
case "text":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = "'" + strValue;
break;
case "date":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
break;
case "datetime":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
break;
case "gender":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
break;
case "married":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
break;
case "labor":
strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
break;
// Convert from Int of Hour and Minute to "Hour : Minute" string
// Using in Time Management Module
// @author : tai.pham
case "hour":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
break;
// Convert from location code to location string
// Using in Time Mangement Module
// @author : tai.pham
case "location":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
break;
case "hhmm":
worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
break;
case "jr":
strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
break;
case "pr":
strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
break;
case "sr":
strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
break;
case "candidate":
strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
break;
case "actionsendmail":
strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
break;
case "jr_request":
strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
break;
case "dayofweek":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
break;
case "number":
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case "duration":
strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
default:
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
}
worksheet.Cell(idx_row, idx_col).Value = strValue;
}
#endregion
else
{
worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
}
worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
idx_col++;
}
worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
idx_row++;
}
}
if (sheet.IsGroup)
{
if (!String.IsNullOrEmpty(sheet.GroupName))
{
for (int i = 0; i < beginSubList.Count; i++)
{
worksheet.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
if (i >= endSubList.Count)
worksheet.Rows((int)beginSubList[i], idx_row - 1).Group();
else
worksheet.Rows((int)beginSubList[i], (int)endSubList[i]).Group();
}
}
}
#endregion
#region Footer
if (sheet.Footer != null)
{
col = COL_BEGIN;
if (sheet.IsRenderNo)
{
worksheet.Cell(idx_row, col).Value = "Total";
worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
worksheet.Cell(idx_row, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
col++;
}
foreach (string footer in sheet.Footer)
{
worksheet.Cell(idx_row, col).Value = footer;
worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
//Style for footer
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.Bold = true;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.FontSize = 12;
worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
}
#endregion
}
private List<String> ReturnMetricsRow(IXLWorksheet sheet)
{
List<String> metricNames = new List<string>();
_worksheet = sheet;
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value == "Month")
{
var metricRow = _worksheet_range.Row(i);
for (int x = 1; x <= metricRow.CellCount(); x++)
{
metricNames.Add(metricRow.Cell(x).Value.ToString());
}
break;
}
}
}
}
return metricNames;
}