/// <summary>
/// Remove a sheet if it exists.
/// </summary>
/// <param name="pFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool RemoveWorkSheet(string pFileName, string pSheetName)
{
using (SLDocument doc = new SLDocument(pFileName))
{
var workSheets = doc.GetSheetNames(false);
if (workSheets.Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower()))
{
if (workSheets.Count > 1)
{
var sheet = doc.GetSheetNames().FirstOrDefault((sName) => sName.ToLower() != pSheetName.ToLower());
doc.SelectWorksheet(doc.GetSheetNames().FirstOrDefault((sName) => sName.ToLower() != pSheetName.ToLower()));
}
else if (workSheets.Count == 1)
{
throw new Exception("Can not delete the sole worksheet");
}
doc.DeleteWorksheet(pSheetName);
doc.Save();
return(true);
}
else
{
return(false);
}
}
}
public void LoadFile(string pstrFile, string pstrSheet)
{
if (!string.IsNullOrEmpty(pstrFile) && File.Exists(pstrFile))
{
//Load File
_objFile = new SLDocument(pstrFile);
_blColumnNames = true;
_blSheetInUse = true;
_intCurrentRow = 2;
_intRowStartIndex = _objFile.GetWorksheetStatistics().StartRowIndex;
_intColumnStartIndex = _objFile.GetWorksheetStatistics().StartColumnIndex;
_intColumnRowIndex = 1;
_GetRowCount();
_GetColumnCount();
_GetHeadersNames();
//Load Sheet
if (!string.IsNullOrEmpty(pstrSheet))
{
if (_objFile.GetSheetNames().Contains(pstrSheet))
{
_objFile.SelectWorksheet(pstrSheet);
}
}
}
}
public static void FileOpen(string pstrFile, [Optional] string pstrSheet)
{
if (!string.IsNullOrEmpty(pstrFile))
{
if (File.Exists(pstrFile))
{
_objFile = new SLDocument(pstrFile);
_blColumnNames = true;
_intCurrentRow = 2;
_intRowStartIndex = _objFile.GetWorksheetStatistics().StartRowIndex;
_intColumnStartIndex = _objFile.GetWorksheetStatistics().StartColumnIndex;
_intColumnRowIndex = 1;
_blSheetInUse = true;
_GetRowCount();
_GetColumnCount();
if (!string.IsNullOrEmpty(pstrSheet))
{
if (_objFile.GetSheetNames().Contains(pstrSheet))
{
_objFile.SelectWorksheet(pstrSheet);
}
}
}
}
}
/// <summary>
/// Determine if a sheet exists in the specified excel file
/// </summary>
/// <param name="pFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool SheetExists(string pFileName, string pSheetName)
{
using (SLDocument doc = new SLDocument(pFileName))
{
return(doc.GetSheetNames(false).Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower()));
}
}
/// <summary>
/// Get sheet names in an Excel file
/// </summary>
/// <param name="pFileName"></param>
/// <returns></returns>
public List <string> SheetNames(string pFileName)
{
using (SLDocument doc = new SLDocument(pFileName))
{
return(doc.GetSheetNames(false));
}
}
public static void UseSpreadsheetLight(string excelFilePath, Action <SLDocument, string> action)
{
using (var fs = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var sl = new SLDocument(fs))
{
var sheetNames = sl.GetSheetNames();
foreach (var sheetName in sheetNames)
{
action(sl, sheetName);
}
}
}
public void Open(string filePath)
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader streamReader = new StreamReader(fileStream))
{
slDocument = new SLDocument(fileStream);
}
}
parameter = filePath;
SetSheet(slDocument.GetSheetNames()[0]);
}
// Separate groups mean separate areas to print arrays
private void DivideToGroupsArrayStructure()
{
int group = 0;
foreach (var sheet in doc.GetSheetNames())
{
var list = ArrayStructure
.Where(s => s.RowRange.SheetName.Equals(sheet, StringComparison.OrdinalIgnoreCase))
.OrderBy(o => o.RowRange.RowStart)
.ToList();
if (list.Count == 0)
{
continue;
}
ArrayTemplate previous = null;
foreach (var rng in list)
{
if (previous != null)
{
if (previous.RowRange.RowEnd + 1 != rng.RowRange.RowStart)
{
group++;
}
if (rng.PropertyName.StartsWith(previous.PropertyName + ".") && previous.Group == group)
{
previous.IsHeader = true;
}
}
rng.Group = group;
previous = rng;
}
group++;
}
ArrayStructure = ArrayStructure.OrderBy(o => o.Group).ThenBy(o => o.RowRange.RowStart).ToList();
}
/// <summary>
/// Add a new sheet if it does not currently exists.
/// </summary>
/// <param name="pFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool AddNewSheet(string pFileName, string pSheetName)
{
using (SLDocument doc = new SLDocument(pFileName))
{
if (!(doc.GetSheetNames(false).Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower())))
{
doc.AddWorksheet(pSheetName);
doc.Save();
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// Simple example where the caller will create the Excel file if missing
/// using the method above CreateNewFile. pTextFile is a tab delimited text
/// file known to be validate which is imported into a specific WorkSheet.
///
/// Calling this method:
/// var ops = new SpreadSheetLightLibrary.Examples();
/// var excelFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SpreadSheetLight.xlsx");
/// var textFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "import1.txt");
/// var sheetName = "People";
///
/// if (File.Exists(excelFileName))
/// {
/// File.Delete(excelFileName);
/// }
///
/// ops.CreateNewFile(excelFileName);
/// ops.ImportTabDelimitedTextFile(textFileName, excelFileName, sheetName);
///
/// </summary>
/// <param name="pTextFileName"></param>
/// <param name="pExcelFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool ImportTabDelimitedTextFile(string pTextFileName, string pExcelFileName, string pSheetName)
{
try
{
using (SLDocument doc = new SLDocument(pExcelFileName))
{
var sheets = doc.GetSheetNames(false);
if (sheets.Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower()))
{
doc.SelectWorksheet(pSheetName);
doc.ClearCellContent();
}
else
{
doc.AddWorksheet(pSheetName);
}
var tio = new SLTextImportOptions();
doc.ImportText(pTextFileName, "A1", tio);
// setting column widths
doc.AutoFitColumn("A");
doc.SetColumnWidth(2, 5);
// do not need Sheet1
if (sheets.FirstOrDefault((sheetName) => sheetName.ToLower() == "sheet1") != null)
{
if (pSheetName.ToLower() != "sheet1")
{
doc.DeleteWorksheet("Sheet1");
}
}
doc.Save();
return(true);
}
}
catch (Exception)
{
return(false);
}
}
private doGenerateDocumentResult GenerateACC012_Sheet6(DocumentContext context, SLDocument doc, string sheetname)
{
#region Prepare
doGenerateDocumentResult result = new doGenerateDocumentResult()
{
ErrorFlag = false,
Total = 0,
Complete = 0,
Failed = 0,
ResultDocumentNoList = string.Empty
};
#endregion
#region Constants
const int ROW_HEADER = 1;
const int COL_HEADER_TITLE = 1;
const int ROW_TBLHDR = 3;
int columncount = 0;
int COL_ROWNUMBER = ++columncount;
int COL_FULLNAMEEN = ++columncount;
int COL_BILLINGTARGETCODE = ++columncount;
int COL_CONTRACTCODE = ++columncount;
int COL_BILLINGTYPE = ++columncount;
int COL_INVOICENO = ++columncount;
int COL_ISSUEINVDATE = ++columncount;
int COL_BILLINGSTARTDATE = ++columncount;
int COL_BILLINGENDDATE = ++columncount;
int COL_BILLINGAMOUNTCURRENCY = ++columncount;
int COL_BILLINGAMOUNT = ++columncount;
int COL_VAT = ++columncount;
int COL_TAXINVOICENO = ++columncount;
int COL_TAXINVOICEDATE = ++columncount;
int COL_FIRSTSECURITYSTARTDATE = ++columncount;
#endregion
if (doc.GetSheetNames().Contains(sheetname))
{
doc.DeleteWorksheet(sheetname);
}
doc.AddWorksheet(sheetname);
doc.SelectWorksheet(sheetname);
doc.SetCellStyle(ROW_HEADER, COL_HEADER_TITLE, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 12, Bold = true
}
});
doc.SetCellValue(ROW_HEADER, COL_HEADER_TITLE, "Not Payment List (BillingTypeCode 99)");
doc.SetCellStyle(ROW_TBLHDR, COL_HEADER_TITLE, ROW_TBLHDR, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = true
}
});
doc.SetCellValue(ROW_TBLHDR, COL_ROWNUMBER, "#");
doc.SetCellValue(ROW_TBLHDR, COL_FULLNAMEEN, "FULLNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGTARGETCODE, "BILLINGTARGETCODE");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTCODE, "CONTRACTCODE");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGTYPE, "BILLINGTYPE");
doc.SetCellValue(ROW_TBLHDR, COL_INVOICENO, "INVOICENO");
doc.SetCellValue(ROW_TBLHDR, COL_ISSUEINVDATE, "ISSUEINVDATE");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGAMOUNTCURRENCY, "BILLINGAMOUNTCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGAMOUNT, "BILLINGAMOUNT");
doc.SetCellValue(ROW_TBLHDR, COL_VAT, "VAT");
doc.SetCellValue(ROW_TBLHDR, COL_TAXINVOICENO, "TAXINVOICENO");
doc.SetCellValue(ROW_TBLHDR, COL_TAXINVOICEDATE, "TAXINVOICEDATE");
doc.SetCellValue(ROW_TBLHDR, COL_FIRSTSECURITYSTARTDATE, "FIRSTSECURITYSTARTDATE");
var reportdata = this.GetAccountDataOfACC012_Sheet6(context.UserHQCode);
if (reportdata.Count == 0)
{
result.ErrorFlag = true;
result.ErrorCode = MessageUtil.MessageList.MSG8005;
return(result);
}
result.Complete = reportdata.Count;
result.Total = reportdata.Count;
int rowindex = ROW_TBLHDR + 1;
doc.SetCellStyle(rowindex, COL_HEADER_TITLE, rowindex + reportdata.Count - 1, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = false
}
});
foreach (var rowdata in reportdata)
{
if (rowdata.RowNumber != null)
{
doc.SetCellValue(rowindex, COL_ROWNUMBER, rowdata.RowNumber.Value);
}
doc.SetCellValue(rowindex, COL_FULLNAMEEN, rowdata.FullNameEN);
doc.SetCellValue(rowindex, COL_BILLINGTARGETCODE, rowdata.BillingTargetCode);
doc.SetCellValue(rowindex, COL_CONTRACTCODE, rowdata.ContractCode);
doc.SetCellValue(rowindex, COL_BILLINGTYPE, rowdata.BillingType);
doc.SetCellValue(rowindex, COL_INVOICENO, rowdata.InvoiceNo);
if (rowdata.IssueInvDate != null)
{
doc.SetCellValue(rowindex, COL_ISSUEINVDATE, rowdata.IssueInvDate.Value.ToString("dd-MMM-yyyy"));
}
if (rowdata.BillingAmountCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_BILLINGAMOUNTCURRENCY, rowdata.BillingAmountCurrencyType);
}
if (rowdata.BillingAmount != null)
{
doc.SetCellValue(rowindex, COL_BILLINGAMOUNT, rowdata.BillingAmount.Value);
}
if (rowdata.Vat != null)
{
doc.SetCellValue(rowindex, COL_VAT, rowdata.Vat.Value);
}
doc.SetCellValue(rowindex, COL_TAXINVOICENO, rowdata.TaxInvoiceNo);
if (rowdata.TaxInvoiceDate != null)
{
doc.SetCellValue(rowindex, COL_TAXINVOICEDATE, rowdata.TaxInvoiceDate.Value.ToString("dd-MMM-yyyy"));
}
if (rowdata.FirstSecurityStartDate != null)
{
doc.SetCellValue(rowindex, COL_FIRSTSECURITYSTARTDATE, rowdata.FirstSecurityStartDate.Value.ToString("dd-MMM-yyyy"));
}
rowindex++;
}
return(result);
}
public List <string> GetSheetNames()
{
return(slDocument.GetSheetNames());
}
private void SubProcess(IReportModel Data)
{
_reportModelData = Data;
//1) Go through each sheet
foreach (var sheetName in Document.GetSheetNames())
{
CurrentSheet = sheetName;
Document.SelectWorksheet(sheetName);
if (HasReportDefinition())
{
this.RowsToRemove.Clear();
this.ColsToRemove.Clear();
this.ColsInseted.Clear();
this.RowsInserted.Clear();
this.ColsShifted.Clear();
this.RowsShifted.Clear();
this.ReportPictures.Clear();
this.ReportCharts.Clear();
this.PageBreaks.Clear();
this.CurrentColumn = 1;
this.CurrentRow = 1;
CurrentSheet = sheetName;
//2) Set template bounds from definition
CurrentReportBounds = null;
CurrentReportBounds = GetReportTemplateBounds();
//3)Remove report definition tag from the output after reading the information
AddRowsToRemove(1);
//Go throught the entire report section
//4) Now call EnumerateReport and Go through each cell in template
EnumerateReport();
//5) Get cell data replace dynamic data with values from ReportDataModel class
//foreach (var item in this.RowsInserted)
//{
// //Picture, Charts, And Pagebreaks defined next will have their position autocorrected since they have not been parsed yet. Previously defined objects will have no effect since row is added beneath. Only charts data need to be corrected
// // AdjustChartDataPostionOnRowInserted(item.InsertedAt, item.InsertedCount);
//}
//Number of rows that have been removed will reduce the next row number
int rowsRemoveCount = 0;
RowsToRemove.Sort();
foreach (int row in RowsToRemove)
{
////////////////////////////////////////////////////////////////////// - Removing row and setting height
List <PairedValues <int, double> > rowsandHeights = new List <PairedValues <int, double> >();
//Get all next rows height, delete the row which needs to be deleted, set replacing row height to it's actual height
for (int i = row; i <= CurrentReportBounds.Bottom; i++)
{
//get next row height and set it at what index it will be after row is removed
double nextrowheight = Document.GetRowHeight(i - rowsRemoveCount + 1);
//Note we are not adding +1 to row index as row index after removal of previous row will be one less then it's current index
rowsandHeights.Add(new PairedValues <int, double>(i - rowsRemoveCount, nextrowheight));
}
//Delete One Row
Document.DeleteRow(row - rowsRemoveCount, 1);
//Now all next rows have got their index one less then their previous index so it will now match the indexes as saved in list of rowsandheight
foreach (var rowsandHeight in rowsandHeights)
{
Document.SetRowHeight(rowsandHeight.ValueA, rowsandHeight.ValueB);
}
/////////////////////////////////////////////////////////////////////////
//After row removal, report bound will reduce by one
this.CurrentReportBounds.Bottom -= 1;
AdjustPicturePositionOnRowRemoved(row - rowsRemoveCount, 1);
AdjustChartPositionOnRowRemoved(row - rowsRemoveCount, 1);
AdjustPageBreaksOnRowsRemoved(row - rowsRemoveCount, 1);
//Change list of rows inserted when a row above then is removed
AdjustRowInsertedListOnRowRemoval((row - rowsRemoveCount), 1);
rowsRemoveCount++;
}
foreach (var item in this.ColsInseted)
{
//This will effect all previously defined objects since pagebreaks and pics defined previously need to be repostioned as the were already parsed. Also charts data position which will be defined next will have to be checked and corrected.
//AdjustChartPositionOnColumnInserted(item.InsertedAt, item.InsertedCount);
AdjustPicturePositionOnColInsert(item.InsertedAt, item.InsertedCount);
AdjustPageBreakOnColumnAdded(item.InsertedAt, item.InsertedCount);
}
int colsRemoveCount = 0;
ColsToRemove.Sort();
foreach (int col in ColsToRemove)
{
//////////////////////////////////////////////////////////////////////////////////
List <PairedValues <int, double> > colsandWidths = new List <PairedValues <int, double> >();
//Get next cols width, remove the col that needs to be removed, set replacing columns width
for (int i = col; i <= CurrentReportBounds.Right; i++)
{
double nextcolwidth = Document.GetColumnWidth(i - colsRemoveCount + 1);
colsandWidths.Add(new PairedValues <int, double>(i - colsRemoveCount, nextcolwidth));
}
///Remove one column
Document.DeleteColumn(col - colsRemoveCount, 1);
///Each column next to the one removed should have their width reset to their previous width
foreach (var colsandWidth in colsandWidths)
{
Document.SetColumnWidth(colsandWidth.ValueA, colsandWidth.ValueB);
}
////////////////////////////////////////////////////////////////////////////////////
this.CurrentReportBounds.Right -= 1;
AdjustPicturePositionOnColumnRemoved(col - colsRemoveCount, 1);
AdjustChartPositionOnColumnRemoved(col - colsRemoveCount, 1);
AdjustPageBreaksOnColumnRemoved(col - colsRemoveCount, 1);
AdjustColInsertedListOnColRemoval(col - colsRemoveCount, 1);
colsRemoveCount++;
}
PutAllPicturesOnReport();
PutAllChartsOnReport();
PutAllPageBreaksOnReport();
}
}
}
private doGenerateDocumentResult GenerateACC010_Sheet1(DocumentContext context, SLDocument doc, string sheetname)
{
#region Prepare
doGenerateDocumentResult result = new doGenerateDocumentResult()
{
ErrorFlag = false,
Total = 0,
Complete = 0,
Failed = 0,
ResultDocumentNoList = string.Empty
};
#endregion
#region Constants
const int ROW_HEADER = 1;
const int COL_HEADER_TITLE = 1;
const int ROW_TBLHDR = 3;
int columncount = 0;
int COL_ROWNUMBER = ++columncount;
int COL_CUSTFULLNAMEEN = ++columncount;
int COL_CONTRACTCODE = ++columncount;
int COL_SITENAMEEN = ++columncount;
int COL_PLANCODE = ++columncount;
int COL_OFFICENAMEEN = ++columncount;
int COL_CUSTACCEPTANCEDATE = ++columncount;
int COL_PRODUCTNAMEEN = ++columncount;
int COL_SALESMANNAME = ++columncount;
int COL_ORDERPRODUCTPRICECURRENCY = ++columncount;
int COL_ORDERPRODUCTPRICE = ++columncount;
int COL_INSTRUMENTCOSTCURRENCY = ++columncount;
int COL_INSTRUMENTCOST = ++columncount;
int COL_ORDERINSTALLFEECURRENCY = ++columncount;
int COL_ORDERINSTALLFEE = ++columncount;
int COL_PAYTOSUBCONTRACTORCURRENCY = ++columncount;
int COL_PAYTOSUBCONTRACTOR = ++columncount;
#endregion
if (doc.GetSheetNames().Contains(sheetname))
{
doc.DeleteWorksheet(sheetname);
}
doc.AddWorksheet(sheetname);
doc.SelectWorksheet(sheetname);
doc.SetCellStyle(ROW_HEADER, COL_HEADER_TITLE, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 12, Bold = true
}
});
doc.SetCellValue(ROW_HEADER, COL_HEADER_TITLE, "New operation sales report");
doc.SetCellStyle(ROW_TBLHDR, COL_HEADER_TITLE, ROW_TBLHDR, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = true
}
});
doc.SetCellValue(ROW_TBLHDR, COL_ROWNUMBER, "NO.");
doc.SetCellValue(ROW_TBLHDR, COL_CUSTFULLNAMEEN, "CUSTFULLNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTCODE, "CONTRACTNO.");
doc.SetCellValue(ROW_TBLHDR, COL_SITENAMEEN, "PREMISE'SNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_PLANCODE, "PLANCODE");
doc.SetCellValue(ROW_TBLHDR, COL_OFFICENAMEEN, "OFFICENAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_CUSTACCEPTANCEDATE, "CUSTACCEPTANCEDATE");
doc.SetCellValue(ROW_TBLHDR, COL_PRODUCTNAMEEN, "PRODUCTNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_SALESMANNAME, "SALESMANNAME");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERPRODUCTPRICECURRENCY, "ORDERPRODUCTPRICECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERPRODUCTPRICE, "ORDERPRODUCTPRICE");
doc.SetCellValue(ROW_TBLHDR, COL_INSTRUMENTCOSTCURRENCY, "INSTRUMENTCOSTCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_INSTRUMENTCOST, "INSTRUMENTCOST");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEECURRENCY, "ORDERINSTALLFEECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEE, "ORDERINSTALLFEE");
doc.SetCellValue(ROW_TBLHDR, COL_PAYTOSUBCONTRACTORCURRENCY, "PAYTOSUBCONTRACTORCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_PAYTOSUBCONTRACTOR, "PAYTOSUBCONTRACTOR");
var accountData = this.GetAccountDataOfACC010(context.TargetPeriodFrom, context.TargetPeriodTo, context.UserHQCode);
if (accountData.Count == 0)
{
result.ErrorFlag = true;
result.ErrorCode = MessageUtil.MessageList.MSG8005;
return(result);
}
result.Complete = accountData.Count;
result.Total = accountData.Count;
int rowindex = ROW_TBLHDR + 1;
doc.SetCellStyle(rowindex, COL_HEADER_TITLE, rowindex + accountData.Count - 1, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = false
}
});
foreach (var rowdata in accountData)
{
if (rowdata.RowNumber != null)
{
doc.SetCellValue(rowindex, COL_ROWNUMBER, rowdata.RowNumber.Value);
}
doc.SetCellValue(rowindex, COL_CUSTFULLNAMEEN, rowdata.CustFullNameEN);
doc.SetCellValue(rowindex, COL_CONTRACTCODE, rowdata.ContractCode);
doc.SetCellValue(rowindex, COL_SITENAMEEN, rowdata.SiteNameEN);
doc.SetCellValue(rowindex, COL_PLANCODE, rowdata.PlanCode);
doc.SetCellValue(rowindex, COL_OFFICENAMEEN, rowdata.OfficeNameEN);
if (rowdata.CustAcceptanceDate != null)
{
doc.SetCellValue(rowindex, COL_CUSTACCEPTANCEDATE, rowdata.CustAcceptanceDate.Value.ToString("dd-MMM-yyyy"));
}
doc.SetCellValue(rowindex, COL_PRODUCTNAMEEN, rowdata.ProductNameEN);
doc.SetCellValue(rowindex, COL_SALESMANNAME, rowdata.SalesmanName);
if (rowdata.OrderProductPriceCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERPRODUCTPRICECURRENCY, rowdata.OrderProductPriceCurrencyType);
}
if (rowdata.OrderProductPrice != null)
{
doc.SetCellValue(rowindex, COL_ORDERPRODUCTPRICE, rowdata.OrderProductPrice.Value);
}
if (rowdata.InstrumentCostCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_INSTRUMENTCOSTCURRENCY, rowdata.InstrumentCostCurrencyType);
}
if (rowdata.InstrumentCost != null)
{
doc.SetCellValue(rowindex, COL_INSTRUMENTCOST, rowdata.InstrumentCost.Value);
}
if (rowdata.OrderInstallFee != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEE, rowdata.OrderInstallFee.Value);
}
if (rowdata.OrderInstallFeeCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEECURRENCY, rowdata.OrderInstallFeeCurrencyType);
}
if (rowdata.PayToSubcontractor != null)
{
doc.SetCellValue(rowindex, COL_PAYTOSUBCONTRACTOR, rowdata.PayToSubcontractor.Value);
}
rowindex++;
}
doc.Filter(ROW_TBLHDR, COL_ROWNUMBER, rowindex - 1, COL_PAYTOSUBCONTRACTOR);
doc.SetCellValue(SLConvert.ToCellReference(rowindex, COL_ORDERPRODUCTPRICE), string.Format("=SUBTOTAL({0})", SLConvert.ToCellRange(ROW_TBLHDR + 1, COL_ORDERPRODUCTPRICE, rowindex - 1, COL_ORDERPRODUCTPRICE)));
return(result);
}
// For each worksheet
// Find the first non-empty cell in Column D
// Calculate the top right diagonal column
// For each row/column combination: store the mapping info in a list
// Finally dump all that in the output file
public void Process(string dpmIdExcelInputFile, string outputMappingTextFile)
{
Regex hasOnlyDigits = new Regex(@"^\$DPM_ID_\[0-9]");
if (!File.Exists(dpmIdExcelInputFile))
{
throw new ApplicationException("Input Excel file does not exist!");
}
using (SLDocument inputExcel = new SLDocument(dpmIdExcelInputFile))
{
foreach (string sheetName in inputExcel.GetSheetNames(true))
{
string tempSheetName = sheetName.ToLower();
if (tempSheetName[0] != 'c' && tempSheetName[0] != 'f' && tempSheetName[0] != 'p')
{
continue;
}
const int columnIndexOfFirstColumnIdentifer = 4;
const int rowIdentifierColumn = 3;
int rowIndexOfFirstRowIdentifer = 1;
int emptyRowIdentifierCount = 0;
if (inputExcel.SelectWorksheet(sheetName))
{
string rowIdentifier = string.Empty;
string columnIdentifier = string.Empty;
int rowIndex = 1;
while (string.IsNullOrEmpty(rowIdentifier))
{
rowIdentifier = inputExcel.GetCellValueAsString(rowIndex, rowIdentifierColumn);
rowIndex++;
}
rowIndex = rowIndex - 1;
rowIndexOfFirstRowIdentifer = rowIndex;
//while (!string.IsNullOrEmpty(rowIdentifier))
while (emptyRowIdentifierCount < 25)
{
if (string.IsNullOrEmpty(rowIdentifier))
{
emptyRowIdentifierCount++;
}
else
{
emptyRowIdentifierCount = 0;
}
int columnIndex = columnIndexOfFirstColumnIdentifer;
int j = 1;
while (string.IsNullOrEmpty(columnIdentifier) && (rowIndexOfFirstRowIdentifer - j) > 0)
{
columnIdentifier = inputExcel.GetCellValueAsString((rowIndexOfFirstRowIdentifer - j), columnIndex);
j++;
}
j--;
while (!string.IsNullOrEmpty(columnIdentifier))
{
string dpmId = GetDPMID(inputExcel.GetCellValueAsString(rowIndex, columnIndex));
Match match = hasOnlyDigits.Match(dpmId);
if (!string.IsNullOrEmpty(dpmId))
{
string cellReferencePrefix = string.Empty;
string adjustedSheetName = sheetName;
if (sheetName.Contains("(") && sheetName.Contains(")"))
{
adjustedSheetName = sheetName.Split('(')[0];
cellReferencePrefix = ",s" + sheetName.Split('(')[1].Replace(")", "");
}
// {C 05.01, r211, c060}
string cellReference = "{" + adjustedSheetName + ",r" + rowIdentifier + ",c" + columnIdentifier + cellReferencePrefix + "}";
cellReference = cellReference.Replace(" ", string.Empty).ToLower();
dpmIdMappingEntry.Add(new DpmIdMappingEntry()
{
CellReference = cellReference,
DpmVariableName = "$DPM_ID_" + dpmId
});
}
columnIndex++;
columnIdentifier = inputExcel.GetCellValueAsString((rowIndexOfFirstRowIdentifer - j), columnIndex);
}
rowIndex++;
rowIdentifier = inputExcel.GetCellValueAsString(rowIndex, rowIdentifierColumn);
}
}
}
}
if (dpmIdMappingEntry.Count > 0)
{
using (StreamWriter sWriter = new StreamWriter(outputMappingTextFile))
{
foreach (DpmIdMappingEntry entry in dpmIdMappingEntry)
{
sWriter.WriteLine(entry.CellReference + "=" + entry.DpmVariableName);
string adjustedCellReference = entry.CellReference
.Replace("{c07.01.a", "{c07.01")
.Replace("{c07.01.b", "{c07.01")
.Replace("{c07.01.c", "{c07.01")
.Replace("{c08.01.a", "{c08.01")
.Replace("{c08.01.b", "{c08.01")
;
if (!adjustedCellReference.Equals(entry.CellReference))
{
sWriter.WriteLine(adjustedCellReference + "=" + entry.DpmVariableName);
}
}
}
}
GeneratePureMappings();
}
private doGenerateDocumentResult GenerateACC009_Sheet1(DocumentContext context, SLDocument doc, string sheetname)
{
#region Prepare
doGenerateDocumentResult result = new doGenerateDocumentResult()
{
ErrorFlag = false,
Total = 0,
Complete = 0,
Failed = 0,
ResultDocumentNoList = string.Empty
};
#endregion
#region Constants
const int ROW_HEADER = 1;
const int COL_HEADER_TITLE = 1;
const int ROW_TBLHDR = 3;
int columncount = 0;
int COL_ROWNUMBER = ++columncount;
int COL_BILLINGTARGETNAMEEN = ++columncount;
int COL_SITENAMEEN = ++columncount;
int COL_CONTRACTCODE = ++columncount;
int COL_BILLINGTARGETCODE = ++columncount;
int COL_PLANCODE = ++columncount;
int COL_ORDERDEPOSITFEECURRENCY = ++columncount;
int COL_ORDERDEPOSITFEE = ++columncount;
int COL_ORDERINSTALLFEECURRENCY = ++columncount;
int COL_ORDERINSTALLFEE = ++columncount;
int COL_INSTALLFEEAMOUNTCURRENCY = ++columncount;
int COL_INSTALLFEEAMOUNT = ++columncount;
int COL_INSTALLFEEINVOICENO = ++columncount;
int COL_ORDERCONTRACTFEECURRENCY = ++columncount;
int COL_ORDERCONTRACTFEE = ++columncount;
int COL_CONTRACTFEEAMOUNTCURRENCY = ++columncount;
int COL_CONTRACTFEEAMOUNT = ++columncount;
int COL_INVOICENO = ++columncount;
int COL_BILLINGCYCLE = ++columncount;
int COL_OFFICENAMEEN = ++columncount;
int COL_FIRSTSECURITYSTARTDATE = ++columncount;
int COL_PRODUCTNAMEEN = ++columncount;
int COL_SALESMANNAME = ++columncount;
#endregion
if (doc.GetSheetNames().Contains(sheetname))
{
doc.DeleteWorksheet(sheetname);
}
doc.AddWorksheet(sheetname);
doc.SelectWorksheet(sheetname);
doc.SetCellStyle(ROW_HEADER, COL_HEADER_TITLE, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 12, Bold = true
}
});
doc.SetCellValue(ROW_HEADER, COL_HEADER_TITLE, "New operation rental report");
doc.SetCellStyle(ROW_TBLHDR, COL_HEADER_TITLE, ROW_TBLHDR, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = true
}
});
doc.SetCellValue(ROW_TBLHDR, COL_ROWNUMBER, "NO.");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGTARGETNAMEEN, "BILLINGCUSTOMERNAME");
doc.SetCellValue(ROW_TBLHDR, COL_SITENAMEEN, "PREMISE'SNAME");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTCODE, "CONTRACTNO.");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGTARGETCODE, "BILLINGCUSTOMERCODE");
doc.SetCellValue(ROW_TBLHDR, COL_PLANCODE, "PLANCODE");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERDEPOSITFEECURRENCY, "ORDERDEPOSITFEECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERDEPOSITFEE, "ORDERDEPOSITFEE");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEECURRENCY, "ORDERINSTALLFEECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEE, "ORDERINSTALLFEE");
doc.SetCellValue(ROW_TBLHDR, COL_INSTALLFEEAMOUNTCURRENCY, "INSTALLFEEAMOUNTCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_INSTALLFEEAMOUNT, "INSTALLFEEAMOUNT");
doc.SetCellValue(ROW_TBLHDR, COL_INSTALLFEEINVOICENO, "INSTALLFEEINVOICENO");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERCONTRACTFEECURRENCY, "ORDERCONTRACTFEECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERCONTRACTFEE, "ORDERCONTRACTFEE");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTFEEAMOUNTCURRENCY, "CONTRACTFEEAMOUNTCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTFEEAMOUNT, "CONTRACTFEEAMOUNT");
doc.SetCellValue(ROW_TBLHDR, COL_INVOICENO, "INVOICENO");
doc.SetCellValue(ROW_TBLHDR, COL_BILLINGCYCLE, "BILLINGCYCLE");
doc.SetCellValue(ROW_TBLHDR, COL_OFFICENAMEEN, "OFFICENAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_FIRSTSECURITYSTARTDATE, "FIRSTSECURITYSTARTDATE");
doc.SetCellValue(ROW_TBLHDR, COL_PRODUCTNAMEEN, "PRODUCTNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_SALESMANNAME, "SALESMANNAME");
List <dtACC009> accountData = this.GetAccountDataOfACC009(context.TargetPeriodFrom, context.TargetPeriodTo, context.UserHQCode);
if (accountData.Count == 0)
{
result.ErrorFlag = true;
result.ErrorCode = MessageUtil.MessageList.MSG8005;
return(result);
}
result.Complete = accountData.Count;
result.Total = accountData.Count;
int rowindex = ROW_TBLHDR + 1;
doc.SetCellStyle(rowindex, COL_HEADER_TITLE, rowindex + accountData.Count - 1, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = false
}
});
foreach (var rowdata in accountData)
{
if (rowdata.No != null)
{
doc.SetCellValue(rowindex, COL_ROWNUMBER, rowdata.No.Value);
}
doc.SetCellValue(rowindex, COL_BILLINGTARGETNAMEEN, rowdata.BillingTargetNameEN);
doc.SetCellValue(rowindex, COL_SITENAMEEN, rowdata.SiteNameEN);
doc.SetCellValue(rowindex, COL_CONTRACTCODE, rowdata.ContractCode);
doc.SetCellValue(rowindex, COL_BILLINGTARGETCODE, rowdata.BillingTargetCode);
doc.SetCellValue(rowindex, COL_PLANCODE, rowdata.PlanCode);
if (rowdata.OrderDepositFeeCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERCONTRACTFEECURRENCY, this.CurrencyName(rowdata.OrderContractFeeCurrencyType));
}
if (rowdata.OrderDepositFee != null)
{
doc.SetCellValue(rowindex, COL_ORDERDEPOSITFEE, rowdata.OrderDepositFee.Value);
}
if (rowdata.OrderInstallFeeCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEECURRENCY, this.CurrencyName(rowdata.OrderInstallFeeCurrencyType));
}
if (rowdata.OrderInstallFee != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEE, rowdata.OrderInstallFee.Value);
}
if (rowdata.InstallFeeAmountCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_INSTALLFEEAMOUNTCURRENCY, this.CurrencyName(rowdata.InstallFeeAmountCurrencyType));
}
if (rowdata.InstallFeeAmount != null)
{
doc.SetCellValue(rowindex, COL_INSTALLFEEAMOUNT, rowdata.InstallFeeAmount.Value);
}
doc.SetCellValue(rowindex, COL_INSTALLFEEINVOICENO, rowdata.InstallFeeInvoiceNo);
if (rowdata.OrderContractFeeCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERCONTRACTFEECURRENCY, this.CurrencyName(rowdata.OrderContractFeeCurrencyType));
}
if (rowdata.OrderContractFee != null)
{
doc.SetCellValue(rowindex, COL_ORDERCONTRACTFEE, rowdata.OrderContractFee.Value);
}
if (rowdata.ContractFeeAmountCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_CONTRACTFEEAMOUNTCURRENCY, this.CurrencyName(rowdata.ContractFeeAmountCurrencyType));
}
if (rowdata.ContractFeeAmount != null)
{
doc.SetCellValue(rowindex, COL_CONTRACTFEEAMOUNT, rowdata.ContractFeeAmount.Value);
}
doc.SetCellValue(rowindex, COL_INVOICENO, rowdata.InvoiceNo);
if (rowdata.BillingCycle != null)
{
doc.SetCellValue(rowindex, COL_BILLINGCYCLE, rowdata.BillingCycle.Value);
}
doc.SetCellValue(rowindex, COL_OFFICENAMEEN, rowdata.OfficeNameEN);
if (rowdata.FirstSecurityStartDate != null)
{
doc.SetCellValue(rowindex, COL_FIRSTSECURITYSTARTDATE, rowdata.FirstSecurityStartDate.Value);
}
doc.SetCellValue(rowindex, COL_PRODUCTNAMEEN, rowdata.ProductNameEN);
doc.SetCellValue(rowindex, COL_SALESMANNAME, rowdata.SalesmanName);
rowindex++;
}
result.Total += accountData.Count;
return(result);
}
public static List <string> GetSheetNames(string fileName)
{
using (var sl = new SLDocument(fileName))
return(sl.GetSheetNames());
}