private void MontarCabecalho(IXLWorksheet worksheet, PropertyInfo[] properties)
{
for (var column = 0; column < properties.Length; column++)
{
var propriedadeRaiz = properties.GetValue(column);
var colunaValor = ((MemberInfo)propriedadeRaiz).Name;
var nomePropriedade = ((MemberInfo)propriedadeRaiz).CustomAttributes.FirstOrDefault(a => a.AttributeType.Name == "DisplayAttribute");
if (nomePropriedade != null)
{
var propriedadeAtributoDescricao = nomePropriedade.NamedArguments.FirstOrDefault(a => a.MemberName == "Description");
if (nomePropriedade != null)
{
colunaValor = propriedadeAtributoDescricao.TypedValue.Value.ToString();
}
}
var colunaNome = $"{Number2String(column, true)}{1}";
CabecalhoFormataStylo(worksheet, colunaNome);
worksheet.Cells(colunaNome).Value = colunaValor;
}
}
PopulationByAges GetAllDataFromWorksheet(IXLWorksheet worksheet, Dictionary <string, HeaderItem> headers, string primaryKey)
{
var headerCells = worksheet.Cells();
foreach (var header in headers)
{
header.Value.Address = GetSpecificHeaderColumn(headerCells, header.Key);
}
var lastRowNumber = worksheet.LastRowUsed().Cell(headers[primaryKey].Address.ColumnNumber).Address.RowNumber;
var lastColNumber = worksheet.LastColumnUsed().Cell(headers[primaryKey].Address.RowNumber).Address.ColumnNumber;
var bodyCells = worksheet.Range($"A1:{ToColumnName(lastColNumber)}{lastRowNumber}").Cells();
var primaryRows = bodyCells.Where(i => i.Address.ColumnNumber == headers[primaryKey].Address.ColumnNumber).Select(i => i);
StringBuilder sb = new StringBuilder();
sb.Append("{\"contents\":[");
foreach (var primaryRow in primaryRows)
{
Regex regex = new Regex("^[0-9]");
if (regex.IsMatch(primaryRow.Value.ToString().Trim()) == false)
{
continue;
}
sb.Append("{");
var excelRow = primaryRow.Address.RowNumber;
foreach (var header in headers)
{
var valueObject = worksheet.Cells().Where(i => i.Address.RowNumber == excelRow &&
i.Address.ColumnNumber == headers[header.Key].Address.ColumnNumber)
.Select(i => i.Value)
.FirstOrDefault();
sb.Append($"\"{header.Key}\":\"{valueObject.ToString().Trim()}\",");
}
sb.ToString().Remove(sb.Length - 1, 1);
sb.Append("},");
}
sb.ToString().Remove(sb.Length - 1, 1);
sb.Append("]}");
return(JsonConvert.DeserializeObject <PopulationByAges>(sb.ToString()));
}
public void WsNamedCells()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell", XLScope.Worksheet);
ws.Cell(2, 1).SetValue("B");
IXLCells cells = ws.Cells("TestCell, A2");
Assert.AreEqual("Test", cells.First().GetString());
Assert.AreEqual("B", cells.Last().GetString());
}
/// <summary>
/// Read object data from XLSX worksheet
/// </summary>
/// <param name="worksheet">worksheet</param>
/// <param name="row">Row index</param>
/// /// <param name="cellOffset">Cell offset</param>
public virtual void ReadFromXlsx(IXLWorksheet worksheet, int row, int cellOffset = 0)
{
if (worksheet?.Cells() == null)
{
return;
}
foreach (var prop in _properties.Values)
{
prop.PropertyValue = worksheet.Row(row).Cell(prop.PropertyOrderPosition + cellOffset).Value;
}
}
private static void CorpoFormataStylo(IXLWorksheet worksheet, string celulaNome)
{
worksheet.Cells(celulaNome).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cells(celulaNome).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cells(celulaNome).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cells(celulaNome).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cells(celulaNome).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cells(celulaNome).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cells(celulaNome).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cells(celulaNome).Style.Border.LeftBorderColor = XLColor.Black;
}
public IList <House> GetHouses(string path)
{
XLWorkbook wb = new XLWorkbook(path);
IXLWorksheet sheet = wb.Worksheets.First();
IXLRange RangeSheet = sheet.RangeUsed();
int rightBorder = RangeSheet.LastColumn().ColumnNumber(); //установим правую границу данных
int downBorder = RangeSheet.LastRow().RowNumber(); //установим нижнюю границу данных
List <IXLCell> Xlhouses = sheet.Cells().Where(c => c.GetValue <string>().Contains("Дом")).ToList(); //получим ячейки, в которых номера домов
List <House> houses = new List <House>();
foreach (var Xlhouse in Xlhouses)
{
House house = new House();
house.Name = Xlhouse.GetValue <string>();
house.Flats = new List <Flat>();
IXLCell cell = Xlhouse.CellBelow();
while (!(cell.GetValue <string>() == "" &&
cell.CellBelow().GetValue <string>() == "" &&
cell.CellRight().GetValue <string>() == "" &&
cell.CellRight().CellBelow().GetValue <string>() == ""
) &&
cell.WorksheetColumn().ColumnNumber() <= rightBorder
) //рассматриваем, есть ли значение в ячейке под названием дома, а также в ближайших от нее соседей
{
IXLCell cellBellowHouse = cell;
while (!(cell.GetValue <string>() == "" &&
cell.CellBelow().GetValue <string>() == ""
) &&
cell.WorksheetRow().RowNumber() <= downBorder
) //смотрим есть ли в текущей ячейке значение или той, которая находится снизу
{
if (cell.GetValue <string>().Contains("№"))
{
Flat flat = new Flat();
flat.Number = cell.GetValue <string>().Substring(1);
flat.Price = cell.CellBelow().GetValue <string>();
house.Flats.Add(flat);
}
cell = cell.CellBelow();
}
cell = cellBellowHouse.CellRight(); //после того, как закончили с колонкой, переходим в следующую колонку
//самую верхнюю под номером дома
}
houses.Add(house);
}
return(houses);
//throw new NotImplementedException();
}
private static void CabecalhoFormataStylo(IXLWorksheet worksheet, string colunaNome)
{
worksheet.Cells(colunaNome).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cells(colunaNome).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cells(colunaNome).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cells(colunaNome).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cells(colunaNome).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cells(colunaNome).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cells(colunaNome).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cells(colunaNome).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cells(colunaNome).Style.Font.Bold = true;
worksheet.Columns().AdjustToContents();
}
//TODO: Delegate convertation to item method.
//TODO: Catch "Too many requests", add timeout and try again.
public static void ObjToExcel(MarketplaceList items, bool open = false)
{
Console.WriteLine("Creating xlsx file. This can take a while.");
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Coins");
worksheet.Cell(1, 1).Value = "Link";
worksheet.Cell(1, 2).Value = "Name";
worksheet.Cell(1, 3).Value = "Code";
worksheet.Cell(1, 4).Value = "Color";
worksheet.Cell(1, 5).Value = "Amount";
worksheet.Cell(1, 6).Value = "Price";
worksheet.Cells(true).Style.Font.SetBold();
for (int i = 0; i < items.Count; i++)
{
MarketplaceItem item = items[i];
worksheet.Cell(i + 2, 1).Hyperlink = new XLHyperlink(@item.Link);
worksheet.Cell(i + 2, 1).Value = @item.Link;
worksheet.Cell(i + 2, 2).Value = item.Name;
worksheet.Cell(i + 2, 3).Value = item.Code;
worksheet.Cell(i + 2, 4).Value = item.ColorStr;
worksheet.Cell(i + 2, 4).Style.Fill.BackgroundColor = XLColor.FromArgb(item.ColorHex);
worksheet.Cell(i + 2, 5).Value = item.Amount;
worksheet.Cell(i + 2, 6).Value = item.Price;
worksheet.Cell(i + 2, 6).Style.NumberFormat.Format = "0.00" + item.PriceCurrency;
}
worksheet.Columns(2, 6).AdjustToContents(10.0, 40.0);
DateTime current = DateTime.Now;
string saveLocation = SaveFolder + current.ToString("yyyy.MM.dd HH.mm.ss") + ".xlsx";
Console.WriteLine("Saving your file to {0}", saveLocation);
workbook.SaveAs(@saveLocation);
if (open)
{
string path = Path.Combine(Environment.CurrentDirectory, saveLocation);
ProcessStartInfo pInfo = new ProcessStartInfo(saveLocation)
{
UseShellExecute = true
};
Process.Start(pInfo);
}
}
private void MontarCorpo(GerarExcelGenericoCommand request, IXLWorksheet worksheet)
{
var bindingFlags = System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.NonPublic |
System.Reflection.BindingFlags.Public;
var iLinha = 2;
foreach (var item in request.ObjetoExportacao)
{
List <object> listValues = item.GetType().GetFields(bindingFlags).Select(field => field.GetValue(item)).Where(value => value != null).ToList();
var iPropriedade = 0;
foreach (var item1 in listValues)
{
var celulaNome = $"{Number2String(iPropriedade, true)}{iLinha}";
CorpoFormataStylo(worksheet, celulaNome);
worksheet.Cells(celulaNome).Value = item1.ToString();
iPropriedade++;
}
iLinha++;
}
}
private int creteFiveReasonAllDesignt(int currentIndex, IXLWorksheet ws, DFourTable item)
{
int capsuleLastCellIndex;
int capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden 1:";
ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden1;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 1:";
ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden1;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden 2:";
ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden2;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 2:";
ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden2;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden 3:";
ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden3;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 3:";
ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden3;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden 4:";
ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden4;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 4:";
ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden4;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden 5:";
ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden5;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 5:";
ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden5;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, false);
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false);
return(currentIndex);
}
public FileStreamResult createExcellReport(string errorNo)
{
//Create an instance of ExcelEngine
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet ws = workbook.Worksheets.Add("8D Rapor");
var dOneTable = db.DOneTable.Where(x => x.ErrorNo == errorNo).FirstOrDefault();
var dTwoTable = db.DTwoTable.Where(x => x.ErrorNo == errorNo).FirstOrDefault();
var dThreeTable = db.DThreeTable.Where(x => x.ErrorNo == errorNo).ToList();
var dFourTable = db.DFourTable.Where(x => x.ErrorNo == errorNo).ToList();
var dFiveTable = db.DFiveTable.Where(x => x.ErrorNo == errorNo).ToList();
var dSixTable = db.DSixTable.Where(x => x.ErrorNo == errorNo).ToList();
var dSevenTable = db.DSevenTable.Where(x => x.ErrorNo == errorNo).ToList();
var dEightTable = db.DEightTable.Where(x => x.ErrorNo == x.ErrorNo).FirstOrDefault();
var errorList = db.ErrorListTable.Where(x => x.ErrorNo == errorNo).FirstOrDefault();
//-----------------------------------------------------------------
// WorkSheet Settings
ws.Column(1).Width = 18;
ws.PageSetup.ShowGridlines = false; // halen gösteriyor
// Başlık tasarımı
int currentIndex = 1;
currentIndex = setBigHeaderWithColor("8D Rapor", XLColor.YellowMunsell, currentIndex, ws, 3, false);
// tablo elemanları atanmaya başlarız
int capsuleStartCellIndex;
int capsuleLastCellIndex;
// 1. Kısım--------------------------------------------------
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cell("A" + currentIndex).Value = "Hata Numarası";
ws.Cell("B" + currentIndex).Value = errorNo;
currentIndex++;
ws.Cell("A" + currentIndex).Value = "Oluşturma Tarihi";
ws.Cell("B" + currentIndex).Value = Convert.ToDateTime(errorList.CreateDate.ToString()).ToShortDateString();
currentIndex++;
ws.Cell("A" + currentIndex).Value = "Kapanma Tarihi";
ws.Cell("B" + currentIndex).Value = Convert.ToDateTime(errorList.CloseDate.ToString()).ToShortDateString();
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "E", capsuleLastCellIndex, ws, false); // text
ws.Cell("F" + capsuleStartCellIndex).Value = "Müşteri";
ws.Cell("G" + capsuleStartCellIndex).Value = errorList.Costumer;
capsuleDesignChangeColor("F", capsuleStartCellIndex, "F", capsuleStartCellIndex, ws, true); // Header
capsuleDesignChangeColor("G", capsuleStartCellIndex, "H", capsuleStartCellIndex, ws, false);
capsuleDesignChangeColor("F", capsuleStartCellIndex + 1, "H", capsuleLastCellIndex, ws, false);
currentIndex++; // boşluk bırak
// 2. Kısım-----------------------------------------------
currentIndex++; // yeni ndexten başla
capsuleStartCellIndex = currentIndex;
ws.Cell("A" + currentIndex.ToString()).Value = "Parça Numarası ";
ws.Cell("B" + currentIndex.ToString()).Value = errorList.PartNumber == null ? string.Empty : errorList.PartNumber.ToString();
currentIndex++;
ws.Cell("A" + currentIndex.ToString()).Value = "Referans Numarası";
ws.Cell("B" + currentIndex.ToString()).Value = errorList.ReferenceNumber.ToString();
currentIndex++;
ws.Cell("A" + currentIndex.ToString()).Value = "Parça Adı";
ws.Cell("B" + currentIndex.ToString()).Value = errorList.PartName == null ? string.Empty : errorList.PartName.ToString();
currentIndex++;
ws.Cell("A" + currentIndex.ToString()).Value = "Hata Kodu";
ws.Cell("B" + currentIndex.ToString()).Value = errorList.ErrorCode.ToString();
currentIndex++;
ws.Cell("A" + currentIndex.ToString()).Value = "Hata Tanımı";
ws.Cell("B" + currentIndex.ToString()).Value = errorList.ErrorDefine;
currentIndex++;
ws.Cell("A" + currentIndex.ToString()).Value = "Hata Açıklama";
ws.Cell("B" + currentIndex.ToString()).Value = errorList.ErrorDescription;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++; // boşluk bırak
// 3. Kısım-----------------------------------------------------
currentIndex++;
crateMiniHeader("D1- Ekip Üyeleri", currentIndex, ws);
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Takım Lideri";
ws.Cells("B" + currentIndex.ToString()).Value = dOneTable.TeamLeader;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Takım Üyeleri";
ws.Cells("B" + currentIndex.ToString()).Value = dOneTable.TeamMembers;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++; // boşluk bırak
// 4. Kısım-----------------------------------------------------
currentIndex++;
crateMiniHeader("D2- Hata Tanımı", currentIndex, ws);
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Hata Tanımı";
ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.ErrorDefine;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Açıklaması";
ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.ErrorDescription;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.Person;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Tarih";
ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.Date.ToShortDateString();
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++; // boşluk bırak
// 5. Kısım-----------------------------------------------------
currentIndex++;
crateMiniHeader("D3 - Acil Önlemler", currentIndex, ws);
int counter = 0;
foreach (var item in dThreeTable)
{
counter++; // 1. ,2.,3... Aksiyonlar
currentIndex++;
createActionHeader("Aksiyon " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Aksiyon";
ws.Cells("B" + currentIndex.ToString()).Value = item.Action;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Uygulama Tarihi";
ws.Cells("B" + currentIndex.ToString()).Value = item.TransactionDate.ToShortDateString();
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Tarih";
ws.Cells("B" + currentIndex.ToString()).Value = item.Date.ToShortDateString();
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
ws.Cells("B" + currentIndex.ToString()).Value = item.Person;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++;
}
currentIndex++; // boşluk bırakılır
// 6. Kısım
crateMiniHeader("D4 - Hata Oluşma Nedeni", currentIndex, ws);
// Makine Alanı
currentIndex++;
createActionHeader("Makine", currentIndex, ws);
foreach (var item in dFourTable)
{
if (item.Machine != null)
{
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = item.Machine;
capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
currentIndex++;
currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
}
}
// Methot Alanı
currentIndex++;
createActionHeader("Methot", currentIndex, ws);
foreach (var item in dFourTable)
{
if (item.Method != null)
{
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = item.Method;
capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
currentIndex++;
currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
}
}
// Malzeme Alanı
currentIndex++;
createActionHeader("Malzeme", currentIndex, ws);
foreach (var item in dFourTable)
{
if (item.Material != null)
{
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = item.Material;
capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
currentIndex++;
currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
}
}
// İnsan Alanı
currentIndex++;
createActionHeader("İnsan", currentIndex, ws);
foreach (var item in dFourTable)
{
if (item.Men != null)
{
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = item.Men;
capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
currentIndex++;
currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
}
}
// Cevre Alanı
currentIndex++;
createActionHeader("Cevre", currentIndex, ws);
foreach (var item in dFourTable)
{
if (item.Monde != null)
{
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = item.Monde;
capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
currentIndex++;
currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
}
}
currentIndex++;
// 7. Kısım
currentIndex++;
crateMiniHeader("D5 - Öngörülen Düzletici Önlemler", currentIndex, ws);
counter = 0;
foreach (var item in dFiveTable)
{
counter++; // 1. ,2.,3... Aksiyonlar
currentIndex++;
createActionHeader("Önlem " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Hata Tipi";
ws.Cells("B" + currentIndex.ToString()).Value = item.ErrorType;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Önlem";
ws.Cells("B" + currentIndex.ToString()).Value = item.Precautions;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Termin Tarihi";
ws.Cells("B" + currentIndex.ToString()).Value = item.Deadline.ToShortDateString();
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
ws.Cells("B" + currentIndex.ToString()).Value = item.Person;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++;
}
// 8. Kısım
currentIndex++;
crateMiniHeader("D6 - Uygulanan Düzletici Önlemler", currentIndex, ws);
counter = 0;
foreach (var item in dSixTable)
{
counter++; // 1. ,2.,3... Aksiyonlar
currentIndex++;
createActionHeader("Önlem " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Hata Tipi";
ws.Cells("B" + currentIndex.ToString()).Value = item.ErrorType;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Düzeltici Önlem";
ws.Cells("B" + currentIndex.ToString()).Value = item.MeasuresTaken;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Uygulama Tarihi";
ws.Cells("B" + currentIndex.ToString()).Value = item.ProcessDate.ToShortDateString();
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Kalan Zaman";
ws.Cells("B" + currentIndex.ToString()).Value = item.TimeRemaining;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Gecikme Sebebi";
ws.Cells("B" + currentIndex.ToString()).Value = item.DelayReason;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
ws.Cells("B" + currentIndex.ToString()).Value = item.Person;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++;
}
// 9. Kısım
currentIndex++;
crateMiniHeader("D7 - Hata Tekrarını Önleyici Tedbirler", currentIndex, ws);
counter = 0;
foreach (var item in dSevenTable)
{
counter++; // 1. ,2.,3... Aksiyonlar
currentIndex++;
createActionHeader("Tedbir " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
currentIndex++;
capsuleStartCellIndex = currentIndex;
ws.Cells("A" + currentIndex.ToString()).Value = "Tedbir";
ws.Cells("B" + currentIndex.ToString()).Value = item.Prevention;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Sorumlu";
ws.Cells("B" + currentIndex.ToString()).Value = item.Responsible;
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Uygulama Tarihi";
ws.Cells("B" + currentIndex.ToString()).Value = item.ProcessDate.ToShortDateString();
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Tarih";
ws.Cells("B" + currentIndex.ToString()).Value = item.Date.ToShortDateString();
currentIndex++;
ws.Cells("A" + currentIndex.ToString()).Value = "Firma";
ws.Cells("B" + currentIndex.ToString()).Value = item.Company;
capsuleLastCellIndex = currentIndex;
capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true); // header
capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text
currentIndex++;
}
// Hata Kabul Veya Red
if (dEightTable.RejectionReason == null) // hata onay
{
currentIndex = setBigHeaderWithColor("Hata Kabul Edildi", XLColor.LightGreen, currentIndex, ws, 3, false);
}
else
{
currentIndex = setBigHeaderWithColor("Hata Reddedildi", XLColor.Red, currentIndex, ws, 3, false);
}
currentIndex++;
var imagePath = AppDomain.CurrentDomain.BaseDirectory + "images/inanSoftLogo.png";
importPicture(ws, "G", currentIndex, imagePath);
MemoryStream MS = new MemoryStream();
workbook.SaveAs(MS);
MS.Position = 0;
return(new FileStreamResult(MS, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = errorNo + "_8DRapor.xlsx"
});
}
public void TestDelete()
{
// Deleting with moving cells up
XLWorkbook wb = InitWorkBookForDeleteRangeTest();
IXLWorksheet ws = wb.Worksheet("Test");
IXLNamedRange parentRange = ws.NamedRange("Parent");
IXLNamedRange childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
}
};
panel.Delete();
IXLCell rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
IXLCell rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
IXLCell belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
IXLCell belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
IXLCell rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
IXLCell rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
IXLCell aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
IXLCell aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
IXLCell leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
IXLCell leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting with moving the row up
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
ShiftType = ShiftType.Row,
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(leftCell1);
Assert.IsNull(rightCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(6, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell2, ws.Cell(6, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting with moving cells left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
Type = PanelType.Horizontal
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting with moving the column left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(aboveCell1);
Assert.IsNull(belowCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell2, ws.Cell(10, 5));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 5));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting without any shift
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
ShiftType = ShiftType.NoShift,
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).FirstCell().Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).Style.Border.BottomBorder);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
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);
}
private static void WriteHeaders <T>(IXLWorksheet worksheet) where T : Player
{
worksheet.Cell("A2").Value = "Player";
worksheet.Cell("C1").Value = "Game";
worksheet.Cell("C2").Value = "G";
if (typeof(T) == typeof(PassingPlayer))
{
worksheet.Cell("B2").Value = "Cmp";
worksheet.Cell("D2").Value = "Att";
worksheet.Cell("E2").Value = "Yds";
worksheet.Cell("F2").Value = "TD";
worksheet.Cell("G2").Value = "Int";
worksheet.Cell("H2").Value = "Sk";
worksheet.Cell("I2").Value = "Team";
}
if (typeof(T) == typeof(KickingPlayer))
{
worksheet.Cells("B1, H1").Value = "Punt";
worksheet.Cells("D1:G1").Value = "Scor";
worksheet.Cell("B2").Value = "Pnt";
worksheet.Cell("D2").Value = "XPM";
worksheet.Cell("E2").Value = "XPA";
worksheet.Cell("F2").Value = "FGM";
worksheet.Cell("G2").Value = "FGA";
worksheet.Cell("H2").Value = "Yds";
worksheet.Cell("I2").Value = "Team";
}
if (typeof(T) == typeof(DefensePlayer))
{
worksheet.Cell("B1").Value = "Def";
worksheet.Cell("B2").Value = "Int";
worksheet.Cell("D2").Value = "Sk";
worksheet.Cells("E1:G1").Value = "Tack";
worksheet.Cell("E2").Value = "Solo";
worksheet.Cell("F2").Value = "Ast";
worksheet.Cell("G2").Value = "TFL";
worksheet.Cells("H1:I1").Value = "Def";
worksheet.Cell("H2").Value = "TD";
worksheet.Cell("I2").Value = "PD";
worksheet.Cells("J1:M1").Value = "Fumb";
worksheet.Cell("J2").Value = "FR";
worksheet.Cell("K2").Value = "Yds";
worksheet.Cell("L2").Value = "TD";
worksheet.Cell("M2").Value = "FF";
worksheet.Cell("N2").Value = "Team";
}
if (typeof(T) == typeof(ReceivingPlayer))
{
worksheet.Cell("B2").Value = "Rec";
worksheet.Cell("D2").Value = "Yds";
worksheet.Cell("E2").Value = "TD";
worksheet.Cell("F2").Value = "Team";
}
if (typeof(T) == typeof(ReturningPlayer))
{
worksheet.Cells("B1, D1:E1").Value = "Kick";
worksheet.Cells("F1:H1").Value = "Punt";
worksheet.Cell("B2").Value = "Rt";
worksheet.Cell("D2").Value = "Yds";
worksheet.Cell("E2").Value = "TD";
worksheet.Cell("F2").Value = "Ret";
worksheet.Cell("G2").Value = "Yds";
worksheet.Cell("H2").Value = "TD";
worksheet.Cell("I2").Value = "Team";
}
if (typeof(T) == typeof(RushingPlayer))
{
worksheet.Cells("B1, D1:E1").Value = "Rush";
worksheet.Cell("B2").Value = "Att";
worksheet.Cell("D1").Value = "Rush";
worksheet.Cell("D2").Value = "Yds";
worksheet.Cell("E1").Value = "Rush";
worksheet.Cell("E2").Value = "TD";
worksheet.Cell("F2").Value = "Fmb";
worksheet.Cell("G2").Value = "Team";
}
}
public void SaveExamSheet(Object[] examGeneration, string fileName)
{
List <Exam> exams = (List <Exam>)examGeneration[0];
List <Exam> unassigned = (List <Exam>)examGeneration[1];
List <Exam> availableRooms = (List <Exam>)examGeneration[2];
XLWorkbook doc = new XLWorkbook();
IXLWorksheet sheet = doc.Worksheets.Add("Exam Schedule");
sheet.Column("A").Width = 15;
sheet.Column("B").Width = 60;
sheet.Column("C").Width = 10;
sheet.Columns("D:I").Width = 20;
int row = 1;
if (unassigned.Count != 0)
{
sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
sheet.Cell("A" + row).Value = "There weren't enough rooms available to fit these exams.";
row++;
sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
sheet.Row(row).Style.Font.SetBold();
sheet.Cell("A" + row).Value = "Code";
sheet.Cell("B" + row).Value = "Course Name";
sheet.Cell("C" + row).Value = "Section";
sheet.Cell("D" + row).Value = "Faculty";
foreach (Exam datarow in unassigned)
{
row++;
sheet.Cell("A" + row).Value = datarow.Code;
sheet.Cell("B" + row).Value = datarow.Name;
sheet.Cell("C" + row).Value = datarow.Section;
sheet.Cell("D" + row).Value = datarow.Faculty;
}
row++;
sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
sheet.Cell("A" + row).Value = "Here's a list of available rooms.";
row++;
sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
sheet.Row(row).Style.Font.SetBold();
sheet.Cell("D" + row).Value = "Faculty";
sheet.Cell("F" + row).Value = "Room";
sheet.Cell("G" + row).Value = "Day";
sheet.Cell("H" + row).Value = "Time";
foreach (Exam datarow in availableRooms)
{
row++;
sheet.Cell("D" + row).Value = datarow.Faculty;
sheet.Cell("F" + row).Value = datarow.Room;
sheet.Cell("G" + row).Value = datarow.Day;
sheet.Cell("H" + row).Value = datarow.Start + " - " + datarow.End;
}
row++;
}
sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Charcoal);
sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
sheet.Row(row).Style.Font.SetBold();
sheet.Cell("A" + row).Value = "Code";
sheet.Cell("B" + row).Value = "Course Name";
sheet.Cell("C" + row).Value = "Section";
sheet.Cell("D" + row).Value = "Faculty";
sheet.Cell("E" + row).Value = "Proctor";
sheet.Cell("F" + row).Value = "Room";
sheet.Cell("G" + row).Value = "Day";
sheet.Cell("H" + row).Value = "Time";
sheet.Cell("I" + row).Value = "Duration";
foreach (Exam datarow in exams)
{
row++;
sheet.Cell("A" + row).Value = datarow.Code;
sheet.Cell("B" + row).Value = datarow.Name;
sheet.Cell("C" + row).Value = datarow.Section;
sheet.Cell("D" + row).Value = datarow.Faculty;
sheet.Cell("E" + row).Value = datarow.Proctor;
sheet.Cell("F" + row).Value = datarow.Room;
sheet.Cell("G" + row).Value = datarow.Day;
sheet.Cell("H" + row).Value = datarow.Start + " - " + datarow.End;
sheet.Cell("I" + row).Value = datarow.Duration;
}
doc.SaveAs(AppDomain.CurrentDomain.GetData("DataDirectory").ToString() + "/sheetStorage/Exam_" + fileName);
}