private void AddBorders(int rowIndex)
{
_worksheet.Range(rowIndex, 1, rowIndex + 2, 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
_worksheet.Range(rowIndex, 2, rowIndex + 2, 2).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
_worksheet.Range(rowIndex, 3, rowIndex + 2, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
_worksheet.Range(rowIndex, 4, rowIndex + 2, _worksheet.ColumnsUsed().Last().ColumnNumber()).Style.Border
.InsideBorder = XLBorderStyleValues.Thin;
_worksheet.Range(rowIndex, 4, rowIndex + 2, _worksheet.ColumnsUsed().Last().ColumnNumber()).Style.Border
.OutsideBorder = XLBorderStyleValues.Thin;
_worksheet.Range(rowIndex, 1, rowIndex + 2, _worksheet.ColumnsUsed().Last().ColumnNumber()).Style.Border
.OutsideBorder = XLBorderStyleValues.Medium;
}
/// <summary>
/// Apply formatting to the worksheet
/// </summary>
/// <param name="worksheet">The worksheet to format</param>
private void FormatWorksheet(ref IXLWorksheet worksheet)
{
worksheet.ColumnsUsed().AdjustToContents();
worksheet.SheetView.FreezeRows(1);
worksheet.Rows().Height = 30;
worksheet.Column(3).Width = 40;
}
internal void CreateWorksheet(IXLWorkbook workbook, string sheetName, IElement tableNode)
{
IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName);
int row = 1;
int col = 1;
foreach (IElement rowNode in tableNode.QuerySelectorAll("tr"))
{
List <IElement> cells = rowNode.QuerySelectorAll("th").ToList();
cells.AddRange(rowNode.QuerySelectorAll("td"));
foreach (IElement cellNode in cells)
{
RenderCell(worksheet, cellNode, row, ref col);
}
col = 1;
row++;
}
if (!hasMergedCells)
{
var table = worksheet.RangeUsed().CreateTable("mainTable" + worksheet.Name);
table.Theme = XLTableTheme.TableStyleLight1;
table.ShowRowStripes = Settings.ShowRowStripes;
table.ShowAutoFilter = Settings.ShowFilter;
}
if (Settings.AutofitColumns)
{
worksheet.ColumnsUsed().AdjustToContents();
}
}
private void makeCustomer(List <Customer> data, IXLWorksheet sheet)
{
sheet.Cell(1, 1).Value = "客戶資料(" + data.Count() + ")";
int row_index = 2;
#region 主標題
sheet.Cell(row_index, 1).Value = "[客戶姓名]";
sheet.Cell(row_index, 2).Value = "[email]";
sheet.Cell(row_index, 3).Value = "[電話]";
sheet.Cell(row_index, 4).Value = "[手機]";
sheet.Cell(row_index, 5).Value = "[郵遞區號]";
sheet.Cell(row_index, 6).Value = "[地址]";
setFontColor_Label(sheet, row_index, row_index, 1, 6, XLColor.Blue);
row_index++;
#endregion
foreach (var i in data)
{
sheet.Cell(row_index, 1).Value = i.c_name;
sheet.Cell(row_index, 2).Value = i.email;
sheet.Cell(row_index, 3).Value = i.tel;
sheet.Cell(row_index, 4).Value = i.mobile;
sheet.Cell(row_index, 5).Value = i.zip;
sheet.Cell(row_index, 6).Value = i.address;
row_index++;
}
sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
}
private static string GetCreateTableQuery(IXLWorksheet workSheet)
{
var createStatement = "CREATE TABLE FEATURES";
foreach (var col in workSheet.ColumnsUsed())
{
var guessedType = GuessType(col);
}
var columnDeclarations = workSheet.ColumnsUsed()
.Select(col =>
Regex.Replace(col.FirstCellUsed().Value.ToString(), "[^0-9a-zA-Z]+", "") +
" " +
GuessType(col).GetSqlDataType())
.Aggregate((current, next) => $"{current}, {next}");
return(string.Format("{0} ({1})", createStatement, columnDeclarations));
}
public string[,] ConvertWorksheetTo2dArray(IXLWorksheet worksheet)
{
// Look for the first row used
var firstRowUsed = worksheet.FirstRowUsed();
// Narrow down the row so that it only includes the used part
var residentinfoHeaders = firstRowUsed.RowUsed();
var residentinfo = residentinfoHeaders;
Console.WriteLine($"Show Resident Information: ", residentinfoHeaders.Cell(1).Value);
var i = 1;
while (!residentinfo.Cell(i).IsEmpty())
{
//Console.WriteLine();
Console.WriteLine(residentinfo.Cell(i).Value + "|");
i++;
}
// for(InitializeSomething; BooleanCheckSomething; MutateSomething(s))
//
//
for (var row = firstRowUsed.RowUsed(); !row.IsEmpty(); row = row.RowBelow())
{
foreach (var cell in row.Cells())
{
Console.Write(cell.Value);
Console.Write(" ");
}
Console.WriteLine();
}
//Index's for The Cells in Table
var activeRowCount = worksheet.RowsUsed().Count();
var activeColumnCount = worksheet.ColumnsUsed().Count();
string[,] my2dArrayWithConvertedCells = new string[, ]
{
{ "", "" },
};
//using foreach to create 2d Array
//convert to 2d Array and return the values
var rangeOfFirstNames = worksheet.Range("A1:A5");
foreach (var cell in rangeOfFirstNames.Cells())
{
cell.SetDataType(XLDataType.Text);
var valueofcell = cell.Value.ToString();
}
return(my2dArrayWithConvertedCells);
}
public static void writeResult(Result result)
{
File.Copy(OutputPath, OutputPath + ".bak.xlsm", true);
XLWorkbook workbook = new XLWorkbook(OutputPath);
IXLWorksheet worksheet = workbook.Worksheet(1);
int resultColumn = worksheet.ColumnsUsed().Count();
foreach (var item in result)
{
++resultColumn;
var cfgResult = item.Value;
int bestObjStartRow = BestObjStartRow;
int averageObjStartRow = AverageObjStartRow;
worksheet.Cell(bestObjStartRow++, resultColumn).Value = item.Key;
worksheet.Cell(averageObjStartRow++, resultColumn).Value = item.Key;
worksheet.Cell(bestObjStartRow++, resultColumn).Value = cfgResult.First().Value.startDate;
worksheet.Cell(averageObjStartRow++, resultColumn).Value = cfgResult.First().Value.startDate;
// EXTEND[szx][5]: add RunTime row (more run time can compensate bad configuration which may confuse the comparison).
int feasibleCount = 0;
int runCount = 0;
for (int i = 0; i < InstanceNum; ++i)
{
string instanceName = worksheet.Cell(BestObjStartRow + HeaderRowNum + i, InstanceNameColumn).GetString();
if (!cfgResult.ContainsKey(instanceName))
{
continue;
}
feasibleCount += cfgResult[instanceName].feasibleCount;
runCount += cfgResult[instanceName].runCount;
}
worksheet.Cell(bestObjStartRow++, resultColumn).Value = feasibleCount;
worksheet.Cell(averageObjStartRow++, resultColumn).Value = feasibleCount;
worksheet.Cell(bestObjStartRow++, resultColumn).Value = runCount;
worksheet.Cell(averageObjStartRow++, resultColumn).Value = runCount;
worksheet.Cell(bestObjStartRow++, resultColumn).SetFormulaR1C1(NormalizedScoreFormula);
worksheet.Cell(averageObjStartRow++, resultColumn).SetFormulaR1C1(NormalizedScoreFormula);
for (int i = 0; i < InstanceNum; ++i)
{
string instanceName = worksheet.Cell(bestObjStartRow + i, InstanceNameColumn).GetString();
if (!cfgResult.ContainsKey(instanceName))
{
continue;
}
worksheet.Cell(bestObjStartRow + i, resultColumn).Value = cfgResult[instanceName].bestObj;
worksheet.Cell(averageObjStartRow + i, resultColumn).Value = cfgResult[instanceName].AverageObj;
}
}
workbook.Save();
}
private void makeShip(List <Api.PurchaseController.ShipData> data, IXLWorksheet sheet)
{
int row_index = 2;
#region 產品名稱
sheet.Cell(row_index, 3).Value = "咖啡豆重量";
List <string> p_name = data.Select(x => x.p_name).ToList();
int col_index = 4;
foreach (var i in p_name)
{
sheet.Cell(row_index, col_index).Value = i;
sheet.Cell(row_index, col_index).Style.Font.Bold = true;
sheet.Cell(row_index, col_index).Style.Font.FontColor = XLColor.FromArgb(153, 51, 0);
col_index++;
}
sheet.Cell(1, 2).Value = "訂單未出貨統計";
sheet.Range(1, 2, 1, col_index).Merge();
setFontColorAndBg(sheet, 1, 2, XLColor.White, XLColor.FromArgb(156, 101, 0));
int col_end = col_index;
row_index++;
#endregion
col_index = 4;
foreach (var i in data)
{
string name = sheet.Cell(2, col_index).GetValue <string>();
if (i.p_name == name)
{
foreach (var j in i.Detail)
{
sheet.Cell(row_index, 1).Value = j.purchase_no; //訂單編號
sheet.Cell(row_index, 2).Value = j.p_d_pack_name; //訂單編號
sheet.Cell(row_index, 3).Value = j.weight; //咖啡豆重量
sheet.Cell(row_index, col_index).Value = j.qty; //數量
row_index++;
}
col_index++;
}
}
#region 方加總
sheet.Cell(row_index, 2).Value = "未出貨統計(g)";
sheet.Range(row_index, 2, row_index, 3).Merge();
setFontColorAndBg(sheet, row_index, 2, XLColor.FromArgb(153, 51, 0), XLColor.FromArgb(255, 235, 156));
for (var i = 4; i < col_end; i++)
{
sheet.Cell(row_index, i).FormulaA1 = string.Format("SUMPRODUCT(C{0}:C{1},{2}{0}:{2}{1})", 3, row_index - 1, Convert.ToChar(64 + i));
sheet.Cell(row_index, i).Style.Font.FontColor = XLColor.FromArgb(156, 101, 0);
sheet.Cell(row_index, i).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 235, 156);
}
sheet.Cell(row_index, col_end).FormulaA1 = string.Format("SUM({0}{1}:{2}{1})", Convert.ToChar(64 + 4), row_index, Convert.ToChar(63 + col_end));
sheet.Cell(row_index, col_end).Style.Font.FontColor = XLColor.FromArgb(156, 101, 0);
sheet.Cell(row_index, col_end).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 235, 156);
#endregion
sheet.Range(1, 2, row_index, col_end).Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
sheet.Range(1, 2, row_index, col_end).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
}
private static DataTable ReadExcelSheet(IXLWorksheet workSheet, ReadOptions options)
{
DataTable dt = new DataTable();
options = options ?? ReadOptions.DefaultOptions;
//primera fila de títulos
bool firstRow = options.TitlesInFirstRow;
dt.TableName = workSheet.GetNameForDataTable();
if (!options.TitlesInFirstRow)
{
//si no tenemos títulos en la tabla utilizamos los nombres de columna del excel para la definición del DataTable
foreach (var col in workSheet.ColumnsUsed())
{
dt.Columns.Add(col.ColumnLetter());
}
}
Func <IXLRow, bool> getRows = _ => true;
if (options.RowStart != 0)
{
getRows = (r) => (r.RowNumber() >= options.RowStart);
}
foreach (IXLRow row in workSheet.RowsUsed(r => getRows(r)))
{
//Usamos la primera fila para crear las columnas con los títulos
//init with options.TitlesInFirstRow
if (firstRow)
{
foreach (IXLCell cell in row.CellsUsed())
{
dt.Columns.Add(cell.Value?.ToString());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
return(dt);
}
private void AdicionarEstilo(IXLWorksheet worksheet, DataTable tabelaDados)
{
int ultimaColunaUsada = worksheet.LastColumnUsed().ColumnNumber();
int ultimaLinhaUsada = worksheet.LastRowUsed().RowNumber();
AdicionarEstiloCabecalho(worksheet, ultimaColunaUsada);
AdicionarEstiloCorpo(worksheet, tabelaDados, ultimaColunaUsada, ultimaLinhaUsada);
worksheet.ShowGridLines = false;
worksheet.ColumnsUsed().AdjustToContents();
worksheet.RowsUsed().AdjustToContents();
}
private DataTable ReadExcelSheet(IXLWorksheet workSheet, ReadOptions options)
{
DataTable dt = new DataTable();
options = options ?? ReadOptions.DefaultOptions;
//TODO: Implementar opción con columnas sin títulos
//primera fila de titulos
bool firstRow = options.TitlesInFirstRow;
dt.TableName = workSheet.GetNameForDataTable();
if (options.TitlesInFirstRow)
{
//si no tenemos títulos en la tabla utilizamos los nombres de columna del excel para la definición del DataTable
foreach (var col in workSheet.ColumnsUsed())
{
dt.Columns.Add(col.ColumnLetter());
}
}
foreach (IXLRow row in workSheet.RowsUsed())
{
//Usamos la primera fila para crear las columnas con los títulos
//init with options.TitlesInFirstRow
if (firstRow)
{
foreach (IXLCell cell in row.CellsUsed())
{
dt.Columns.Add(cell.GetContentWithOutSpaces());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
return(dt);
}
public static byte[] GetContentAsBytes(params DataTable[] tables)
{
using (var workbook = new XLWorkbook())
{
foreach (DataTable dataTable in tables)
{
IXLWorksheet ws = workbook.Worksheets.Add(dataTable);
ws.RangeUsed().ColumnsUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.ColumnsUsed().AdjustToContents();
}
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
return(stream.ToArray());
}
}
}
public void NoColumnsUsed()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
Int32 count = 0;
foreach (IXLColumn row in ws.ColumnsUsed())
{
count++;
}
foreach (IXLRangeColumn row in ws.Range("A1:C3").ColumnsUsed())
{
count++;
}
Assert.AreEqual(0, count);
}
public void SetCompetencies(IXLWorksheet workSheet, int row, Dictionary <string, string> compDic)
{
var resultList = new List <string>();
var competenciesList = workSheet.Cell(row,
workSheet.ColumnsUsed().Count()).Value.ToString().Split(';', ' ').ToList();
//Извлекает компетенции дисциплины
foreach (var item in competenciesList)
{
if (!string.IsNullOrEmpty(item))
{
if (compDic.ContainsKey(item))
{
resultList.Add($"{item}" + " -" + compDic[item]);//Находит такие же в словаре
}
}
}
this.Competencies = "\t" + string.Join(";\n\t", resultList) + ".";//Переводит в строку
}
public void DateBottomBorder(IXLWorksheet worksheet, DataTable dateexcel, string BeforeCell, string NextCell)
{
string saveCellNumber = "";
string saveCellNumber2 = "";
string BeforeBottomCell = "";
string NextBottomCell = "";
int range = 29;
string Nowtime = DateTime.Now.ToString("yyyy-MM-dd");
// 사용중인 컬럼 담기
var value = worksheet.ColumnsUsed();
string[] splitColumns = new string[2];
List <string> Columns = new List <string>();
List <string> ValueTest = new List <string>();
// 값 확인용
for (int i = 0; i < Columns.Count; i++)
{
string a = Columns[i];
}
foreach (var row in value)
{
string test = row.RangeAddress.ToString();
splitColumns = test.Split(':');
Columns.Add(splitColumns[0]);
}
for (int b = dateexcel.Rows.Count; b > 0; b--)
{
string Value = worksheet.Cell(BeforeCell + (b + 28)).Value.ToString();
string Value2 = worksheet.Cell(NextCell + (b + 28)).Value.ToString();
/* BeforeBottomCell = worksheet.Cell(BeforeCell + (b + 28)).ToString(); // cell 이름
* NextBottomCell = worksheet.Cell(NextCell + (b + 28)).ToString(); // cell 이름*/
DateTime Celltime; // 참조될 Cell Type
DateTime Celltime2; // 참조될 Cell Type
string BeforeBottomCellValue = worksheet.Cell(BeforeCell + (b + 28)).Style.Border.BottomBorder.ToString(); // 하단 셀 찾기
string NextBottomCellValue = worksheet.Cell(NextCell + (b + 28)).Style.Border.BottomBorder.ToString(); // 하단 셀 찾기
if (NextBottomCellValue.Equals("Medium"))
{ // 다음셀에서 사용할 미디움 발견 즉시 바로 번호 저장
saveCellNumber2 = (b + 28).ToString();
}
if (BeforeBottomCellValue.Equals("Medium"))
{ // 이전셀에서 미디움 발견 즉시 바로 번호 저장
saveCellNumber = (b + 28).ToString();
}
// 사용중인 컬럼 값 조회 & 날짜 확인
for (int i = Columns.Count; i > 0; i--)
{
string columnValue = Columns[i - 1];
string compare = worksheet.Cell(columnValue + (b + range)).Value.ToString();
if (DateTime.TryParse(compare, out Celltime)) // 날짜 타입이면
{
int celldate = DateTime.Compare(DateTime.Parse(Nowtime), Celltime); // 현재보다 미래면 -1
// int celldateCompare = DateTime.Compare(DateTime.Parse(Nowtime), Celltime2); // 현재보다 미래면 -1
// if (celldate > 0) // 과거인 날짜 찾기
if (celldate < 0) // 미래인 날짜 찾기
{
// worksheet.Cell("H" + (b + 29)).Style.Border.RightBorder = XLBorderStyleValues.Medium;
// worksheet.Range("H41:I34").Style.Border.RightBorder = XLBorderStyleValues.Medium;
ValueTest.Add(columnValue + (b + range)); // 과거인 날짜 컬럼 담기
worksheet.Cell(columnValue + (b + range)).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0); // 노란색
// 미래 데이터 노란색
}
}
}
}
ValueTest.Sort();
// int MinusnSaveCellnumber = Int32.Parse(saveCellNumber2) + 1;
int MinusnSaveCellnumber = Int32.Parse(saveCellNumber2);
worksheet.Range(NextCell + MinusnSaveCellnumber.ToString() + ":" + (NextCell + saveCellNumber)).Style.Border.LeftBorder = XLBorderStyleValues.Medium;
// 반복문을 거꾸로 했기때문에 역순임
}
/// <summary>
/// Apply formatting to the worksheet
/// </summary>
/// <param name="worksheet">The worksheet to format</param>
private void FormatWorksheet(ref IXLWorksheet worksheet)
{
worksheet.ColumnsUsed().AdjustToContents();
worksheet.SheetView.FreezeRows(1);
}
private void makePurchase(List <m_Purchase> data, IXLWorksheet sheet)
{
sheet.Cell(1, 1).Value = "銷售明細總表";
sheet.Range(1, 1, 1, 14).Merge();
setFontColorAndBg(sheet, 1, 1, XLColor.White, XLColor.Blue);
int row_index = 2;
foreach (var i in data)
{
#region 主標題
sheet.Cell(row_index, 1).Value = "[訂單編號]";
sheet.Cell(row_index, 2).Value = "[購買人]";
sheet.Cell(row_index, 3).Value = "[付款方式]";
sheet.Cell(row_index, 4).Value = "[付款狀態]";
sheet.Cell(row_index, 5).Value = "[出貨狀態]";
sheet.Cell(row_index, 6).Value = "[下單日期]";
sheet.Cell(row_index, 7).Value = "[運費]";
sheet.Cell(row_index, 8).Value = "[手續費]";
sheet.Cell(row_index, 9).Value = "[總計金額(含運費)]";
sheet.Cell(row_index, 10).Value = "[收件人]";
sheet.Cell(row_index, 11).Value = "[收件人電話]";
sheet.Cell(row_index, 12).Value = "[收件人手機]";
sheet.Cell(row_index, 13).Value = "[收件人地址]";
sheet.Cell(row_index, 14).Value = "[收件備註]";
setFontColor_Label(sheet, row_index, row_index, 1, 14, XLColor.Blue);
row_index++;
#endregion
sheet.Cell(row_index, 1).Value = i.purchase_no;
sheet.Cell(row_index, 2).Value = i.customer_name;
sheet.Cell(row_index, 3).Value = CodeSheet.GetStateVal(i.pay_type, i_CodeName.Value, CodeSheet.IPayTypeData);
sheet.Cell(row_index, 4).Value = CodeSheet.GetStateVal(i.pay_state, i_CodeName.Value, CodeSheet.IPayStateData);
sheet.Cell(row_index, 5).Value = CodeSheet.GetStateVal(i.ship_state, i_CodeName.Value, CodeSheet.IShipStateData);
sheet.Cell(row_index, 6).Value = i.order_date.ToString("yyyy/MM/dd HH:mm");
sheet.Cell(row_index, 7).Value = i.ship_fee;
sheet.Cell(row_index, 8).Value = i.bank_charges;
sheet.Cell(row_index, 9).Value = i.total;
sheet.Cell(row_index, 10).Value = i.receive_name;
sheet.Cell(row_index, 11).Value = i.receive_tel;
sheet.Cell(row_index, 12).Value = i.receive_mobile;
sheet.Cell(row_index, 13).Value = i.receive_zip + "-" + i.receive_address;
sheet.Cell(row_index, 14).Value = i.receive_memo;
row_index++;
#region 次標題
sheet.Cell(row_index, 3).Value = "產品購買清單";
sheet.Range(row_index, 3, row_index, 9).Merge();
setFontColorAndBg(sheet, row_index, 3, XLColor.White, XLColor.DeepSkyBlue);
row_index++;
sheet.Cell(row_index, 3).Value = "[項次]";
sheet.Cell(row_index, 4).Value = "[產品料號]";
sheet.Cell(row_index, 5).Value = "[產品名稱]";
sheet.Cell(row_index, 6).Value = "[產品包裝]";
sheet.Cell(row_index, 7).Value = "[單價]";
sheet.Cell(row_index, 8).Value = "[數量]";
sheet.Cell(row_index, 9).Value = "[小計]";
setFontColor_Label(sheet, row_index, row_index, 3, 9, XLColor.Blue);
row_index++;
#endregion
int index = 1;
foreach (var detail in i.Deatil)
{
sheet.Cell(row_index, 3).Value = index;
sheet.Cell(row_index, 4).Value = detail.p_d_sn;
sheet.Cell(row_index, 5).Value = detail.p_name;
sheet.Cell(row_index, 6).Value = detail.p_d_pack_name;
sheet.Cell(row_index, 7).Value = detail.price;
sheet.Cell(row_index, 8).Value = detail.qty;
sheet.Cell(row_index, 9).Value = detail.sub_total;
row_index++;
}
row_index++;
}
sheet.ColumnsUsed().AdjustToContents();//自動調整寬度
}
/// <summary>
/// Apply formatting to the worksheet
/// </summary>
/// <param name="worksheet"></param>
private void FormatWorksheet(ref IXLWorksheet worksheet)
{
worksheet.ColumnsUsed().AdjustToContents();
worksheet.Column(2).Width = 30;
worksheet.Column(2).Style.Alignment.WrapText = true;
}
XLWorkbook CreateXlWorkBook(DataSet ImportDs, XLWorkbook wb)
{
try
{
int Index = 0;
foreach (DataTable dt in ImportDs.Tables)
{
if (dt.Rows.Count <= 0)
{
dt.Columns.Add("No records", typeof(string));
dt.Rows.Add("No records");
}
if (Index % 2 != 0 || Index == 0)
{
//Add DataTable as Worksheet.
IXLWorksheet worksheet = wb.Worksheets.Add(dt);
worksheet.Tables.FirstOrDefault().ShowAutoFilter = false;
///// Add Header ///
worksheet.Row(1).InsertRowsAbove(1);
worksheet.Row(2).InsertRowsAbove(1);
worksheet.Row(3).InsertRowsAbove(1);
int UsedCol = worksheet.ColumnsUsed().Count();
// worksheet.Row(1).Cell(UsedCol).Hyperlink.Cell.Value = "Help";
// worksheet.Row(1).Cell(UsedCol).Hyperlink.InternalAddress = "www.gstsaathiaccounts.in";
// worksheet.Row(1).Cell(UsedCol).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// Put a Blue Color in a 2 Rows Of Cells
foreach (var c in Enumerable.Range(1, UsedCol))
{
//worksheet.Cell(2, c).Style.Border.
worksheet.Cell(2, c).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
worksheet.Cell(2, c).Style.Font.FontColor = XLColor.White;
// worksheet.Cell(2, c).Style.Border.OutsideBorderColor = XLColor.Black;
}
// Put a Orange Color in a 4 Rows Of Cells Of Headers of Data
foreach (var c in Enumerable.Range(1, UsedCol))
{
worksheet.Cell(4, c).Style.Fill.BackgroundColor = XLColor.FromArgb(250, 191, 143);
worksheet.Cell(4, c).Style.Font.FontColor = XLColor.Black;
// worksheet.Cell(4, c).Style.Border.OutsideBorderColor = XLColor.Black;
}
// Excel Name Row 1 Cell 1;
//worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
//worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.White;
worksheet.Cells("A1").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
worksheet.Cells("A1").Style.Font.FontColor = XLColor.White;
#region b2b
if (worksheet.Name == "b2b")
{
worksheet.Row(1).Cell(1).Value = "Summary For B2B(4)";
worksheet.Cell("A2").Value = "No. of Recipients";
worksheet.Cell("B2").Value = "No. of Invoices";
worksheet.Cell("D2").Value = "Total Invoice Value";
worksheet.Cell("J2").Value = "Total Taxable Value";
worksheet.Cell("K2").Value = "Total Cess";
//if (dt.Rows.Count > 0)
//{
//DataView view = new DataView(dt);
worksheet.Cell("A3").Value = ImportDs.Tables[2].Rows[0]["TotalParty"].ToString();
worksheet.Cell("B3").Value = ImportDs.Tables[2].Rows[0]["TotalInvoice"].ToString();
worksheet.Cell("D3").Value = ImportDs.Tables[2].Rows[0]["invoiceValue"].ToString();
worksheet.Cell("J3").Value = ImportDs.Tables[2].Rows[0]["Taxableval"].ToString();
worksheet.Cell("K3").Value = ImportDs.Tables[2].Rows[0]["Cess"].ToString();
//view.ToTable(true, "GSTIN/UIN of Recipient").Rows.Count;
//view.ToTable(true, "Invoice Number").Rows.Count;
//dt.Compute("SUM([Invoice Value])", string.Empty);
//dt.Compute("SUM([Taxable Value])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
//}
}
#endregion
#region b2cl
if (worksheet.Name == "b2cl")
{
worksheet.Row(1).Cell(1).Value = "Summary For B2CL(5)";
worksheet.Cell("A2").Value = "No. of Invoices";
worksheet.Cell("C2").Value = "Total Invoice Value";
worksheet.Cell("F2").Value = "Total Taxable Value";
worksheet.Cell("G2").Value = "Total Cess";
//DataView view = new DataView(dt);
worksheet.Cell("A3").Value = ImportDs.Tables[4].Rows[0]["TotalInvoice"].ToString();
worksheet.Cell("C3").Value = ImportDs.Tables[4].Rows[0]["invoiceValue"].ToString();
worksheet.Cell("F3").Value = ImportDs.Tables[4].Rows[0]["Taxableval"].ToString();
worksheet.Cell("G3").Value = ImportDs.Tables[4].Rows[0]["Cess"].ToString();
//view.ToTable(true, "Invoice Number").Rows.Count;
//dt.Compute("SUM([Invoice Value])", string.Empty);
//dt.Compute("SUM([Taxable Value])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
}
#endregion
#region b2cs
if (worksheet.Name == "b2cs")
{
worksheet.Row(1).Cell(1).Value = "Summary For B2CS(7)";
worksheet.Cell("D2").Value = "Total Taxable Value";
worksheet.Cell("E2").Value = "Total Cess";
//DataView view = new DataView(dt);
worksheet.Cell("D3").Value = ImportDs.Tables[6].Rows[0]["Taxableval"].ToString();
worksheet.Cell("E3").Value = ImportDs.Tables[6].Rows[0]["Cess"].ToString();
//dt.Compute("SUM([Taxable Value])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
}
#endregion
#region cdnr
if (worksheet.Name == "cdnr")
{
worksheet.Row(1).Cell(1).Value = "Summary For CDNR(9B)";
worksheet.Cell("A2").Value = "No. of Recipients";
worksheet.Cell("B2").Value = "No. of Invoices";
worksheet.Cell("D2").Value = "No. of Notes/Vouchers";
worksheet.Cell("I2").Value = "Total Note/Refund Voucher Value";
worksheet.Cell("K2").Value = "Total Taxable Value";
worksheet.Cell("L2").Value = "Total Cess";
//DataView view = new DataView(dt);
worksheet.Cell("A3").Value = ImportDs.Tables[8].Rows[0]["TotalReciepent"].ToString();
worksheet.Cell("B3").Value = ImportDs.Tables[8].Rows[0]["AmdTotalInvoice"].ToString();
worksheet.Cell("D3").Value = ImportDs.Tables[8].Rows[0]["TotalInvoice"].ToString();
worksheet.Cell("I3").Value = ImportDs.Tables[8].Rows[0]["invoiceValue"].ToString();
worksheet.Cell("K3").Value = ImportDs.Tables[8].Rows[0]["Taxableval"].ToString();
worksheet.Cell("L3").Value = ImportDs.Tables[8].Rows[0]["Cess"].ToString();
//view.ToTable(true, "GSTIN/UIN of Recipient").Rows.Count;
//view.ToTable(true, "Invoice/Advance Receipt Number").Rows.Count;
//view.ToTable(true, "Note/Refund Voucher Number").Rows.Count;
//dt.Compute("SUM([Note/Refund Voucher Value])", string.Empty);
//dt.Compute("SUM([Taxable Value])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
}
#endregion
#region cdnur
if (worksheet.Name == "cdnur")
{
worksheet.Row(1).Cell(1).Value = "Summary For CDNUR(9B)";
worksheet.Cell("B2").Value = "No. of Notes/Vouchers";
worksheet.Cell("E2").Value = "No. of Invoices";
worksheet.Cell("I2").Value = "Total Note Value";
worksheet.Cell("K2").Value = "Total Taxable Value";
worksheet.Cell("L2").Value = "Total Cess";
//DataView view = new DataView(dt);
worksheet.Cell("B3").Value = ImportDs.Tables[10].Rows[0]["AmdTotalInvoice"].ToString();
worksheet.Cell("E3").Value = ImportDs.Tables[10].Rows[0]["TotalInvoice"].ToString();
worksheet.Cell("I3").Value = ImportDs.Tables[10].Rows[0]["invoiceValue"].ToString();
worksheet.Cell("K3").Value = ImportDs.Tables[10].Rows[0]["Taxableval"].ToString();
worksheet.Cell("L3").Value = ImportDs.Tables[10].Rows[0]["Cess"].ToString();
// view.ToTable(true, "Note/Refund Voucher Number").Rows.Count;
//view.ToTable(true, "Invoice/Advance Receipt Number").Rows.Count;
//dt.Compute("SUM([Note/Refund Voucher Value])", string.Empty);
//dt.Compute("SUM([Taxable Value])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
}
#endregion
#region exp
if (worksheet.Name == "exp")
{
worksheet.Row(1).Cell(1).Value = "Summary For EXP(6)";
worksheet.Cell("B2").Value = "No. of Invoices";
worksheet.Cell("D2").Value = "Total Note Value";
worksheet.Cell("F2").Value = "No. of Shipping Bill";
worksheet.Cell("I2").Value = "Total Taxable Value";
//DataView view = new DataView(dt);
worksheet.Cell("B3").Value = ImportDs.Tables[12].Rows[0]["TotalInvoice"].ToString();
worksheet.Cell("D3").Value = ImportDs.Tables[12].Rows[0]["InvoiceValue"].ToString();
worksheet.Cell("F3").Value = ImportDs.Tables[12].Rows[0]["ShippedInvoice"].ToString();
worksheet.Cell("I3").Value = ImportDs.Tables[12].Rows[0]["Taxablevalue"].ToString();
//view.ToTable(true, "Invoice Number").Rows.Count;
//dt.Compute("SUM([Invoice Value])", string.Empty);
//view.ToTable(true, "Shipping Bill Number").Rows.Count;
//dt.Compute("SUM([Taxable Value])", string.Empty);
}
#endregion
#region at
if (worksheet.Name == "at")
{
worksheet.Row(1).Cell(1).Value = "Summary For Advance Adjusted (11B)";
worksheet.Cell("C2").Value = "Total Advance Received";
worksheet.Cell("D2").Value = "Total Cess";
worksheet.Cell("C3").Value = ImportDs.Tables[14].Rows[0]["GrossReceived"].ToString();
worksheet.Cell("D3").Value = ImportDs.Tables[14].Rows[0]["CessAmt"].ToString();
//dt.Compute("SUM([Gross Advance Received])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
}
#endregion
#region atadj
if (worksheet.Name == "atadj")
{
worksheet.Row(1).Cell(1).Value = "Summary For Advance Adjusted (11B)";
worksheet.Cell("C2").Value = "Total Advance Adjusted";
worksheet.Cell("D2").Value = "Total Cess";
worksheet.Cell("C3").Value = ImportDs.Tables[16].Rows[0]["GrossReceived"].ToString();
worksheet.Cell("D3").Value = ImportDs.Tables[16].Rows[0]["CessAmt"].ToString();
}
#endregion
#region exemp
if (worksheet.Name == "exemp")
{
worksheet.Row(1).Cell(1).Value = "Summary For Nil rated, exempted and non GST outward supplies (8)";
worksheet.Cell("B2").Value = "Total Nil Rated Supplies";
worksheet.Cell("C2").Value = "Total Exempted Supplies";
worksheet.Cell("D2").Value = "Total Non-GST Supplies";
//DataView view = new DataView(dt);
worksheet.Cell("B3").Value = ImportDs.Tables[18].Rows[0]["NillRated"].ToString();
worksheet.Cell("C3").Value = ImportDs.Tables[18].Rows[0]["ExmTotal"].ToString();
worksheet.Cell("D3").Value = ImportDs.Tables[18].Rows[0]["NonGstTotal"].ToString();
//dt.Compute("SUM([Nil Rated Supplies])", string.Empty);
//dt.Compute("SUM([Exempted (other than nil rated/non GST supply )])", string.Empty);
//dt.Compute("SUM([Non-GST supplies])", string.Empty);
}
#endregion
#region hsn
if (worksheet.Name == "hsn")
{
worksheet.Row(1).Cell(1).Value = "Summary For HSN(12)";
worksheet.Cell("A2").Value = "No. of HSN";
worksheet.Cell("E2").Value = "Total Value";
worksheet.Cell("F2").Value = "Total Taxable Value";
worksheet.Cell("G2").Value = "Total Integrated Tax";
worksheet.Cell("H2").Value = "Total Central Tax";
worksheet.Cell("I2").Value = "Total State/UT Tax";
worksheet.Cell("J2").Value = "Total Cess";
//if (dt.Rows.Count > 0)
//{
// DataView view = new DataView(dt);
worksheet.Cell("A3").Value = ImportDs.Tables[20].Rows[0]["HSN"].ToString();
worksheet.Cell("E3").Value = ImportDs.Tables[20].Rows[0]["TotalValue"].ToString();
worksheet.Cell("F3").Value = ImportDs.Tables[20].Rows[0]["TotalTaxValue"].ToString();
worksheet.Cell("G3").Value = ImportDs.Tables[20].Rows[0]["IGSTAmt"].ToString();
worksheet.Cell("H3").Value = ImportDs.Tables[20].Rows[0]["CGSTAmt"].ToString();
worksheet.Cell("I3").Value = ImportDs.Tables[20].Rows[0]["SGSTAmt"].ToString();
worksheet.Cell("J3").Value = ImportDs.Tables[20].Rows[0]["Cess"].ToString();
//view.ToTable(true, "HSN").Rows.Count;
//dt.Compute("SUM([Total Value])", string.Empty);
//dt.Compute("SUM([Taxable Value])", string.Empty);
//dt.Compute("SUM([Integrated Tax Amount])", string.Empty);
//dt.Compute("SUM([Central Tax Amount])", string.Empty);
//dt.Compute("SUM([State/UT Tax Amount])", string.Empty);
//dt.Compute("SUM([Cess Amount])", string.Empty);
worksheet.Column("D").CellsUsed().Style.NumberFormat.Format = "0.00";
worksheet.Row(3).Cell("E").Style.NumberFormat.Format = "0.00";
worksheet.Row(3).Cell("F").Style.NumberFormat.Format = "0.00";
//var validE3 = worksheet.Row(3).Cell("E").DataValidation;
//validE3.Decimal.GetType();
//validE3.ErrorStyle = XLErrorStyle.Warning;
//validE3.ErrorTitle = "Number out of range";
//validE3.ErrorMessage = "This cell only allows the number 2.";
//worksheet.Cell("E3").Style.NumberFormat.Format = "0.00"; //"#,##0.00";
//worksheet.Cell("F3").Style.NumberFormat.Format = "0.00"; //"#,##0.00";
//worksheet.Range("D4", "D" + worksheet.RowsUsed().Count()).Style.NumberFormat.Format = "0.00";
//worksheet.CellsUsed("A").Style.NumberFormat.Format = "0.00";
//worksheet.Columns(4,6).CellsUsed().Style.NumberFormat.Format = "0.00";
// }
}
#endregion
#region docs
if (worksheet.Name == "docs")
{
worksheet.Row(1).Cell(1).Value = "Summary of documents issued during the tax period (13)";
worksheet.Cell("D2").Value = "Total Number";
worksheet.Cell("E2").Value = "Total Cancelled";
// DataView view = new DataView(dt);
worksheet.Cell("D3").Value = ImportDs.Tables[22].Rows[0]["TotalNumber"].ToString();
worksheet.Cell("E3").Value = ImportDs.Tables[22].Rows[0]["Cancelled"].ToString();
//dt.Compute("SUM([Total Number])", string.Empty);
//dt.Compute("SUM([Cancelled])", string.Empty);
}
#endregion
}
Index++;
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
throw ex;
}
return(wb);
}
public ExportarKardexResponseDTO ExportarKardex(KardexPergaminoIngresoConsultaRequest request)
{
ExportarKardexResponseDTO respose = new ExportarKardexResponseDTO();
try
{
using (var workbook = new XLWorkbook())
{
IXLWorksheet ws = workbook.Worksheets.Add("Kardex");
ws.Style.Fill.BackgroundColor = XLColor.White;
ws.Range("A1:BA90").Style.Fill.BackgroundColor = XLColor.White;
ws.Column("A").Width = 1.71;
ws.Column("B").Width = 11.29;
//ws.Cell(2, 2).Value = "Kardex:";
//ws.Cell(2, 2).Style.Font.Bold = true;
//ws.Cell(3, 2).Value = "Producto:";
//ws.Cell(3, 2).Style.Font.Bold = true;
//ws.Cell(4, 2).Value = "Sub Producto:";
//ws.Cell(4, 2).Style.Font.Bold = true;
ws.Cell(3, 2).Value = "Fecha Inicio:";
ws.Cell(3, 2).Style.Font.Bold = true;
ws.Cell(4, 2).Value = "Fecha Fin:";
ws.Cell(4, 2).Style.Font.Bold = true;
ws.Cell(1, 7).Value = "KARDEX PERGAMINO";
ws.Cell(1, 7).Style.Font.Bold = true;
//PRIMERA TABLA
/*
* ws.Cell(2, 8).Value = "Sacos";
* ws.Cell(2, 8).Style.Font.Bold = true;
* ws.Cell(2, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(2, 8).Style.Alignment.WrapText = true;
* ws.Cell(2, 9).Value = "Kgs Netos a pagar";
* ws.Cell(2, 9).Style.Font.Bold = true;
* ws.Cell(2, 9).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(2, 10).Value = "Moneda";
* ws.Cell(2, 10).Style.Font.Bold = true;
* ws.Cell(2, 10).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(2, 11).Value = "Importe";
* ws.Cell(2, 11).Style.Font.Bold = true;
* ws.Cell(2, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(3, 7).Value = "Inventario Actual:";
* ws.Cell(3, 7).Style.Font.Bold = true;
* ws.Cell(3, 7).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(3, 7).Style.Fill.BackgroundColor = XLColor.LightGray;
* ws.Range("H2:K2").Style.Fill.BackgroundColor = XLColor.LightGray;
* ws.Range("H2:K2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
* ws.Range("H2:K2").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
*/
ws.Cell(8, 2).Value = "INGRESOS";
ws.Cell(8, 2).Style.Font.Bold = true;
ws.Cell(8, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(8, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
IXLRange rangeIngresos = ws.Range(ws.Cell(8, 2), ws.Cell(8, 19));
rangeIngresos.Merge();
rangeIngresos.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rangeIngresos.Style.Fill.BackgroundColor = XLColor.FromHtml("#479d9c");//XLColor.YellowProcess;
ws.Cell(8, 20).Value = "SALIDAS";
ws.Cell(8, 20).Style.Font.Bold = true;
ws.Cell(8, 20).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(8, 20).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
IXLRange rangeSalidas = ws.Range(ws.Cell(8, 20), ws.Cell(8, 31));
rangeSalidas.Merge();
rangeSalidas.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rangeSalidas.Style.Fill.BackgroundColor = XLColor.FromHtml("#479d9c");
//INGRESOS
ws.Cell(9, 2).Value = "Fecha Registro";
ws.Cell(9, 3).Value = "Nro. Guia de Recepcion";
ws.Cell(9, 4).Value = "Observaciones";
ws.Cell(9, 5).Value = "Nombre/Razon Social";
ws.Cell(9, 6).Value = "Nro. Documento";
ws.Cell(9, 7).Value = "Zona";
ws.Cell(9, 8).Value = "Producto";
ws.Cell(9, 9).Value = "Cantidad";
ws.Cell(9, 10).Value = "Kilos Brutos";
ws.Cell(9, 11).Value = "Tara";
ws.Cell(9, 12).Value = "Kilos Netos a Descontar";
ws.Cell(9, 13).Value = "Kilos Netos a Pagar";
ws.Cell(9, 14).Value = "Precio del Día";
ws.Cell(9, 15).Value = "Importe";
ws.Cell(9, 16).Value = "% Humedad";
ws.Cell(9, 17).Value = "% Rendimiento (Exportable)";
ws.Cell(9, 18).Value = "Calculo (kilos Netos a Pagar x % Humedad)";
ws.Cell(9, 19).Value = "Calculo (kilos Netos a Pagar x % Rendimiento (Exportable))";
//SALIDAS
ws.Cell(9, 20).Value = "Nro. Guia de Remisión Electronica";
ws.Cell(9, 21).Value = "Fecha Guia de Remisión Electronica";
ws.Cell(9, 22).Value = "Calculo (Kilos Netos * % Humedad)";
ws.Cell(9, 23).Value = "Calculo (Kilos Netos * % Rendimiento)";
ws.Cell(9, 24).Value = "Cantidad";
ws.Cell(9, 25).Value = "Kilos Brutos";
ws.Cell(9, 26).Value = "Tara";
ws.Cell(9, 27).Value = "Kilos Netos a Descontar";
ws.Cell(9, 28).Value = "Kilos Netos";
ws.Cell(9, 29).Value = "% Humedad";
ws.Cell(9, 30).Value = "% Rendimiento";
ws.Cell(9, 31).Value = "Saldo";
for (int i = 2; i <= 19; i++)
{
ws.Cell(9, i).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#479d9c"));
ws.Cell(9, i).Style.Font.Bold = true;
ws.Cell(9, i).Style.Alignment.WrapText = true;
ws.Cell(9, i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(9, i).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(9, i).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
for (int i = 20; i <= 31; i++)
{
ws.Cell(9, i).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#479d9c"));
ws.Cell(9, i).Style.Font.Bold = true;
ws.Cell(9, i).Style.Alignment.WrapText = true;
ws.Cell(9, i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(9, i).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(9, i).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
//INICIO: DATA HARDCODE
//ws.Cell(2, 3).Value = "Almacén 01";
// ws.Cell(3, 3).Value = "Café Pergamino";
// ws.Cell(4, 3).Value = "Seco";
ws.Cell(3, 3).Value = request.FechaInicio.ToShortDateString();
ws.Cell(4, 3).Value = request.FechaFin.ToShortDateString();
ws.Range("D2:D6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range("D2:D6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
/*
* ws.Cell(3, 8).Value = 24;
* ws.Cell(3, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(3, 9).Value = 1286.20;
* ws.Cell(3, 9).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(3, 10).Value = "Soles";
* ws.Cell(3, 10).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Cell(3, 11).Value = 9727.03;
* ws.Cell(3, 11).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
*
*
* ws.Cell("G10").Value = "SALDO ANTERIOR";
* ws.Cell("G10").Style.Font.Bold = true;
* ws.Cell("G10").Style.Font.FontColor = XLColor.Red;
*
* ws.Cell("R10").Value = "Saco";
* ws.Cell("S10").Value = 20;
* ws.Cell("V10").Value = 1096;
* ws.Cell("X10").Value = 48.5;
* ws.Cell("Y10").Value = 1047.5;
* ws.Cell("Z10").Value = "Soles";
* ws.Cell("AB10").Value = 7891.20;
*
* ws.Range("B10:AH10").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Range("AI10:AZ10").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
*/
/*
* KardexPergaminoIngresoConsultaRequest requestIngreso = new KardexPergaminoIngresoConsultaRequest();
* requestIngreso.EmpresaId = 1;
* requestIngreso.FechaInicio = Convert.ToDateTime("2018-07-19T00:00:00");
* requestIngreso.FechaFin = Convert.ToDateTime("2024-07-19T00:00:00");
*/
var list = _IKardexProcesoRepository.KardexPergaminoIngresoConsulta(request);
List <KardexPergaminoIngresoConsultaResponse> listaIngreso = list.ToList();
decimal sumaKilosBrutos = 0;
decimal sumaKilosNetos = 0;
decimal sumaImporte = 0;
decimal sumaRemdimiento = 0;
for (int i = 1; i <= listaIngreso.Count; i++)
{
ws.Cell(9 + i, 2).Value = listaIngreso[i - 1].FechaRegistro.ToShortDateString();
ws.Cell(9 + i, 3).Value = listaIngreso[i - 1].Numero;
ws.Cell(9 + i, 4).Value = listaIngreso[i - 1].ObservacionPesado;
ws.Cell(9 + i, 5).Value = listaIngreso[i - 1].NombreRazonSocial;
ws.Cell(9 + i, 6).Value = listaIngreso[i - 1].NumeroDocumento;
ws.Cell(9 + i, 7).Value = listaIngreso[i - 1].Zona;
ws.Cell(9 + i, 8).Value = listaIngreso[i - 1].Producto;
ws.Cell(9 + i, 9).Value = listaIngreso[i - 1].CantidadPesado;
ws.Cell(9 + i, 10).Value = listaIngreso[i - 1].KilosBrutosPesado;
ws.Cell(9 + i, 11).Value = listaIngreso[i - 1].TaraPesado;
ws.Cell(9 + i, 12).Value = listaIngreso[i - 1].KilosNetosDescontar;
ws.Cell(9 + i, 13).Value = listaIngreso[i - 1].KilosNetosPagar;
ws.Cell(9 + i, 14).Value = listaIngreso[i - 1].PrecioPagado;
ws.Cell(9 + i, 15).Value = listaIngreso[i - 1].Importe;
ws.Cell(9 + i, 16).Value = listaIngreso[i - 1].HumedadPorcentajeAnalisisFisico;
ws.Cell(9 + i, 17).Value = listaIngreso[i - 1].ExportablePorcentajeAnalisisFisico;
ws.Cell(9 + i, 18).Value = listaIngreso[i - 1].CalculoKilosNetosPagarPorHumedad;
ws.Cell(9 + i, 19).Value = listaIngreso[i - 1].CalculoKilosNetosPagarPorRendimiento;
sumaKilosBrutos = sumaKilosBrutos + listaIngreso[i - 1].KilosBrutosPesado;
sumaKilosNetos = sumaKilosNetos + listaIngreso[i - 1].KilosNetosPagar;
sumaImporte = sumaImporte + listaIngreso[i - 1].Importe;
sumaRemdimiento = sumaRemdimiento + listaIngreso[i - 1].ExportablePorcentajeAnalisisFisico;
}
ws.Cell(9 + listaIngreso.Count + 1, 10).Value = sumaKilosBrutos;
ws.Cell(9 + listaIngreso.Count + 1, 10).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 13).Value = sumaKilosNetos;
ws.Cell(9 + listaIngreso.Count + 1, 13).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 15).Value = sumaImporte;
ws.Cell(9 + listaIngreso.Count + 1, 15).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 17).Value = listaIngreso.Count > 0 ? sumaRemdimiento / listaIngreso.Count : 0;
ws.Cell(9 + listaIngreso.Count + 1, 17).Style.Font.Bold = true;
KardexPergaminoSalidaConsultaRequest requestSalida = new KardexPergaminoSalidaConsultaRequest();
requestSalida.EmpresaId = request.EmpresaId;
requestSalida.FechaInicio = request.FechaInicio;
requestSalida.FechaFin = request.FechaFin;
var listSalida = _IKardexProcesoRepository.KardexPergaminoSalidadConsulta(requestSalida);
List <KardexPergaminoSalidaConsultaResponse> listaSalida = listSalida.ToList();
decimal sumaKilosBrutosSalida = 0;
decimal sumaTaraSalida = 0;
decimal sumaKilosNetosSalida = 0;
decimal sumaKilosNetosDescontar = 0;
for (int i = 1; i <= listaSalida.Count; i++)
{
ws.Cell(9 + i, 20).Value = listaSalida[i - 1].NumeroGuiaRemision;
ws.Cell(9 + i, 21).Value = listaSalida[i - 1].FechaGuiaRemision.ToShortDateString();
ws.Cell(9 + i, 22).Value = listaSalida[i - 1].CalculoKilosNetosPagarPorHumedad;
ws.Cell(9 + i, 23).Value = listaSalida[i - 1].CalculoKilosNetosPagarPorRendimiento;
ws.Cell(9 + i, 24).Value = listaSalida[i - 1].Cantidad;
ws.Cell(9 + i, 25).Value = listaSalida[i - 1].TotalKilosBrutosPesado;
ws.Cell(9 + i, 26).Value = listaSalida[i - 1].Tara;
ws.Cell(9 + i, 27).Value = listaSalida[i - 1].TotalKilosNetosDescontar;
ws.Cell(9 + i, 28).Value = listaSalida[i - 1].TotalKilosNetosPesado;
ws.Cell(9 + i, 29).Value = listaSalida[i - 1].HumedadPorcentajeAnalisisFisico;
ws.Cell(9 + i, 30).Value = listaSalida[i - 1].RendimientoPorcentaje;
sumaKilosBrutosSalida = sumaKilosBrutosSalida + listaSalida[i - 1].TotalKilosBrutosPesado;
sumaTaraSalida = sumaTaraSalida + listaSalida[i - 1].Tara;
sumaKilosNetosSalida = sumaKilosNetosSalida + listaSalida[i - 1].TotalKilosNetosPesado;
sumaKilosNetosDescontar = sumaKilosNetosDescontar + listaSalida[i - 1].TotalKilosNetosDescontar;
}
ws.Cell(9 + listaIngreso.Count + 1, 25).Value = sumaKilosBrutosSalida;
ws.Cell(9 + listaIngreso.Count + 1, 25).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 26).Value = sumaTaraSalida;
ws.Cell(9 + listaIngreso.Count + 1, 26).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 27).Value = sumaKilosNetosDescontar;
ws.Cell(9 + listaIngreso.Count + 1, 27).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 28).Value = sumaKilosNetosSalida;
ws.Cell(9 + listaIngreso.Count + 1, 28).Style.Font.Bold = true;
ws.Cell(9 + listaIngreso.Count + 1, 31).Value = sumaKilosNetos - sumaKilosNetosSalida;
ws.Cell(9 + listaIngreso.Count + 1, 31).Style.Font.Bold = true;
int g = 0;
for (int i = 0; i <= listaIngreso.Count - 1; i++)
{
g = 10 + i;
for (int y = 2; y <= 31; y++)
{
ws.Cell(g, y).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
}
/*
* ws.Cell("R15").Value = 70;
* ws.Cell("V15").Value = 4249.5;
* ws.Cell("Y15").Value = 4111.32;
* ws.Cell("Z15").Value = "Soles";
* ws.Cell("AB15").Value = 31099.83;
* ws.Cell("AO15").Value = 46;
* ws.Cell("AR15").Value = 2963.30;
* ws.Cell("AU15").Value = 2901.04;
* ws.Cell("AX15").Value = 21372.80;
*
* ws.Range("B15:AH15").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Range("B15:AH15").Style.Fill.BackgroundColor = XLColor.LightGray;
* ws.Range("AI15:AZ15").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
* ws.Range("AI15:AZ15").Style.Fill.BackgroundColor = XLColor.LightGray;
*/
//FIN: DATA HARDCODE
ws.ColumnsUsed().AdjustToContents();
ws.RowsUsed().AdjustToContents();
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
respose.content = stream.ToArray();
}
}
}
catch (Exception ex)
{
}
return(respose);
}
private int GetIndexExistRow(IXLWorksheet worksheet, int workingRow, int workingCol, int workingIndex, string workingValue, Dictionary <string, List <string> > workingDictionary)
{
int rowCount = 0;
int colCount = 0;
var rowUsed = worksheet.RowsUsed();
var colUsed = worksheet.ColumnsUsed();
foreach (var row in rowUsed)
{
if (row.RowNumber() > 1)
{
string currentValue = common.GetValue(worksheet, row.RowNumber(), workingCol);
if (currentValue == workingValue)
{
// get row has exist data
int rowExist = row.RowNumber();
// loop all col check data
int colLoopCount = 0;
int colExistLoopCount = 0;
foreach (var col in colUsed)
{
// check exist data clo bigger col working
if (col.ColumnNumber() > workingCol)
{
// get header name
string nameHeader = common.GetValue(worksheet, 1, col.ColumnNumber());
// get value dictionary by key (header name)
List <string> list = workingDictionary[nameHeader];
string valueChecking = list[workingIndex];
if (valueChecking != "")
{
// get current value
currentValue = common.GetValue(worksheet, rowExist, col.ColumnNumber());
if (valueChecking == currentValue)
{
colExistLoopCount++;
}
colLoopCount++;
}
}
colCount++;
}
if (colExistLoopCount == colLoopCount)
{
return(workingIndex);
}
}
}
rowCount++;
}
return(-1);
}
public async Task <ActionResult> OrderPembelianExcel(int id)
{
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
try
{
var pembelian = await _pembelianService.GetOrder(id);
string fileName = $"Order Pembelian No:{pembelian.Nomor}.xlsx";
using var workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Order");
worksheet.Cell(1, 1).Value = "ORDER PEMBELIAN";
worksheet.Cell(2, 1).Value = $"Kepada : {pembelian.Supplier.Nama}";
var range = worksheet.Range("A1:G1");
range.Merge().Style.Font.SetBold().Font.FontSize = 16;
var range2 = worksheet.Range("A2:G2");
range2.Merge().Style.Font.SetBold().Font.FontSize = 14;
var start = 4;
worksheet.Cell(start, 1).Value = "Nomor";
worksheet.Cell(start, 2).Value = "Article";
worksheet.Cell(start, 3).Value = "Nama";
worksheet.Cell(start, 4).Value = "Jumlah";
worksheet.Cell(start, 5).Value = "Harga";
worksheet.Cell(start, 6).Value = $"Discount";
worksheet.Cell(start, 7).Value = "Total";
var items = pembelian.Items.ToList();
int nomor = 1;
for (int index = 1; index <= items.Count; index++)
{
var total = items[index - 1].Total;
worksheet.Cell(index + start, 1).Value = nomor;
worksheet.Cell(index + start, 2).Value = items[index - 1].Product.CodeArticle;
worksheet.Cell(index + start, 3).Value = items[index - 1].Product.Name + " | " + items[index - 1].Product.Size;
worksheet.Cell(index + start, 4).Value = items[index - 1].Quntity;
worksheet.Cell(index + start, 5).Value = items[index - 1].Price;
worksheet.Cell(index + start, 6).Value = total * 10 / 100;
worksheet.Cell(index + start, 7).Value = total - (total * 10 / 100);
worksheet.Cell(index + start, 5).Style.NumberFormat.Format = "0,000.00";
worksheet.Cell(index + start, 6).Style.NumberFormat.Format = "0,000.00";
worksheet.Cell(index + start, 7).Style.NumberFormat.Format = "0,000.00";
nomor++;
}
worksheet.Range($"A{start}:G{items.Count + start}").Style.Border.InsideBorder = XLBorderStyleValues.Thin;
worksheet.Range($"A{start}:G{items.Count + start}").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
foreach (var item in worksheet.ColumnsUsed())
{
item.AdjustToContents();
}
worksheet.Cell(items.Count + 8, 1).Value = "Hormat Kami";
worksheet.Cell(items.Count + 12, 1).Value = "Elish";
var rangeAsign = worksheet.Range($"A{items.Count + 8}:B{items.Count + 8}");
rangeAsign.Merge().Style.Font.SetBold().Font.FontSize = 12;
using var stream = new MemoryStream();
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, contentType, fileName));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
// 创建一个 Sheet
IXLWorksheet CreateSheet(XLWorkbook doc,
string sheet_name,
List <TransferGroup> groups)
{
IXLWorksheet sheet = doc.Worksheets.Add(Cut(GetSheetName(sheet_name), 31));
// 每个列的最大字符数
// List<int> column_max_chars = new List<int>();
// 准备书目列标题
var biblio_column_option = new TransferColumnOption(Program.MainForm.UserDir);
biblio_column_option.LoadData(Program.MainForm.AppInfo,
ColumnDefPath);
List <Order.ColumnProperty> biblio_title_list = Order.DistributeExcelFile.BuildList(biblio_column_option);
List <string> headers = new List <string>();
biblio_title_list.ForEach(o => headers.Add(o.Caption));
int nRowIndex = 1;
int nColIndex = 1;
// 输出表格标题
{
var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
range.Merge();
range.SetValue(sheet_name);
range.Style.Alignment.WrapText = true;
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range.Style.Font.Bold = true;
nRowIndex++;
}
int lines = 0;
groups.ForEach(o => lines += o.Items.Count);
{
var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
range.Merge();
range.SetValue($"行数: {lines}, 打印日期: {DateTime.Now.ToString("yyyy-M-d")}");
range.Style.Alignment.WrapText = true;
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
nRowIndex++;
}
nRowIndex++;
foreach (string header in headers)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(header, '*'));
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
//cell.Style.Font.FontName = strFontName;
//cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
}
{
// 设置边框
var range = sheet.Range(nRowIndex, 1, nRowIndex, biblio_title_list.Count);
range.Style.Border.SetBottomBorderColor(XLColor.Black);
range.Style.Border.SetBottomBorder(XLBorderStyleValues.Medium);
}
nRowIndex++;
// 用于发现重复的册
// recpath --> (此路径的第一个) TransferItem
Hashtable recpath_table = new Hashtable();
int dup_count = 0; // 记录路径发生重复的行数
int notchange_count = 0; // 馆藏地没有发生改变的行数
int nNo = 1;
foreach (var group in groups)
{
foreach (var item in group.Items)
{
bool dup = false;
if (recpath_table.ContainsKey(item.RecPath))
{
dup = true;
dup_count++;
}
else
{
recpath_table[item.RecPath] = item;
}
bool onlyWriteLog = StringUtil.IsInList("onlyWriteLog", item.Style);
nColIndex = 1;
// WriteCell(nNo.ToString());
// WriteCell(item.Barcode);
// 获得册记录
XmlDocument itemdom = new XmlDocument();
itemdom.LoadXml(item.NewXml);
string strParentID = DomUtil.GetElementText(itemdom.DocumentElement, "parent");
// 输出书目列
WriteBiblioColumns(
item.RecPath,
strParentID,
biblio_title_list,
sheet,
nColIndex - 1,
nRowIndex - 1);
OutputTransferColumns(
nNo,
item,
sheet,
nColIndex - 1,
Order.ColumnProperty.GetTypeList(biblio_title_list, false),
nRowIndex - 1);
/*
* nColIndex += biblio_title_list.Count;
*
* WriteCell(item.SourceLocation);
* WriteCell(item.TargetLocation);
* WriteCell(item.BatchNo);
* WriteCell(item.OperTime.ToString());
* WriteCell(item.Operator);
*/
// 设置边框
var range = sheet.Range(nRowIndex, 1, nRowIndex, biblio_title_list.Count);
range.Style.Border.SetBottomBorderColor(XLColor.Black);
range.Style.Border.SetBottomBorder(XLBorderStyleValues.Hair);
if (dup)
{
range.Style.Fill.BackgroundColor = XLColor.Yellow;
}
if (onlyWriteLog)
{
notchange_count++;
// 寻找 SourceLocation 和 TargetLocation 列
{
int index = biblio_title_list.FindIndex(o => o.Type == "log_sourceLocation");
if (index != -1)
{
sheet.Cell(nRowIndex, 1 + index).Style.Fill.BackgroundColor = XLColor.LightBlue;
}
}
{
int index = biblio_title_list.FindIndex(o => o.Type == "log_targetLocation");
if (index != -1)
{
sheet.Cell(nRowIndex, 1 + index).Style.Fill.BackgroundColor = XLColor.LightBlue;
}
}
}
nRowIndex++;
nNo++;
}
}
// 警告行
if (dup_count > 0)
{
nRowIndex++;
var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
range.Merge();
range.Style.Fill.BackgroundColor = XLColor.DarkGray;
range.Style.Font.FontColor = XLColor.White;
range.Style.Font.Bold = true;
range.SetValue($"警告：表中有 {dup_count} 个重复的册记录行。这些行已显示为黄色背景色");
range.Style.Alignment.WrapText = true;
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
nRowIndex++;
}
// 警告行
if (notchange_count > 0)
{
var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1);
range.Merge();
range.Style.Fill.BackgroundColor = XLColor.DarkGray;
range.Style.Font.FontColor = XLColor.White;
range.SetValue($"提醒：表中有 {notchange_count} 个册记录行在移交操作中馆藏地并没有发生变化。这些行的源、目标馆藏地列已显示为蓝色背景色");
range.Style.Alignment.WrapText = true;
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
nRowIndex++;
}
// TODO: 设置 header 和 footer
// https://stackoverflow.com/questions/34104107/closedxml-change-existing-footer-header
double char_width = DigitalPlatform.dp2.Statis.ClosedXmlUtil.GetAverageCharPixelWidth(Program.MainForm);
// 字符数太多的列不要做 width auto adjust
const int MAX_CHARS = 30; // 60
int i = 0;
foreach (IXLColumn column in sheet.ColumnsUsed())
{
int nChars = GetMaxChars(column);
column.Width = Math.Min(nChars, MAX_CHARS);
#if NO
if (nChars < MAX_CHARS)
{
// column.AdjustToContents();
column.Width = nChars;
}
else
{
int nColumnWidth = 100;
/*
* // 2020/1/6 增加保护判断
* if (i >= 0 && i < list.Columns.Count)
* nColumnWidth = list.Columns[i].Width;
*/
column.Width = (double)nColumnWidth / char_width; // Math.Min(MAX_CHARS, nChars);
}
#endif
i++;
}
return(sheet);
#if NO
void WriteCell(string text)
{
// 统计最大字符数
// int nChars = column_max_chars[nColIndex - 1];
if (text != null)
{
DigitalPlatform.dp2.Statis.ClosedXmlUtil.SetMaxChars(column_max_chars, nColIndex - 1, text.Length);
}
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(text, '*'));
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// cell.Style.Font.FontName = strFontName;
/*
* if (nColIndex - 1 < alignments.Count)
* cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
* else
* cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
*/
nColIndex++;
}
#endif
}