public static ExcelFile CreateExcelFile()
{
ExcelFile ex = new ExcelFile();
ex.LimitNear += delegate(object sender_x, LimitEventArgs e_x) { e_x.WriteWarningWorksheet = false; };
return ex;
}
private void ParseExcelSheets(string filename, ExcelFile report, DateTime reportDate)
{
foreach (ExcelWorksheet sheet in report.Worksheets)
{
Console.WriteLine(filename);
var supermarketName = sheet.Rows[1].AllocatedCells[1].Value.ToString();
supermarketName = this.ReplaceSpecialCharacters(supermarketName);
using (TransactionScope tran = new TransactionScope())
{
var checkIfSupermarketExists =
SqlMarketContext.Supermarkets.FirstOrDefault(p => p.Name == supermarketName);
if (checkIfSupermarketExists == null)
{
this.SqlMarketContext.Supermarkets.Add(new Supermarket() { Name = supermarketName });
this.SqlMarketContext.SaveChanges();
}
tran.Complete();
}
var supermarket =
this.SqlMarketContext.Supermarkets.FirstOrDefault(s => s.Name == supermarketName);
if (supermarket != null)
{
var productsImported = this.ParseRowsData(reportDate, sheet, supermarket.Id);
Console.WriteLine("{0}/{1} sales imported.\n", productsImported, sheet.Rows.Count - 4);
}
}
}
private void m_B_Export_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists(this.m_TaskCsvName))
{
if (MessageBox.Show("文件已经存在，是否覆盖?", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.Cancel)
{
return;
}
}
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Mrg");
try
{
DataGridViewConverter.ImportFromDataGridView(ws, this.m_dgv_ItemDetail, new ImportFromDataGridViewOptions() { ColumnHeaders = true });
ef.Save(this.m_TaskCsvName, CsvSaveOptions.CsvDefault);
Process.Start("Excel.exe", this.m_TaskCsvName);
}
catch (System.Exception ex)
{
MessageBox.Show("文件保存失败 : " + ex.Message, "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
}
}
public void Export(string fileName, DateTime dateFrom, DateTime dateTo, IEnumerable<TaskGroup> groups)
{
SpreadsheetInfo.SetLicense("E5M8-KYCM-HFC2-WRTR");
if (File.Exists(fileName))
File.Delete(fileName);
if (groups == null)
throw new ApplicationException("no data");
var workbook = new ExcelFile();
// Create new worksheet and set cell A1 value to 'Hello world!'.
ExcelWorksheet ws = workbook.Worksheets.Add("TimeAnalytic");
int row = 0; //numeration starts from 0
WriteDate(ws, 1, "Date From", dateFrom);
WriteDate(ws, 2, "Date To", dateTo);
row = 4;
WriteGroupHeader(ws, row);
row++;
TaskGroup summary = groups.FirstOrDefault(x => x.IsSummary);
WriteGroupHeader(ws, row);
row = WriteGroups(ws, groups, summary, ref row);
row++;
row++;
WriteTasksHeader(ws, row);
WriteTasksForGroups(ws, groups, summary, ref row);
workbook.Save(fileName);
}
private static void AddExcelStyle(ExcelFile excelFile)
{
excelFile.GetSheet("User")
.addAlternativeBackgroundColor(Color.Aquamarine, Color.Aqua)
.addHeaderBackgroundColor(Color.Red);
excelFile.GetSheet("Product").addBackgroundColor(Color.Red);
}
private static ExcelFile CreateExcelFile()
{
var excelFile = new ExcelFile();
AddExcelData(excelFile);
AddExcelStyle(excelFile);
return excelFile;
}
protected static ExcelFile Print(IEnumerable<Table> tables)
{
var ef = new ExcelFile();
foreach (var table in tables)
{
var worksheetName = table.Name;
// Work sheet name can't be over 31 characters.
if (worksheetName.Length > 31)
{
worksheetName = worksheetName.Substring(0, 28) + "...";
}
var ws = ef.Worksheets.Add(worksheetName);
if (table.Head != null)
{
var rowsHeight = CalculateRowsHeight(table.Head.Rows, table.Body.Rows);
var columnsWidth = CalculateColumnsWidth(table.Head.Rows, table.Body.Rows);
for (var index = 0; index < columnsWidth.Length; index++)
{
ws.Columns[index].Width = columnsWidth[index] * 256;
}
for (var index = 0; index < rowsHeight.Length; index++)
{
ws.Rows[index].Height = rowsHeight[index] * 20;
}
PrintSection(ref ws, table.Head.Rows);
PrintSection(ref ws, table.Body.Rows, table.Head.Rows.Length);
}
else
{
var rowsHeight = CalculateRowsHeight(table.Body.Rows);
var columnsWidth = CalculateColumnsWidth(table.Body.Rows);
for (var index = 0; index < columnsWidth.Length; index++)
{
ws.Columns[index].Width = columnsWidth[index] * 256;
}
for (var index = 0; index < rowsHeight.Length; index++)
{
ws.Rows[index].Height = rowsHeight[index] * 20;
}
PrintSection(ref ws, table.Body.Rows);
}
foreach (var image in table.Images)
{
ws.Pictures.Add(image.ImagePath, new Rectangle(image.Left, image.Top, image.Width, image.Height));
}
}
return ef;
}
public static string GenerateFinancialReport(IEnumerable<FinancialReport> reportData, string folderPath)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("FinancialReport");
CreateHeader(ws);
var row = 1;
foreach (var financialReport in reportData)
{
var vendor = financialReport.Vendor;
var incomes = financialReport.Incomes;
var expenses = financialReport.Expenses;
var totalTaxes = financialReport.TotalTaxes;
var financialResult = incomes - expenses - totalTaxes;
FillRowData(ws, row, vendor, incomes, expenses, totalTaxes, financialResult);
row++;
}
ApplyColumnsAutoWidth(ws);
ef.Save(folderPath + Filename);
return Path.GetFullPath(folderPath + Filename);
}
public TablesControl()
{
InitializeComponent();
SourceDocument = new ExcelFile();
Initialize();
}
public TablesControl(String path)
{
InitializeComponent();
SourceDocument = new ExcelFile();
OpenFromFile(path);
Initialize();
}
/// <summary>
/// Initializes a new instance of class ExcelDocument
/// </summary>
public ExcelDocument()
{
var key = new Key();
SpreadsheetInfo.SetLicense(key.ExcelKey37);
emdCache = new List<EntityMetadata>();
innerWorkBook = new ExcelFile();
lineNumber = 0;
summaryLineNumber = 0;
}
public ExcelToXLIFF(String excelPath, String ouputPath)
{
try
{
excel = new ExcelFile(excelPath);
xBuilder = new XLIFFBuilder("en-US", "en-GB");
this.outputPath = ouputPath;
}
catch (Exception ex)
{
throw ex;
}
}
protected static void LoadDataInToWorkSheet(MemoryStream stream, ExcelFile sheet, DataFileFormat fileFormat)
{
if (fileFormat == DataFileFormat.excel)
{
sheet.LoadXls(stream);
stream.Close();
return;
}
File.WriteAllBytes(@"C:\Test.csv", stream.ToArray());
sheet.LoadCsv(stream, CsvType.CommaDelimited);
stream.Close();
}
public void FillTable_WithHeaders(int rowCount, Type[] colTypes, string[] colNames)
{
BasicFrame frame = BasicFrameGenerator.CreateTable(rowCount, colTypes, colNames);
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet ws = excelFile.Worksheets.Add("test");
excelFile.Worksheets.ActiveWorksheet = ws;
BasicFrameToExcelWriter writer = new BasicFrameToExcelWriter();
writer.ExportHeaders = true;
writer.FillTable(ws, frame);
Assert.Equal(frame.RowCount+1, ws.Rows.Count);
// Assert.Equal(frame.ColumnCount, ws.Columns.Count);
}
private static void AddExcelData(ExcelFile excelFile)
{
var userCollection = new List<User>()
{
new User("Rodrigo", "*****@*****.**"),
new User("Jéssica", "*****@*****.**"),
new User("Jéssica", "*****@*****.**")
};
excelFile.AddSheet<User>("User", userCollection);
var productCollection = new List<Product>()
{
new Product("Bread", 10.5d),
new Product("Cheese", 3.2d)
};
excelFile.AddSheet<Product>("Product", productCollection);
}
public void SetHeaders(int id, string[] colNames)
{
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet ws = excelFile.Worksheets.Add("test");
excelFile.Worksheets.ActiveWorksheet = ws;
BasicFrameToExcelWriter writer = new BasicFrameToExcelWriter();
writer.ExportHeaders = true;
writer.SetHeaders(ws, colNames);
Assert.Equal(1, ws.Rows.Count);
for (int i = 0; i < colNames.Length; i++)
{
Assert.Equal(colNames[i], ws.Cells[0, i].Value);
}
// Assert.Equal(frame.ColumnCount, ws.Columns.Count);
}
public void Export(Models.Student student)
{
try {
string desktop = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + Path.DirectorySeparatorChar;
var excelFile = new ExcelFile();
excelFile.LoadXlsx("chart_template.xlsx", XlsxOptions.PreserveKeepOpen);
var worksheet = excelFile.Worksheets[0];
SetInitialLabels(student, worksheet);
SetTimeLabels(student, worksheet);
SetWeekData(student, worksheet);
SetMonthData(student, worksheet);
SetHourData(student, worksheet);
excelFile.SaveXlsx(desktop + student.FirstName + " " + student.LastName + ".xlsx");
}
catch {
throw new SpreadsheetExportException();
}
}
public DateReport(string financeType, DateTime date, DataTable dt)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
SpreadsheetInfo.FreeLimitReached +=
(sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;
output = new ExcelOutput
("Report_" + financeType + "_" +
date.ToString("yyyyMMdd") + "_" + ".xls");
this.workbook = new ExcelFile();
this.worksheet = workbook.Worksheets.Add("Report");
this.date = date;
this.dt = dt;
this.financeType = financeType;
row = 0;
worksheet.Columns[3].Width = 35 * 256;
worksheet.Columns[1].Width = 15 * 256;
worksheet.Columns[2].Width = 12 * 256;
worksheet.Columns[1].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;
}
public MonthlyReport(string financeType, int month, int year, DataTable dt)
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
SpreadsheetInfo.FreeLimitReached +=
(sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;
output = new ExcelOutput
("Report_" + financeType + "_" + year + "_" + month + ".xls");
this.workbook = new ExcelFile();
this.worksheet = workbook.Worksheets.Add("Report");
this.month = month;
this.year = year;
this.dt = dt;
this.financeType = financeType;
row = 0;
worksheet.Columns[3].Width = 35 * 256;
worksheet.Columns[1].Width = 15 * 256;
worksheet.Columns[2].Width = 12 * 256;
worksheet.Columns[1].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;
}
public void When_GettingPaymentReportForKnownPaymentId_DataIsReturned_AndCanBeWrittenInValidFormat(PaymentType paymentType, DataFileFormat fileFormat)
{
var clientConfiguration = GetDefaultDataClientConfiguration()
.With((clientConfig) => clientConfig.WireDataFormat = WireDataFormat.Other);
var client = new JustGivingDataClient(clientConfiguration);
CreatePaymentsClient(client);
var payment = PaymentsClient.RetrieveReport(GetPaymentId(paymentType), fileFormat);
SpreadsheetInfo.SetLicense(TestContext.GemBoxSerial);
var sheet = new ExcelFile();
using (var stream = new MemoryStream(payment))
{
sheet.LoadCsv(stream, CsvType.CommaDelimited);
stream.Close();
}
AssertResponseDoesNotHaveAnError(payment);
Assert.That(sheet.Worksheets.Count, Is.GreaterThan(0));
}
public override void Save(string[,] collection, string fileName)
{
var excelFile = new ExcelFile();
if (excelFile.Worksheets.Count == 0)
{
excelFile.Worksheets.Add("Sheet1");
}
ExcelWorksheet worksheet = excelFile.Worksheets[0];
if (worksheet != null)
{
for (int i = 0; i < collection.GetLength(0); i++)
{
for (int j = 0; j < collection.GetLength(1); j++)
{
worksheet.Cells[i, j].Value = collection[i, j];
}
}
base.Save(collection, fileName);
excelFile.Save(fileName);
}
}
// load data to excel
public bool ExcelExport(System.Data.DataTable objTable, System.Data.DataTable objTableNodes, string sFileName)
{
ExcelFile ef = new ExcelFile();
try
{
CreateExcelFile(sFileName);
// Load Excel file.
ef.LoadXls(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\" + sFileName);
// Select the first worksheet from the file.
ExcelWorksheet ws = ef.Worksheets[0];
// Extract the data from the worksheet to the DataTable.
// Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
// ws.ExtractToDataTable(objTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
// Insert the data from DataTable to the worksheet starting at cell "A1"
ws.InsertDataTable(objTable, "D5", true);
if (objTableNodes != null)
ws.InsertDataTable(objTableNodes, "R5", true);
// Save the file to XLS format.
ef.SaveXls(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\" + sFileName);
// open the excel file
//object misValue = System.Reflection.Missing.Value;
//Application appExcel = new Application();
//Workbook wbExcel = appExcel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\" + sFileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
return true;
}
public void IsValidCsvData(DataFileFormat fileFormat)
{
var clientConfiguration = GetDefaultDataClientConfiguration()
.With((clientConfig) => clientConfig.WireDataFormat = WireDataFormat.Other);
var client = new JustGivingDataClient(clientConfiguration);
var pagesClient = CreatePagesClient(client);
var startDate = new DateTime(2011,4,28);
var endDate = new DateTime(2011,6,28);
var data = pagesClient.RetrievePagesCreated(startDate, endDate, fileFormat);
SpreadsheetInfo.SetLicense(TestContext.GemBoxSerial);
var sheet = new ExcelFile();
using (var stream = new MemoryStream(data))
{
LoadDataInToWorkSheet(stream, sheet, fileFormat);
}
Assert.That(sheet.Worksheets.Count, Is.GreaterThan(0));
}
private void ParseExcelSheets(string filename, ExcelFile report, DateTime reportDate)
{
foreach (ExcelWorksheet sheet in report.Worksheets)
{
Console.WriteLine(filename);
var supermarketName = sheet.Rows[1].AllocatedCells[1].Value.ToString();
supermarketName = this.ReplaceSpecialCharacters(supermarketName);
var supermarketId =
this.SqlManager.GetSupermarketIdByName(supermarketName);
if (supermarketId == null)
{
Console.WriteLine("Supermarket does not exist in the database!");
continue;
}
var productsImported = this.ParseRowsData(reportDate, sheet, (int)supermarketId);
Console.WriteLine("{0}/{1} sales imported.\n", productsImported, sheet.Rows.Count - 4);
}
}
void Export()
{
SPListItem item = SPContext.Current.ListItem;
ISharePointService sps = ServiceFactory.GetSharePointService(true);
SPList items = sps.GetList("Store Maintenance Items2");
QueryField field = new QueryField("WorkflowNumber", false);
SPListItemCollection coll = sps.Query(items, field.Equal(item["WorkflowNumber"] + ""),0);
int len = coll.Count;
if (len > 0)
{
string strFilePath = Server.MapPath("standard PO.xls");
string strCostCenter = item["CostCenter"] + "";
string strDate = item["Created"] + "";
//string strTotalPrice
decimal coltotal = 0;
string strInstallation = "0.00";
string strFreigh = "0.00";
string strPackaging = "0.00";
string strFileName = this.DataForm1.WorkflowNumber + ".xls";
string strPath = Server.MapPath("/tmpfiles/excel");
DirectoryInfo dinfo = new DirectoryInfo(strPath);
if (!dinfo.Exists)
{
Directory.CreateDirectory(strPath);
}
string strSavePath = strPath + "/" + item["WorkflowNumber"] + ".xls";
GemBox.Spreadsheet.SpreadsheetInfo.SetLicense("E43X-6VAB-CTVW-E9C8");
GemBox.Spreadsheet.ExcelFile objExcelFile = new ExcelFile();
objExcelFile.LoadXls(strFilePath);
GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets[0];
GemBox.Spreadsheet.ExcelWorksheet worksheet2 = objExcelFile.Worksheets[1];
//objExcelFile = new ExcelFile();
//GemBox.Spreadsheet.ExcelWorksheet worksheet1 = objExcelFile.Worksheets.AddCopy(worksheet1.Name, worksheet1);
//GemBox.Spreadsheet.ExcelWorksheet worksheet2 = objExcelFile.Worksheets.AddCopy(worksheet2.Name, worksheet2);
int j = 0;
int k = 0;
for (int i = 10; i < 10 + len; i++)
{
decimal price = decimal.Parse(coll[k]["Price"] + "");
decimal total = decimal.Parse(coll[k]["Total"] + "");
int quantity = int.Parse(coll[k]["Quantity"] + "");
decimal rowtotal = price * quantity;
coltotal += rowtotal;
worksheet1.Rows[i].InsertEmpty(1);
worksheet1.Cells[i, 0].Value = ++j;
worksheet1.Cells[i, 1].Value = coll[k]["Seq"].ToString();
worksheet1.Cells[i, 2].Value = strCostCenter;
worksheet1.Cells.GetSubrangeAbsolute(i, 3, i, 6).Merged = true;
worksheet1.Cells[i, 3].Value = coll[k]["Reason"].ToString();
worksheet1.Cells[i, 7].Value = strDate;
worksheet1.Cells.GetSubrangeAbsolute(i, 8, i, 9).Merged = true;
worksheet1.Cells[i, 8].Value = coll[k]["Quantity"].ToString();
//worksheet1.Cells[i, 10].Value = coll[k]["Unit"].ToString();
worksheet1.Cells.GetSubrangeAbsolute(i, 11, i, 14).Merged = true;
worksheet1.Cells[i, 14].Value = price.ToString("N2");
worksheet1.Cells.GetSubrangeAbsolute(i, 15, i, 19).Merged = true;
worksheet1.Cells[i, 19].Value = total.ToString("N2");//rowtotal.ToString("N2");
worksheet1.Cells.GetSubrangeAbsolute(i, 20, i, 24).Merged = true;
worksheet1.Cells[i, 24].Value = "";
k++;
}
worksheet1.Cells[11 + len, 0].Value = coltotal.ToString("N2");
worksheet1.Cells[11 + len, 2].Value = strInstallation;
worksheet1.Cells[11 + len, 4].Value = strPackaging;
worksheet1.Cells[11 + len, 6].Value = strFreigh;
worksheet1.Cells[11 + len, 9].Value = coltotal.ToString("N2");
j = 0; k = 0;
for (int i = 10; i < 10 + len; i++)
{
decimal price = decimal.Parse(coll[k]["Price"] + "");
decimal total = decimal.Parse(coll[k]["Total"] + "");
int quantity = int.Parse(coll[k]["Quantity"] + "");
decimal rowtotal = price * quantity;
worksheet2.Rows[i].InsertEmpty(1);
worksheet2.Cells[i, 0].Value = ++j;
worksheet2.Cells[i, 1].Value = coll[k]["Seq"].ToString();
worksheet2.Cells[i, 2].Value = strCostCenter;
worksheet2.Cells.GetSubrangeAbsolute(i, 3, i, 6).Merged = true;
worksheet2.Cells[i, 3].Value = coll[k]["Reason"].ToString();
worksheet2.Cells[i, 7].Value = "";
worksheet2.Cells.GetSubrangeAbsolute(i, 8, i, 9).Merged = true;
worksheet2.Cells[i, 8].Value = coll[k]["Quantity"].ToString();
//worksheet2.Cells[i, 10].Value = coll[k]["Unit"].ToString();
worksheet2.Cells.GetSubrangeAbsolute(i, 11, i, 14).Merged = true;
worksheet2.Cells[i, 14].Value = price.ToString("N2");
worksheet2.Cells.GetSubrangeAbsolute(i, 15, i, 19).Merged = true;
worksheet2.Cells[i, 19].Value = total.ToString("N2");//rowtotal.ToString("N2");
worksheet2.Cells.GetSubrangeAbsolute(i, 20, i, 24).Merged = true;
worksheet2.Cells[i, 24].Value = "";
k++;
}
worksheet2.Cells[11 + len, 0].Value = coltotal.ToString("N2");
worksheet2.Cells[11 + len, 2].Value = strInstallation;
worksheet2.Cells[11 + len, 4].Value = strPackaging;
worksheet2.Cells[11 + len, 6].Value = strFreigh;
worksheet2.Cells[11 + len, 9].Value = coltotal.ToString("N2");
if (File.Exists(strSavePath))
{
File.Delete(strSavePath);
}
objExcelFile.SaveXls(strSavePath);
}
}
public string CreateReceipt(ModelViewUserG objCred, ModelViewReceiptRef obj)
{
var objRepository = new RepositoryOrder();
var NegocioOrden = new BusinessOrder();
var objAlerta = new BusinessNotification();
var NegocioUsuario = new BusinessUsers();
var NegocioCliente = new BusinessClient();
var NegocioContrato = new BusinessContrat();
var NegocioEmpleado = new BusinessEmployee();
var NegocioBOM = new BusinessBuildOfMaterial();
var dataUsuario = NegocioUsuario.GetUserByToken(objCred.TokenUser);
if (objCred.TokenApp != GlobalConfiguration.TokenWEB)
{
if (objCred.TokenApp != GlobalConfiguration.TokenMobile)
{
throw new Exception("TokenInvalid");
}
}
if (dataUsuario == null)
{
throw new Exception("UserPasswordInvalid");
}
var empleado = NegocioEmpleado.GetByUserID(dataUsuario.UserID);
var orden = NegocioOrden.GetByOrderID(obj.FK_OrderID);
var cliente = NegocioCliente.GetByID(orden.FK_ClientID);
SpreadsheetInfo.SetLicense("EQU2-3K5L-UZDC-SDYN");
string Origin = GlobalConfiguration.MabeAttachmentsLocal + "FormatoReciboRef.xlsx";
List <string> result = obj.EMails.Split(new char[] { ';' }).ToList();
ExcelFile ef = ExcelFile.Load(Origin);
ExcelWorksheet ws = ef.Worksheets[0];
ws.Cells["W9"].Value = obj.Folio;
ws.Cells["W12"].Value = DateTime.Today.ToString("dd-MM-yyyy");
ws.Cells["L18"].Value = cliente.FirstName.ToUpper();
ws.Cells["P18"].Value = cliente.LastName.ToUpper();
ws.Cells["L21"].Value = cliente.PhoneNumber1;
ws.Cells["T21"].Value = cliente.PhoneNumber2;
ws.Cells["M23"].Value = cliente.Email;
int cantidad = 29;
foreach (var jko in obj.SpareParts)
{
if (cantidad == 60)
{
cantidad = 85;
}
////var bom = NegocioBOM.GetByID(jko.ProductID);
//if (bom != null)
//{
// ws.Cells["L" + cantidad1.ToString()].Value = bom.SparePartsID;
// ws.Cells["L" + cantidad.ToString()].Value = bom.SparePartDescription;
//}
else
{
ws.Cells["K" + cantidad.ToString()].Value = jko.RefManID;
ws.Cells["M" + cantidad.ToString()].Value = jko.SparePartsDescription;
}
ws.Cells["R" + cantidad.ToString()].Value = jko.Quantity;
ws.Cells["U" + cantidad.ToString()].Value = Convert.ToDouble(jko.Price);
ws.Cells["W" + cantidad.ToString()].Value = Convert.ToDouble(jko.Totals);
cantidad = cantidad + 1;
}
double subtotal = Convert.ToDouble(obj.SubTotal);
double iva = Convert.ToDouble(obj.IVA);
double total = Convert.ToDouble(obj.Total);
double subtotalref = subtotal;
string totalletras = NegocioOrden.enletras(total.ToString());
ws.Cells["W41"].Value = subtotalref;
ws.Cells["W42"].Value = iva;
ws.Cells["W43"].Value = total;
string file = "ReciboRef" + obj.Folio + ".pdf";
string quotion = "RecibosRefaccion_" + DateTime.Today.ToString("yyyyMMdd");
string Destiny = GlobalConfiguration.MabeAttachmentsLocal + quotion + "/" + file;
if (obj.SpareParts.Count < 11)
{
ws.NamedRanges.SetPrintArea(ws.Cells.GetSubrange("J1", "X54"));
}
string Cotizaciones = new DirectoryInfo(GlobalConfiguration.MabeAttachmentsLocal).ToString() + quotion;
if (!(Directory.Exists(Cotizaciones)))
{
Directory.CreateDirectory(Cotizaciones);
}
ef.Save(Destiny);
string URL = GlobalConfiguration.urlRequest + "Content/Attachments/" + quotion + "/" + file;
var Recibo = NegocioContrato.GetByOrderFolio(orden.PK_OrderID, obj.Folio);
if (obj.EMails != "")
{
if (Recibo == null)
{
Recibo = NegocioContrato.Insert(orden.PK_OrderID, obj.Folio, URL);
}
else
{
Recibo.Fk_OrderID = orden.PK_OrderID;
Recibo.Folio = obj.Folio;
Recibo.Ruta = URL;
NegocioContrato.Update(Recibo);
}
string sb = File.ReadAllText(GlobalConfiguration.LocateBodyMail + "MaiReciboRef.txt");
sb = sb.Replace("#%Nombre%#", cliente.FirstName + " " + cliente.LastName);
sb = sb.Replace("#%Folio%#", obj.Folio);
objAlerta.SendMailExchange(GlobalConfiguration.exchangeUserCotiza, GlobalConfiguration.exchangePwdCotiza, result, "Recibo Refacciones", sb.ToString(), Destiny);
return(URL);
}
else
{
string Tipo = "Recibo";
var EnvioSMS = SendNotification(obj.PhoneNumber, Tipo, URL);
return(EnvioSMS);
}
}
private void button1_Click(object sender, EventArgs e)
{
InitTablesList();
DataSet dataSet = new DataSet();
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
foreach (string table in Tables)
{
if (table == "Test")
{
Test tst = new Test();
string sql = tst.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Тип")
{
Type tp = new Type();
string sql = tp.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Поставщик")
{
Provider pr = new Provider();
string sql = pr.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Страна")
{
Country c = new Country();
string sql = c.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Клиент")
{
Client cl = new Client();
string sql = cl.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Должность")
{
Post ps = new Post();
string sql = ps.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Производитель")
{
Creator cr = new Creator();
string sql = cr.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Товар")
{
Product p = new Product();
string sql = p.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Заказ")
{
Order or = new Order();
string sql = or.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Сотрудник")
{
Employee em = new Employee();
string sql = em.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
if (table == "Отправка")
{
Sending send = new Sending();
string sql = send.SelectShow;
dataSet.Tables.Add(controll.InitDT(sql));
}
}
// Create new ExcelFile.
ExcelFile workbook2 = new ExcelFile();
// Imports all tables from DataSet to new file.
foreach (DataTable dataTable in dataSet.Tables)
{
// Add new worksheet to the file.
ExcelWorksheet worksheet = workbook2.Worksheets.Add(dataTable.TableName);
worksheet.Cells[0, 0].Value = "Таблица: " + dataTable.TableName;
CellRange mrgdRange = worksheet.Cells.GetSubrangeAbsolute(0, 0, 0, dataTable.Columns.Count - 1);
mrgdRange.Merged = true;
CellStyle cs = new CellStyle();
cs.HorizontalAlignment = HorizontalAlignmentStyle.Center;
cs.VerticalAlignment = VerticalAlignmentStyle.Center;
cs.Font.Weight = ExcelFont.BoldWeight;
cs.Font.Size = 16 * 18;
mrgdRange.Style = cs;
// Insert the data from DataTable to the worksheet starting at cell "A1".
// worksheet.InsertDataTable(dataTable,
// new InsertDataTableOptions("A2") { ColumnHeaders = true });
//Добавляем все остальные ячейки
for (int i = 0; i < dataTable.Columns.Count; i++)
{
worksheet.Cells[1, i].Value = dataTable.Columns[i].ColumnName;
worksheet.Cells[1, i].Style.Font.Size = 16 * 16;
worksheet.Cells[1, i].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Cells[1, i].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
}
for (int i = 0; i < dataTable.Rows.Count; i++)
{
worksheet.Columns[i].Width = 30 * 200;
for (int j = 0; j < dataTable.Columns.Count; j++)
{
worksheet.Cells[i + 2, j].Value = dataTable.Rows[i][j];
//_excelHeader++;
}
}
}
// Save the file to XLS format.
workbook2.Save(@"C:\Users\WorkUser\Desktop\DataSet.xls");
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
const int BEGIN_COLUMN_INDEX = 1;
const int CAMP_NAME_MERGED_CELL_NUMBER = 6;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20;
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "FJC Allocation Report";
//
iRow += 1;
// Create Report SubHeader - usually it's camp year
CellStyle styleSubHeader = new CellStyle();
styleSubHeader.Font.Size = 16 * 20;
styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = "Camp Year: " + ddlCampYear.SelectedItem.Text;
SubHeader.Style = styleSubHeader;
iRow += 2;
// Data Content of report
DataTable dt = GenerateDataTable();
dt.Columns.RemoveAt(1); // delete the federation ID
dt.Columns.RemoveAt(2); // delete the FCJ Allocated (because it's alreay divided into FJC Standard and Overage
dt.Columns.RemoveAt(2); // delete the PartnerAllocated (because it has another duplicate row that located in the right position in column index
CellStyle cs = new CellStyle();
cs.Font.Size = 18 * 20;
cs.Font.Weight = ExcelFont.BoldWeight;
CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
cr.Merged = true;
cr.Value = "";
cr.Style = cs;
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
dt.AcceptChanges();
ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
ws.Cells[iRow + dt.Rows.Count, i].Style = tableHeaderStyle;
if (i == 1)
{
ws.Columns[i].Width = 35 * 256;
}
else
{
ws.Columns[i].Width = 20 * 256;
}
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = "\\" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Millisecond.ToString() + "CamperCountByProgramAndStatus.xls";
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
public void RevenueExportBySales(IList <Booking> bookings)
{
ExcelFile excelFile = new ExcelFile();
excelFile.LoadXls(Server.MapPath("/Modules/Sails/Admin/ExportTemplates/BaoCaoDoanhThuTheoSales.xls"));
DateTime from = DateTimeUtil.DateGetDefaultFromDate();
try
{
if (Request.QueryString != null)
{
from = DateTime.ParseExact(Request.QueryString["f"], "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
}
catch (Exception) { }
DateTime to = DateTimeUtil.DateGetDefaultToDate();
try
{
if (Request.QueryString != null)
{
to = DateTime.ParseExact(Request.QueryString["t"], "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
}
catch (Exception) { }
var time = string.Format("{0:dd/MM/yyyy} - {1:dd/MM/yyyy}", from, to);
if (from.Month == to.Month)
{
if (to.Subtract(from).Days == (DateTime.DaysInMonth(from.Year, from.Month) - 1))
{
time = string.Format("{0:MMM - yyyy}", from);
}
}
ExcelWorksheet activeWorkSheet = excelFile.Worksheets[0];
activeWorkSheet.Cells["G1"].Value = time;
var sales = (from b in bookings select b.BookingSale.Sale).Where(x => x != null).Distinct().ToList <User>();
foreach (User _sales in sales)
{
excelFile.Worksheets.AddCopy(_sales.FullName, excelFile.Worksheets[0]);
}
foreach (User _sales in sales)
{
foreach (ExcelWorksheet sheet in excelFile.Worksheets)
{
if (_sales.FullName == sheet.Name)
{
activeWorkSheet = sheet;
var salesBookings = (from b in Bookings
where b.BookingSale.Sale == _sales
select b).ToList <Booking>();
activeWorkSheet.Cells["C4"].Value = _sales.FullName;
RevenueExportBySalesToSheet(activeWorkSheet, salesBookings);
}
}
}
if (excelFile.Worksheets.Count > 1)
{
excelFile.Worksheets[0].Delete();
}
ExcelSendBackToClient(excelFile, string.Format("DoanhThuSales {0}.xls", time));
}
public static string ExportToExcel(DataTable elementData, DataTable recommendationData)
{
var fPath = Path.GetTempFileName() + ".xlsx";
try
{
SpreadsheetInfo.SetLicense(AppConstants.GemLic);
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Exported Data - SSK Online");
var styleHeader2 = new CellStyle();
styleHeader2.HorizontalAlignment = HorizontalAlignmentStyle.Left;
styleHeader2.VerticalAlignment = VerticalAlignmentStyle.Center;
styleHeader2.Font.Weight = ExcelFont.BoldWeight;
styleHeader2.FillPattern.SetSolid(System.Drawing.Color.LightGreen);
var styleHeader = new CellStyle();
styleHeader.HorizontalAlignment = HorizontalAlignmentStyle.Left;
styleHeader.VerticalAlignment = VerticalAlignmentStyle.Center;
styleHeader.Font.Weight = ExcelFont.BoldWeight;
styleHeader.FillPattern.SetSolid(System.Drawing.Color.AliceBlue);
int Rows = 0;
int Cols = 0;
foreach (DataColumn dc in elementData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dc.ColumnName;
worksheet.Cells[Rows, Cols].Style = styleHeader;
Cols++;
}
Rows = 1;
foreach (DataRow dr in elementData.Rows)
{
Cols = 0;
foreach (DataColumn dc in elementData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dr[dc.ColumnName].ToString();
Cols++;
}
Rows++;
}
Cols = 0;
foreach (DataColumn dc in recommendationData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dc.ColumnName;
worksheet.Cells[Rows, Cols].Style = styleHeader2;
Cols++;
}
Rows++;
foreach (DataRow dr in recommendationData.Rows)
{
Cols = 0;
foreach (DataColumn dc in recommendationData.Columns)
{
worksheet.Cells[Rows, Cols].Value = dr[dc.ColumnName].ToString();
Cols++;
}
Rows++;
}
workbook.Save(fPath);
}
catch (Exception ex)
{
Console.WriteLine($"Error - save to file : {ex.Message}");
return(null);
}
return(fPath);
}
public File(string name, ExcelFile tableCollection)
{
this.Name = name;
this.Tables = tableCollection;
}
/// <summary>
/// 将Users表中的数据导出到指定filepath的Excel中，需要用到GemBox.ExcelLite;
/// /// </summary>
///<param name="dataGridView1">excel的路径</param>
///<param name="filePath">保存excel的路径</param>
public static void Export_to_Excel(DataGridView dataGridView1, string filepath)
{
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet sheet = excelFile.Worksheets.Add("Users");
int columns = dataGridView1.Columns.Count;
int rows = dataGridView1.Rows.Count;
for (int j = 0; j < columns; j++)
{
sheet.Cells[0, j].Value = dataGridView1.Columns[j].HeaderText;
}
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
sheet.Cells[i, j].Value = dataGridView1[j, i - 1].Value.ToString().Trim();
}
}
excelFile.SaveXls(@filepath);
MessageBox.Show("生成成功");
}
public static void Main(string[] args)
{
//Создаем переменную, содержащую все параметры продукта внутри себя
Product product = new Product();
//Создаём первый массив вопросов, позже они в цикле будут направленны пользователю в виде подсказки
String[] questions = new string[]
{
"наименование изделия",
"код изделия",
"единицу измерения",
"цену за единицу измеряемого изделия",
"номер цеха",
"номер склада",
"наименование склада",
"номер накладной",
"дату договора",
"дату в накладной",
"количество товара",
"номер договора"
};
//Создаем цикл, который проитерирует по всем вопросам и задаст их пользователю
for (int iterator = 0; iterator < questions.Length; iterator++)
{
//Вывод подсказки пользователю с подстановкой вопроса в неё
Console.Write("Введите " + questions[iterator] + ": ");
//Читаем ввод от пользователя
String userInput = Console.ReadLine();
//Вызываем у продукта функцию установки параметра по индексу и результат записываем в переменную статуса
bool status = product.SetParameter(iterator, userInput);
//Если статус установки параметра является неуспешным - уменьшаем переменную итератора
//это действие заставит цикл повторить вопрос
if (status == false)
{
iterator--;
}
}
//Создаём второй массив вопросов, данные вопросы будут использоваться в двух циклах,
//а именно в цикле вопросов по поставщику и по покупателю
String[] personQuestions = new string[]
{
"имя",
"фамилию",
"город",
"номер телефона"
};
//Создаем цикл вопросов к поставщику
//Принцип работы данного цикла идентичен первому циклу с основными вопросами
for (int i = 0; i < personQuestions.Length; i++)
{
Console.Write("Введите " + personQuestions[i] + " поставщика: ");
bool status = product.Diler.SetParameter(i, Console.ReadLine());
if (status == false)
{
i--;
}
}
//Создаем цикл вопросов к покупателю
//Принцип работы данного цикла идентичен первому циклу с основными вопросами
for (int i = 0; i < personQuestions.Length; i++)
{
Console.Write("Введите " + personQuestions[i] + " покупателя: ");
bool status = product.Buyer.SetParameter(i, Console.ReadLine());
if (status == false)
{
i--;
}
}
//Установка параметров в переменную product завершена
//Установка лицензии для использование библиотеки ввода и вывода данных в Excel
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
//Создание переменной фойла Excel
ExcelFile excelFile = new ExcelFile();
//Добавление в файл Excell новой таблици с именем Отчет
ExcelWorksheet mainTable = excelFile.Worksheets.Add("Отчет");
//Запись параметров продукта в таблицу
WriteProductToTable(mainTable, product);
//Сохранение файла Excel
excelFile.Save("Файл.xlsx");
}
public override void CreatePackage(string fileName)
{
// Извлекаем шаблон из вспомогательной сборки и создаем объект экселя
var ef = ExcelFile.Load(DocumentTemplate.ExtractXls("PublicEntrantList"));
var ws = ef.Worksheets[0];
// готовим шляпу
string reportTitle = string.Format("Списки поступающих на {0} - {1} форма обучения",
DateTime.Now.ToString("dd.MM.yyyy г."), _educationForm.Name);
ws.FindAndReplaceText("ReportTitle", reportTitle);
// Подготовка данных
var directionNameTemplate = ws.Rows[0];
var headTableTemplate = ws.Rows[2];
var itemTableTemplate = ws.Rows[3];
var i = 7;
// Получаем список направлений
var directions = (from compGroup in Session.DataModel.CompetitiveGroups
where compGroup.EducationForm.Id == _educationForm.Id &&
compGroup.Campaign.CampaignStatus.Id == 2
orderby compGroup.EducationLevel.Id, compGroup.Direction.Code
select compGroup.Direction).ToList();
// Удалить дубликаты из коллекции, отсортировать по коду
directions = directions.DistinctBy(d => d.Code).ToList();
foreach (var direction in directions)
{
// Прописываем направление
ws.Rows.InsertCopy(i, directionNameTemplate);
ws.Rows[i].Cells[0].Value = string.Format("{0} {1}",
direction.Code, direction.Name);
i += 2;
ws.Rows.InsertCopy(i, headTableTemplate);
i++;
int j = 1;
// Получаем конкурсные группы по направлению и форме обучения
var actualCompetitiveGroups = (from compGroup in direction.CompetitiveGroups
where compGroup.EducationForm.Id == _educationForm.Id &&
compGroup.Campaign.CampaignStatus.Id == 2
orderby compGroup.FinanceSource.SortNumber ascending
select compGroup).ToList();
int flag = i;
foreach (var compGroup in actualCompetitiveGroups)
{
// Получить список условий приёма
var conditions = (from cond in compGroup.ClaimConditions
where cond.Claim != null &&
(cond.Claim.ClaimStatus.Id == 1 || cond.Claim.ClaimStatus.Id == 2)
select cond).OrderByDescending(c => c.Claim.TotalScore).ToList();
foreach (var condition in conditions)
{
ws.Rows.InsertCopy(i, itemTableTemplate);
var currentRow = ws.Rows[i];
currentRow.Cells[0].Value = j;
currentRow.Cells[1].Value = condition.Claim.Person.FullName;
currentRow.Cells[2].Value = condition.Claim.Number;
currentRow.Cells[3].Value = condition.Claim.IsOriginal ? "оригинал" : "копия";
currentRow.Cells[4].Value = condition.CompetitiveGroup.FinanceSource.Name;
currentRow.Cells[5].Value = condition.Priority;
currentRow.Cells[6].Value = condition.Claim.TotalScore;
i++;
j++;
}
}
// КОСТЫЛЬ: Если нет абитуриентов по направлению, то скрываем ко всем херам
if (flag == i)
{
ws.Rows[i - 3].Hidden = true;
ws.Rows[i - 2].Hidden = true;
ws.Rows[i - 1].Hidden = true;
ws.Rows[i].Hidden = true;
continue;
}
i += 2;
}
// Скрываем шаблоны
directionNameTemplate.Hidden = true;
headTableTemplate.Hidden = true;
itemTableTemplate.Hidden = true;
ws.Rows[1].Hidden = true;
ws.Rows[4].Hidden = true;
ef.Save(fileName);
}
public Stream GetMembershipReport()
{
ExcelFile xl = ExcelService.Create(ExcelFileType.XLS);
var goodList = xl.CreateSheet("Mission Ready");
this.GenerateMissionReadySheets(null, xl, goodList);
var dataSheet = xl.CreateSheet("Member Data");
var interestingCourses = (from c in this.db.TrainingCourses where c.WacRequired > 0 select c).OrderBy(x => x.DisplayName).ToList();
interestingCourses.AddRange(this.db.GetCoreCompetencyCourses());
IQueryable <Member> members = this.db.Members.Include("Addresses", "Memberships", "ComputedAwards.Course").Where(f => f.Memberships.Any(g => g.Status.IsActive && g.EndTime == null));
members = members.OrderBy(f => f.LastName).ThenBy(f => f.FirstName);
// Set column header titles. A static list, followed by a list of "interesting" training courses
var columns = new[] { "DEM", "Lastname", "Firstname", "WAC Card", "Street", "City", "State", "ZIP", "Phone", "Email", "HAM", "Units" }.Union(interestingCourses.Select(f => f.DisplayName)).ToArray();
for (int i = 0; i < columns.Length; i++)
{
dataSheet.CellAt(0, i).SetValue(columns[i]);
dataSheet.CellAt(0, i).SetBold(true);
}
int row = 1;
foreach (var m in members)
{
int col = 0;
dataSheet.CellAt(row, col++).SetValue(m.DEM);
dataSheet.CellAt(row, col++).SetValue(m.LastName);
dataSheet.CellAt(row, col++).SetValue(m.FirstName);
dataSheet.CellAt(row, col++).SetValue(m.WacLevel.ToString());
var address = m.Addresses.OrderBy(f => f.InternalType).FirstOrDefault();
if (address != null)
{
dataSheet.CellAt(row, col++).SetValue(address.Street);
dataSheet.CellAt(row, col++).SetValue(address.City);
dataSheet.CellAt(row, col++).SetValue(address.State);
dataSheet.CellAt(row, col++).SetValue(address.Zip);
}
else
{
col += 4;
}
Action <Member, string> doContact = (member, type) =>
{
var phone = m.ContactNumbers.Where(f => f.Type == type).OrderBy(f => f.Priority).FirstOrDefault();
if (phone != null)
{
dataSheet.CellAt(row, col).SetValue(phone.Value);
}
col++;
};
doContact(m, "phone");
doContact(m, "email");
doContact(m, "hamcall");
dataSheet.CellAt(row, col++).SetValue(string.Join(" ",
m.Memberships.Where(f => f.Status.IsActive && f.EndTime == null).Select(f => f.Unit.DisplayName).OrderBy(f => f)
));
var trainingStatus = CompositeTrainingStatus.Compute(m, interestingCourses, DateTime.Now);
for (int i = 0; i < interestingCourses.Count; i++)
{
dataSheet.CellAt(row, col++).SetValue(trainingStatus.Expirations[interestingCourses[i].Id].ToString());
}
row++;
}
//IQueryable<UnitMembership> memberships = this.db.UnitMemberships.Include("Person.Addresses", "Person.ContactNumbers").Include("Status");
//memberships = memberships.Where(um => um.EndTime == null && um.Status.IsActive);
//memberships = memberships.OrderBy(f => f.Person.LastName).ThenBy(f => f.Person.FirstName);
MemoryStream ms = new MemoryStream();
xl.Save(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
///
/// </summary>
/// <param name="unit"></param>
/// <param name="xl"></param>
/// <param name="goodList"></param>
private void GenerateMissionReadySheets(SarUnit unit, ExcelFile xl, ExcelSheet goodList)
{
IMissionReadyPlugin extension = null;
string longName = this.settings.GroupFullName ?? this.settings.GroupName;
IQueryable <UnitMembership> memberships = this.db.UnitMemberships.Include("Person.ComputedAwards.Course").Include("Status");
if (unit != null)
{
memberships = memberships.Where(um => um.Unit.Id == unit.Id);
longName = unit.LongName;
extension = this.extensions.For <IMissionReadyPlugin>(unit);
}
memberships = memberships.Where(um => um.EndTime == null && um.Status.IsActive);
memberships = memberships.OrderBy(f => f.Person.LastName).ThenBy(f => f.Person.FirstName);
goodList.Header = longName + " Mission Active Roster";
goodList.Footer = DateTime.Now.ToShortDateString();
var courses = (from c in this.db.TrainingCourses where c.WacRequired > 0 select c).OrderBy(x => x.DisplayName).ToList();
int headerIdx = 0;
Action <string> appendHeader = head =>
{
var cell = goodList.CellAt(0, headerIdx++);
cell.SetValue(head);
cell.SetBold(true);
cell.SetTextWrap(true);
};
Action <MissionReadyColumns> insertExtensionHeaders = group =>
{
if (extension == null)
{
return;
}
foreach (var value in extension.GetHeadersAfter(group))
{
appendHeader(value);
}
};
insertExtensionHeaders(MissionReadyColumns.Start);
appendHeader("DEM");
insertExtensionHeaders(MissionReadyColumns.WorkerNumber);
appendHeader("Lastname");
appendHeader("Firstname");
insertExtensionHeaders(MissionReadyColumns.Name);
appendHeader("Card Type");
insertExtensionHeaders(MissionReadyColumns.WorkerType);
foreach (var course in courses)
{
var cell = goodList.CellAt(0, headerIdx++);
cell.SetValue(course.DisplayName);
cell.SetBold(true);
cell.SetTextWrap(true);
}
insertExtensionHeaders(MissionReadyColumns.Courses);
ExcelSheet badList = xl.CopySheet(goodList.Name, "Non-Mission Members");
ExcelSheet nonFieldList = xl.CopySheet(goodList.Name, "Admin Members");
using (SheetAutoFitWrapper good = new SheetAutoFitWrapper(xl, goodList))
{
using (SheetAutoFitWrapper bad = new SheetAutoFitWrapper(xl, badList))
{
using (SheetAutoFitWrapper admin = new SheetAutoFitWrapper(xl, nonFieldList))
{
int idx = 1;
int c = 0;
Guid lastId = Guid.Empty;
foreach (UnitMembership membership in memberships)
{
Member member = membership.Person;
if (member.Id == lastId)
{
continue;
}
lastId = member.Id;
CompositeTrainingStatus stats = CompositeTrainingStatus.Compute(member, courses, DateTime.Now);
SheetAutoFitWrapper wrap = bad;
// If the person isn't supposed to keep up a WAC card, then they're administrative...
if (membership.Status.WacLevel == WacLevel.None)
{
wrap = admin;
}
// If they're current on training and have a DEM card, they're good...
else if (stats.IsGood && member.WacLevel != WacLevel.None)
{
wrap = good;
}
idx = wrap.Sheet.NumRows + 1;
c = 0;
Action <MissionReadyColumns> insertExtensionColumns = group =>
{
if (extension == null)
{
return;
}
foreach (var value in extension.GetColumnsAfter(group, member))
{
wrap.SetCellValue(value, idx, c++);
}
};
insertExtensionColumns(MissionReadyColumns.Start);
wrap.SetCellValue(string.Format("{0:0000}", member.DEM), idx, c++);
insertExtensionColumns(MissionReadyColumns.WorkerNumber);
wrap.SetCellValue(member.LastName, idx, c++);
wrap.SetCellValue(member.FirstName, idx, c++);
insertExtensionColumns(MissionReadyColumns.Name);
ExcelCell cell = wrap.Sheet.CellAt(idx, c);
switch (member.WacLevel)
{
case WacLevel.Field:
cell.SetFillColor(Color.Green);
cell.SetFontColor(Color.White);
break;
case WacLevel.Novice:
cell.SetFillColor(Color.Red);
cell.SetFontColor(Color.White);
break;
case WacLevel.Support:
cell.SetFillColor(Color.Orange);
break;
}
wrap.SetCellValue(member.WacLevel.ToString(), idx, c++);
insertExtensionColumns(MissionReadyColumns.WorkerType);
foreach (var course in courses)
{
TrainingStatus stat = stats.Expirations[course.Id];
if ((stat.Status & ExpirationFlags.Okay) != ExpirationFlags.Okay)
{
wrap.Sheet.CellAt(idx, c).SetFillColor(Color.Pink);
wrap.Sheet.CellAt(idx, c).SetBorderColor(Color.Red);
}
wrap.SetCellValue(stat.ToString(), idx, c);
if (stat.Expires.HasValue)
{
wrap.Sheet.CellAt(idx, c).SetValue(stat.Expires.Value.Date.ToString("yyyy-MM-dd"));
}
c++;
}
insertExtensionColumns(MissionReadyColumns.Courses);
if (wrap == bad)
{
wrap.Sheet.CellAt(idx, c).SetValue(member.ContactNumbers.Where(f => f.Type == "email").OrderBy(f => f.Priority).Select(f => f.Value).FirstOrDefault());
}
insertExtensionColumns(MissionReadyColumns.End);
idx++;
}
admin.Sheet.AutoFitAll();
good.Sheet.AutoFitAll();
bad.Sheet.AutoFitAll();
}
}
}
}
public void m_BExport_Click(object sender, EventArgs e)
{
if (this.m_dgv_ItemDetail.Rows.Count <= 0 && this.m_dgv_ItemDetail.ColumnCount <= 0)
{
MessageBox.Show("请选择您要打开的模板项，然后再进行导出。", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
return;
}
String path = this.GetCurrentCsvFile();
if (System.IO.File.Exists(path))
{
if (MessageBox.Show("文件已经存在，是否覆盖?", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.Cancel)
{
return;
}
}
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Mrg");
try
{
DataGridViewConverter.ImportFromDataGridView(ws, this.m_dgv_ItemDetail, new ImportFromDataGridViewOptions() { ColumnHeaders = true });
ef.Save(path, CsvSaveOptions.CsvDefault);
Process.Start("Excel.exe", path);
}
catch (System.Exception ex)
{
MessageBox.Show("文件保存失败 : " + ex.Message, "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
}
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX = 1;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Session Length by Camp (Online Data Only)";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
CellStyle styleReportSubHeader = new CellStyle();
styleReportSubHeader.Font.Size = 16 * 20;
styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Style = styleReportSubHeader;
SubHeader.Value = string.Format("Camp Year: {0}. Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
iRow += 4;
CellStyle styleTableTitle = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
styleTableTitle.Font.Size = 16 * 20;
styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
styleTableTitle.Font.Weight = ExcelFont.BoldWeight;
CellStyle styleTableDataRow = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
CellStyle styleTableDataRowCampName = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Left
};
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
// Data Content of report
DataSet dsModified = GetDataFromRepository();
// Get rid of first columns for other tables except first one
for (int i = 1; i < dsModified.Tables.Count; i++)
{
dsModified.Tables[i].Columns.Remove("Camp Name");
}
int table_column_count = dsModified.Tables[0].Columns.Count;
int times = 0, current_starting_column = 0;
foreach (DataTable dt in dsModified.Tables)
{
if (times == 1)
{
BEGIN_COLUMN_INDEX += 1;
}
// Get the header location
current_starting_column = BEGIN_COLUMN_INDEX + times * table_column_count;
int temp_column = 0;
if (times == 0)
{
temp_column = current_starting_column + 1;
}
else
{
temp_column = current_starting_column;
}
// Table Title
CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
TableTitle.Merged = true;
TableTitle.Value = dt.TableName;
TableTitle.Style = styleTableTitle;
// this creats the real table
ws.InsertDataTable(dt, iRow, current_starting_column, true);
// loop through each column and set style accordingly
for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
{
ws.Cells[iRow, i].Style = styleTableHeaderColumns;
ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;
ws.Columns[i].Width = 11 * 256;
// first column of first table, e.g. camp naem
if (times == 0)
{
if (i == current_starting_column)
{
ws.Columns[i].Width = 55 * 256; // camp/program name
}
else if (i == current_starting_column + 1)
{
ws.Columns[i].Width = 15 * 256;
}
}
else if (i == current_starting_column)
{
ws.Columns[i].Width = 15 * 256;
}
}
// Set the data row style
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Rows[j].Style = styleTableDataRow;
}
// left justify the camp names
if (times == 0)
{
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
}
}
times++;
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}SessionLengthByCamp.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
protected void btnExport_Click(object sender, EventArgs e)
{
if (!Module.PermissionCheck(Permission.ACTION_EXPORTAGENCY, UserIdentity))
{
ShowError(Resources.textDeniedFunction);
return;
}
int count;
IList data = Module.vAgencyGetByQueryString(Request.QueryString, 0, 0, out count, UserIdentity);
string tpl = Server.MapPath("/Modules/Sails/Admin/ExportTemplates/Danh_sach_dai_ly.xls");
ExcelFile excelFile = new ExcelFile();
excelFile.LoadXls(tpl);
ExcelWorksheet sheet = excelFile.Worksheets[0];
// Dòng dữ liệu đầu tiên
const int firstrow = 7;
int crow = firstrow;
sheet.Rows[crow].InsertCopy(count - 1, sheet.Rows[firstrow]);
foreach (vAgency agency in data)
{
sheet.Cells[crow, 0].Value = crow - firstrow + 1;
sheet.Cells[crow, 1].Value = agency.Name;
sheet.Cells[crow, 2].Value = agency.Phone;
sheet.Cells[crow, 3].Value = agency.Fax;
sheet.Cells[crow, 4].Value = agency.Address;
sheet.Cells[crow, 5].Value = agency.Email;
if (agency.Sale != null)
{
sheet.Cells[crow, 6].Value = agency.Sale.UserName;
}
if (agency.LastBooking.HasValue)
{
sheet.Cells[crow, 7].Value = agency.LastBooking.Value.ToString("dd/MM/yyyy");
}
else
{
sheet.Cells[crow, 7].Value = "Never";
}
switch (agency.ContractStatus)
{
case 0:
sheet.Cells[crow, 8].Value = "No";
break;
case 1:
sheet.Cells[crow, 8].Value = "Pending";
break;
case 2:
sheet.Cells[crow, 8].Value = "Yes";
break;
}
crow++;
}
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("content-disposition",
"attachment; filename=agencylist.xls");
MemoryStream m = new MemoryStream();
excelFile.SaveXls(m);
Response.OutputStream.Write(m.GetBuffer(), 0, m.GetBuffer().Length);
Response.OutputStream.Flush();
Response.OutputStream.Close();
m.Close();
Response.End();
}
private static void RenderSheetList(ExcelFile excelFile)
{
_selectedSheet = EditorGUILayout.Popup(_selectedSheet, excelFile.Sheets.Select(s => s.Name).ToArray());
}
/// <summary>
///
/// </summary>
/// <param name="datastore"></param>
/// <param name="keys">les parametres utilisés dans la requete</param>
/// <param name="values"></param>
/// <param name="superParamKey">mot cle utilise dans les parametres de nom de fichiers etc...</param>
/// <param name="superParamValue">la valeur du mot cle</param>
private void output(string datastore, string[] keys = null, string[] values = null,
string superParamKey = null, string superParamValue = null)
{
BP2SText bp2stext = null;
string sql = CommandLine["sql"];
DataSet DS = new DataSet();
// fichier de la requete SQL utilisée pour extraire les données
if (sql != null)
{
// formater les paramètres:
SQLrequest p = null;
try
{
// tester si il y a des pararametres personnalise
if (keys != null && keys.Length > 0)
{
IntegratorBatch.InfoLogger.Debug("Lecture d une requete avec parametres : " + keys);
p = new SQLrequest(sql, datastore, keys, values);
}
else
{
p = new SQLrequest(sql, datastore);
}
p.Execute(out DS);
}
catch (Exception e)
{
IntegratorBatch.ExceptionLogger.Fatal("Impossible d executer la requete " + sql, e);
throw e;
}
}
else
{
Console.WriteLine(usage());
IntegratorBatch.ExceptionLogger.Fatal("Parametre obligatoire -sql inexistant");
// sortie car aucune requete à jouer
return;
}
//------------------------------------------------------------------------------------------
// declencher un calcul de BondPricer
string bondPricer = CommandLine["bondPricer"];
if (bondPricer != null)
{
BondPricer.OPTION opt;
switch (bondPricer)
{
case "DURATION": opt = BondPricer.OPTION.DURATION; break;
case "ACCRUEDINTEREST": opt = BondPricer.OPTION.ACCRUEDINTEREST; break;
case "CLEANPRICE": opt = BondPricer.OPTION.CLEANPRICE; break;
case "DIRTYPRICE": opt = BondPricer.OPTION.DIRTYPRICE; break;
default: opt = BondPricer.OPTION.ALL; break;
}
BondPricer bp = new BondPricer();
DS = bp.Execute(DS, opt);
}
//------------------------------------------------------------------------------------------
if (CommandLine["bp2s"] != null)
{
bp2stext = new BP2SText();
string bp2sValid = (CommandLine["bp2sregex"] == null ? @"Resources/Consumer/BP2StextValidation.txt" : CommandLine["bp2sregex"]);
IntegratorBatch.InfoLogger.Debug("Lecture d un fichier de validation des champs (regex) " + bp2sValid);
string bp2s = CommandLine["bp2s"];
bp2stext.ValidationPath = bp2sValid;
IntegratorBatch.InfoLogger.Debug("Configuration pour une sortie BP2S " + bp2s);
bp2s = bp2s.Replace("YYYYMMDD", now.ToString("yyyyMMdd"));
// remplacer le mot cle par sa valeur
if (superParamKey != null)
{
bp2s = bp2s.Replace(superParamKey, superParamValue);
}
bp2stext.CreateFile(DS, bp2s, now);
}
//------------------------------------------------------------------------------------------
string excel = CommandLine["xls"];
if (excel != null)
{
excel = excel.Replace("YYYYMMDD", now.ToString("yyyyMMdd"));
// remplacer le mot cle par sa valeur
if (superParamKey != null)
{
excel = excel.Replace(superParamKey, superParamValue);
}
IntegratorBatch.InfoLogger.Debug("Configuration pour une sortie Excel " + excel);
ExcelFile.CreateWorkbook(DS, excel);
}
//------------------------------------------------------------------------------------------
string openXMLExcel = CommandLine["oxlsx"];
if (openXMLExcel != null)
{
openXMLExcel = openXMLExcel.Replace("YYYYMMDD", now.ToString("yyyyMMdd"));
// remplacer le mot cle par sa valeur
if (superParamKey != null)
{
openXMLExcel = openXMLExcel.Replace(superParamKey, superParamValue);
}
IntegratorBatch.InfoLogger.Debug("Configuration pour une sortie Open XML Excel " + openXMLExcel);
// tester si il y a des pararametres personnalise
string date = CommandLine["#date"] == null?now.ToString("yyyyMMdd") : CommandLine["#date"];
string sheetNames = CommandLine["#names"];
string style = CommandLine["#style"];
string template = CommandLine["#template"];
string graph = CommandLine["#graph"];
bool monoSheetFlag = ((CommandLine["#monoSheetFlag"] == null || CommandLine["#monoSheetFlag"] == "F" || CommandLine["#monoSheetFlag"] == "N") ? false : true);
OpenXMLFile.CreateExcelWorkbook(DS, openXMLExcel, style, template, graph, date, sheetNames.Split(';'), monoSheetFlag);
}
//------------------------------------------------------------------------------------------
string csv = CommandLine["csv"];
if (csv != null)
{
string fileNames = CommandLine["#names"];
if (fileNames != null)
{
fileNames = fileNames.Replace("YYYYMMDD", now.ToString("yyyyMMdd"));
}
else
{
fileNames = "";
}
// remplacer le mot cle par sa valeur
if (superParamKey != null)
{
fileNames = fileNames.Replace(superParamKey, superParamValue);
}
CSVFile.WriteToCSV(DS, csv, fileNames);
}
//------------------------------------------------------------------------------------------
//Si include et exclude sont vide : copie la BDD en entier
//Si include et exclude non vide : aucune table ne sera copiée
//Si include est vide : copie tout sauf les tables comprises dans exclude
//Si exclude est vide : copie uniquement les tables dans include
string bdd = CommandLine["dbCopy"];
if (bdd != null)
{
MSDBIntegration.InfoLogger = IntegratorBatch.InfoLogger;
MSDBIntegration.ExceptionLogger = IntegratorBatch.ExceptionLogger;
string connection1 = CommandLine["#connectionSource"];
string connection2 = CommandLine["#connectionDest"];
// une seule liste autorisée
List <string> liste = null;
ListeMode mode;
if (CommandLine["#include"] != null)
{
liste = CommandLine["#include"].Split(';').ToList();
mode = ListeMode.Include;
}
else if (CommandLine["#include"] != null)
{
liste = CommandLine["#exclude"].Split(';').ToList();
mode = ListeMode.Exclude;
}
else
{
mode = ListeMode.Aucune;
}
List <string> nomTable = null;
// si il y a une option -sql , le resultat de la requete sera mis dans la ou les tables nommées nomtable
if (sql != null)
{
nomTable = CommandLine["#nomTable"].Split(';').ToList();
DBCopy.DataBaseCopy(connection1, connection2, DS, nomTable);
}
else
{
//TODO
}
}
//------------------------------------------------------------------------------------------
}
static public void HelpFill(SpreadsheetDocument spreadSheetDocument, string sheetFromFile, ExcelFile file, ref List <ProductsOfInterest> products, Report report)
{
DataTable dt = DataTableFromExcel.GetDataTableFromSpreadSheet(spreadSheetDocument, sheetName: sheetFromFile);
List <string> productsFromSheet = new List <string>();
foreach (DataRow row in dt.Rows)
{
try // возможно исключение "нельзя привести DBNull к string"
{
if ((string)row[2] != "") // В третьем столбце интересующие продукты
{
if ((string)row[2] == "-") // Клиент не интересовался продуктами
{
continue;
}
string product = (string)row[2];
string productWithoutDigits = "";
for (int i = 0; i < product.Length; i++)
{
if (!char.IsDigit(product[i]))
{
productWithoutDigits += product[i];
}
}
string[] productWithoutDigitsSeparated = productWithoutDigits.Split(stringSeparators, StringSplitOptions.None);
foreach (string prod in productWithoutDigitsSeparated)
{
productsFromSheet.Add(prod);
}
}
}
catch (Exception e)
{ }
}
products.Add(new ProductsOfInterest()
{
FilePath = file.FilePath,
SheetName = sheetFromFile,
Products = productsFromSheet,
vendors = (report.Vendors != null) ? new Dictionary <string, int>(report.Vendors) : new Dictionary <string, int>(),
areas = (report.Areas != null) ? new Dictionary <string, int>(report.Areas) : new Dictionary <string, int>()
});
}
/// <summary>
/// Метод выборки значений из Excell
/// </summary>
/// <param name="path">Путь к документу</param>
/// <param name="date">Дата за которую необходимо получить значения</param>
/// <returns>Возвращает таблицу со значениями</returns>
private static DataTable getCSV(string path, DateTime date, out int err)
{
err = -1;
DataTable dataTableRes = new DataTable();
string name_worksheet = string.Empty;
DataRow[] rows = new DataRow[25];
int col_worksheet = 0;
bool start_work = false;
for (int i = 0; i < m_col_name.Length; i++)
{
dataTableRes.Columns.Add(m_col_name[i]);//Добавление колонок в таблицу
}
//Открыть поток чтения файла...
try
{
ExcelFile excel = new ExcelFile();
excel.LoadXls(path); //загружаем в созданный экземпляр документ Excel
name_worksheet = "Расчет " + mounth[date.Month]; //Генерируем имя необходимого листа в зависимости от переданной даты
foreach (ExcelWorksheet w in excel.Worksheets) //Перебор листов
{
if (w.Name.Equals(name_worksheet, StringComparison.InvariantCultureIgnoreCase)) //Если имя совпадает с сгенерируемым нами то
{
start_work = true;
foreach (ExcelRow r in w.Rows) //перебор строк документа
{
if (r.Cells[0].Value != null) //Если значение строки не пусто то
{
if (r.Cells[0].Value.ToString() == date.Date.ToString()) //Если дата в строке совпадает с переданной то
{
for (int i = 0; i < 24; i++) //Перебор ячеек со значениями по часам
{
object[] row = new object[3];
row[0] = i.ToString(); //Час
if (r.Cells[i + 2].Value == null) //Если ячейка пуста то
{
row[(int)INDEX_COLUMN.Power] = 0.ToString("F2"); //0 в формате (0.00)
}
else
{
row[(int)INDEX_COLUMN.Power] = r.Cells[i + 2].Value.ToString().Trim();//Значение ПБР
}
if (w.Rows[r.Index + 1].Cells[i + 2].Value == null)
{
row[(int)INDEX_COLUMN.Temperature] = string.Empty;
}
else
{
row[(int)INDEX_COLUMN.Temperature] = w.Rows[r.Index + 1].Cells[i + 2].Value.ToString().Trim(); //Значение температуры
}
dataTableRes.Rows.Add(row); //Добавляем строку в таблицу
}
}
}
if (dataTableRes.Rows.Count >= 24) //Если количестко строк стало равным ли больше 24 то прерываем перебор
{
//err = (int)INDEX_ERR.NOT_ERR;
break;
}
}
}
else
{
if (dataTableRes.Rows.Count == 0 && col_worksheet == excel.Worksheets.Count - 1 && start_work == true)
{
err = (int)INDEX_ERR.NOT_DATA;
break;
}
else
{
if (dataTableRes.Rows.Count >= 24 && start_work == true)
{
err = (int)INDEX_ERR.NOT_ERR;
break;
}
else
{
err = (int)INDEX_ERR.NOT_WORKSHEET;
}
}
}
col_worksheet++;
}
}
catch (Exception e)
{
Logging.Logg().Error("PanelAdminLK : getCSV - ошибка при открытии потока" + e.Message, Logging.INDEX_MESSAGE.NOT_SET);
}
return(dataTableRes);
}
internal static void DrawPlainText(IFlxGraphics Canvas, ExcelFile Workbook, TShapeProperties ShProp, RectangleF Coords, TShadowInfo ShadowInfo, TClippingStyle Clipping, float Zoom100)
{
string Text = GetGeoText(ShProp);
if (Text == null)
{
return;
}
Text = Text.Replace("\n", String.Empty);
string[] Lines = Text.Split('\r');
if (Lines == null || Lines.Length <= 0)
{
return;
}
int LinesLength = Lines[Lines.Length - 1].Length == 0? Lines.Length - 1: Lines.Length; //Last line is an empty enter.
if (LinesLength <= 0)
{
return;
}
using (Font TextFont = GetGeoFont(ShProp))
{
using (Pen pe = GetPen(ShProp, Workbook, ShadowInfo))
{
Canvas.SaveTransform();
try
{
float LineGap = Canvas.FontLinespacing(TextFont);
SizeF[] Sizes = new SizeF[LinesLength];
Sizes[0] = Canvas.MeasureStringEmptyHasHeight(Lines[0], TextFont);
Sizes[0].Height -= LineGap; //Linespacing is not included here.
SizeF sz = Sizes[0];
for (int i = 1; i < LinesLength; i++)
{
Sizes[i] = Canvas.MeasureStringEmptyHasHeight(Lines[i], TextFont);
if (Sizes[i].Width > sz.Width)
{
sz.Width = Sizes[i].Width;
}
sz.Height += Sizes[i].Height;
}
if (sz.Width <= 0 || sz.Height <= 0 || Coords.Width <= 0 || Coords.Height <= 0)
{
return;
}
float rx = Coords.Width / sz.Width;
float ry = Coords.Height / sz.Height;
Canvas.Scale(rx, ry);
using (Brush br = GetBrush(new RectangleF(Coords.Left / rx, Coords.Top / ry, sz.Width, sz.Height), ShProp, Workbook, ShadowInfo, Zoom100)) //Mast be selected AFTER scaling, so gradients work.
{
float y = LineGap;
for (int i = 0; i < LinesLength; i++)
{
y += Sizes[i].Height;
float x = (sz.Width - Sizes[i].Width) / 2f;
Canvas.DrawString(Lines[i], TextFont, pe, br, Coords.Left / rx + x, Coords.Top / ry + y);
}
}
}
finally
{
Canvas.ResetTransform();
}
}
}
}
public SPT(Model model, ExcelFile excelFile) : base(model, excelFile)
{
name = "Rule of Shortest Processing Time";
}
internal static Brush GetBrush(RectangleF Coords, TShapeProperties ShProp, ExcelFile Workbook, TShadowInfo ShadowInfo, float Zoom100)
{
return(DrawShape.GetBrush(Coords, ShProp, Workbook, ShadowInfo, Zoom100));
}
static void CommandHandler(string S)
{
try
{
var commandName = string.Join("", S.TakeWhile(ch => ch != ' '));
if (commandName == "calc")
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var excelFile = new ExcelFile();
var workSheet = excelFile.Worksheets.Add("NestingResults");
var rowNum = 0;
var jsonConverter = new NestingConverter();
jsonConverter.SaveFromNfToDbs();
var dirs = Directory.GetDirectories(AppDomain.CurrentDomain.BaseDirectory);
foreach (var dir in dirs)
{
var fileName = dir + "\\nest_000.nres";
if (File.Exists(fileName))
{
rowNum++;
var domainSize = 0d;
var maxSize = 0d;
var listY = 0d;
var nfRatio = 0d;
using (var file = new StreamReader(fileName))
{
while (!file.EndOfStream)
{
var line = file.ReadLine();
if (string.IsNullOrEmpty(line))
{
continue;
}
var split = line.Split(' ').ToList();
if (split.FirstOrDefault() == "Material")
{
double.TryParse(split.LastOrDefault()?.Replace(".", ","), out nfRatio);
continue;
}
if (split.Count < 2)
{
continue;
}
double.TryParse(split.FirstOrDefault()?.Replace(".", ","), out double X);
double.TryParse(split.FirstOrDefault()?.Replace(".", ","), out double Y);
if (X > domainSize)
{
domainSize = X;
}
if (X > maxSize && Math.Abs(X - domainSize) > 0.1)
{
maxSize = X;
}
if (listY <= 0 && Y > 0)
{
listY = Y;
}
}
}
var dirName = dir.Split('\\').ToList().Last();
var loader = new TaskLoader();
var task = loader.LoadTFlexTask(dirName + ".tfnesting");
var tFlexRatio = task.Results.FirstOrDefault()?.KIM ?? 0;
CalculateDbsNesting(dirName);
var siriusRatio = "";
var resultReportPath = "report\\report_result.htm";
if (File.Exists(resultReportPath))
{
var resultsHtml = File.ReadAllText(resultReportPath);
siriusRatio = resultsHtml?.Split(' ').LastOrDefault()?.Replace("</b></body></html>\r\n", "");
}
Console.WriteLine(
$"Size: {domainSize}x{listY} ({domainSize * listY}), fact size: {maxSize}x{listY} ({maxSize * listY}), NF ratio: {nfRatio}, TFlex ratio: {tFlexRatio}");
workSheet.Cells[rowNum, 0].Value = dirName;
workSheet.Cells[rowNum, 1].Value = $"{domainSize}x{listY}";
workSheet.Cells[rowNum, 2].Value = task.Parts.Sum(i => i.Count);
workSheet.Cells[rowNum, 4].Value = nfRatio;
workSheet.Cells[rowNum, 5].Value = domainSize - maxSize;
workSheet.Cells[rowNum, 6].Value = tFlexRatio;
workSheet.Cells[rowNum, 8].Value = siriusRatio;
}
}
excelFile.Save("results.xlsx");
}
}
catch (Exception ex)
{
Console.Write($"{ex.Message}\n{ex.StackTrace}\n");
}
}
internal static Pen GetPen(TShapeProperties ShProp, ExcelFile Workbook, TShadowInfo ShadowInfo)
{
return(DrawShape.GetPen(ShProp, Workbook, ShadowInfo));
}
public string SetRefQuotation(ModelViewUserG objCred, ModelViewBilling obj)
{
var objRepository = new RepositoryOrder();
var NegocioOrden = new BusinessOrder();
var objAlerta = new BusinessNotification();
var NegocioUsuario = new BusinessUsers();
var NegocioCliente = new BusinessClient();
var NegocioBase = new BusinessInstalledBase();
var NegocioProducto = new BusinessProduct();
var NegocioFallas = new BusinessCodeFailure();
var NegocioBOM = new BusinessBuildOfMaterial();
var NegocioCotizacion = new BusinessQuotation();
var NegocioCotizacionDetalle = new BusinessRefsellDetail();
var NegocioCFP = new BusinessCodeFailureByProduct();
var NegocioCF = new BusinessCodeFailure();
var NegocioLugarCompra = new BusinessShopPlace();
var NegocioEmpleado = new BusinessEmployee();
var NegocioMonitor = new BusinessVisit();
var NegocioHistorico = new BusinessHistory();
var NegocioGarantia = new BusinessGuaranty();
var NegocioPayment = new BusinessPayment();
var NegocioInvoice = new BusinessInvoice();
var NegocioRefsell = new BusinessRefsell();
var dataUsuario = NegocioUsuario.GetUserByToken(objCred.TokenUser);
if (objCred.TokenApp != GlobalConfiguration.TokenWEB)
{
if (objCred.TokenApp != GlobalConfiguration.TokenMobile)
{
throw new Exception("TokenInvalid");
}
}
if (dataUsuario == null)
{
throw new Exception("UserPasswordInvalid");
}
var empleado = NegocioEmpleado.GetByUserID(dataUsuario.UserID);
var orden = NegocioOrden.GetByOrderID(obj.ODS);
var cliente = NegocioCliente.GetByID(orden.FK_ClientID);
var lugarcompra = NegocioLugarCompra.GetByShopPlaceID(obj.FK_ShopPlaceID.Value);
var pro = NegocioProducto.GetByID(obj.FK_ProductID.Value);
SpreadsheetInfo.SetLicense("EQU2-3K5L-UZDC-SDYN");
string Origin = GlobalConfiguration.MabeAttachmentsLocal + "FormatoVentaRefacciones.xlsx";
List <string> result = obj.EMails.Split(new char[] { ';' }).ToList();
ExcelFile ef = ExcelFile.Load(Origin);
ExcelWorksheet ws = ef.Worksheets[0];
string Folio = "";
ws.Cells["L7"].Value = obj.ODS;
ws.Cells["L9"].Value = cliente.ClientID;
ws.Cells["L11"].Value = cliente.FirstName.ToUpper() + " " + cliente.LastName.ToUpper();
ws.Cells["L13"].Value = pro.Model + " / " + pro.ProductName.ToUpper();
ws.Cells["U13"].Value = DateTime.Today.ToString("dd-MM-yyyy");
ws.Cells["L80"].Value = obj.ODS;
int cantidad = 30;
int cantidad1 = 31;
foreach (var jko in obj.BillingDetails)
{
if (jko.SparePartsDescription.EndsWith("-R"))
{
Folio = jko.SparePartsDescription;
ws.Cells["L6"].Value = Folio;
ws.Cells["L79"].Value = Folio;
}
else
{
if (cantidad == 60)
{
cantidad = 85;
cantidad1 = 86;
}
var bom = NegocioBOM.GetByID(jko.ProductID);
if (bom != null)
{
ws.Cells["L" + cantidad1.ToString()].Value = bom.SparePartsID;
ws.Cells["L" + cantidad.ToString()].Value = bom.SparePartDescription;
}
else
{
ws.Cells["L" + cantidad1.ToString()].Value = jko.RefManID;
ws.Cells["L" + cantidad.ToString()].Value = jko.SparePartsDescription;
}
ws.Cells["J" + cantidad.ToString()].Value = jko.Quantity;
ws.Cells["T" + cantidad.ToString()].Value = Convert.ToDouble(jko.Price);
ws.Cells["V" + cantidad.ToString()].Value = Convert.ToDouble(jko.Totals);
cantidad = cantidad + 3;
cantidad1 = cantidad1 + 3;
}
}
double subtotal = Convert.ToDouble(obj.SubTotal.Substring(1));
double iva = Convert.ToDouble(obj.IVA.Substring(1));
double total = Convert.ToDouble(obj.Total.Substring(1));
double subtotalref = subtotal;
string totalletras = NegocioOrden.enletras(total.ToString());
ws.Cells["M16"].Value = subtotalref;
ws.Cells["M17"].Value = subtotal;
ws.Cells["M18"].Value = iva;
ws.Cells["U17"].Value = total;
ws.Cells["M19"].Value = totalletras;
string file = "Cotización_" + Folio + ".pdf";
string quotion = "CotizacionesRefaccion_" + DateTime.Today.ToString("yyyyMMdd");
string Destiny = GlobalConfiguration.MabeAttachmentsLocal + quotion + "/" + file;
if (obj.BillingDetails.Count < 11)
{
ws.NamedRanges.SetPrintArea(ws.Cells.GetSubrange("J1", "W74"));
}
string Cotizaciones = new DirectoryInfo(GlobalConfiguration.MabeAttachmentsLocal).ToString() + quotion;
if (!(Directory.Exists(Cotizaciones)))
{
Directory.CreateDirectory(Cotizaciones);
}
ef.Save(Destiny);
string URL = GlobalConfiguration.urlRequest + "Content/Attachments/" + quotion + "/" + file;
var cotizacion = NegocioCotizacion.GetByOrderFolio(orden.PK_OrderID, Folio);
var Payment = NegocioPayment.GetPolicyPayment(orden.PK_OrderID, Folio);
var Invoice = NegocioInvoice.GetPolicyInvoice(orden.PK_OrderID, Folio);
var LsVenta = NegocioRefsell.GetByFolio(orden.PK_OrderID, Folio);
if (obj.EMails != "")
{
if (cotizacion == null)
{
cotizacion = NegocioCotizacion.Insert(orden.PK_OrderID, subtotal.ToString(), iva.ToString(), total.ToString(), Folio, URL, obj.EstimatedTipe, empleado[0].PK_EmployeeID);
}
else
{
cotizacion.Folio = Folio;
cotizacion.IVA = iva.ToString();
cotizacion.SubTotal = subtotal.ToString();
cotizacion.Total = total.ToString();
cotizacion.URL = URL;
cotizacion.ModifyDate = DateTime.UtcNow;
cotizacion.TypeQuotation = obj.EstimatedTipe;
cotizacion.FK_EmployeeID = empleado[0].PK_EmployeeID;
NegocioCotizacion.Update(cotizacion);
}
string sb = File.ReadAllText(GlobalConfiguration.LocateBodyMail + "NotificationCotizacion.txt");
string lugCom = obj.ShopDate != null ? obj.ShopDate : "";
sb = sb.Replace("#%Nombre%#", cliente.FirstName + " " + cliente.LastName);
sb = sb.Replace("#%OrderID%#", obj.ODS);
sb = sb.Replace("#%Folio%#", Folio);
sb = sb.Replace("#%Modelo%#", pro.Model);
sb = sb.Replace("#%Descripcion%#", pro.ProductName.ToUpper());
sb = sb.Replace("#%Serie%#", obj.SerialNumber);
sb = sb.Replace("#%Fecha%#", lugCom);
sb = sb.Replace("#%Lugar%#", lugarcompra.ShopPlace1);
//objAlerta.SendMails(result, "Cotización ServiPlus", sb.ToString(), Destiny);
objAlerta.SendMailExchange(GlobalConfiguration.exchangeUserCotiza, GlobalConfiguration.exchangePwdCotiza, result, "Cotización ServiPlus", sb.ToString(), Destiny);
}
else
{
if (LsVenta.PK_RefSellID == 0)
{
if (cotizacion == null)
{
cotizacion = NegocioCotizacion.Insert(orden.PK_OrderID, subtotal.ToString(), iva.ToString(), total.ToString(), Folio, URL, obj.EstimatedTipe, empleado[0].PK_EmployeeID);
}
else
{
cotizacion.Folio = Folio;
cotizacion.IVA = iva.ToString();
cotizacion.SubTotal = subtotal.ToString();
cotizacion.Total = total.ToString();
cotizacion.URL = URL;
cotizacion.ModifyDate = DateTime.UtcNow;
cotizacion.TypeQuotation = obj.EstimatedTipe;
cotizacion.FK_EmployeeID = empleado[0].PK_EmployeeID;
NegocioCotizacion.Update(cotizacion);
}
var NewRefSell = new EntityRefSell();
var NuevaVenta = new EntityRefSell();
NewRefSell.FK_OrderID = orden.PK_OrderID;
NewRefSell.FK_ClientID = cliente.PK_ClientID;
NewRefSell.FK_EmployeeID = empleado[0].PK_EmployeeID;
NewRefSell.FK_PaymentID = Payment.PK_PaymentID;
NewRefSell.FK_Invoice_ID = Invoice.InvoicingID;
NewRefSell.FK_ProductID = obj.FK_ProductID.Value;
NewRefSell.FK_ShopPlace = obj.FK_ShopPlaceID.Value;
NewRefSell.FK_QuotationID = cotizacion.PK_QuotationID;
NewRefSell.IDRefSell = Folio;
NuevaVenta = NegocioRefsell.Insert(NewRefSell);
}
else
{
LsVenta.FK_OrderID = orden.PK_OrderID;
LsVenta.FK_ClientID = cliente.PK_ClientID;
LsVenta.FK_EmployeeID = empleado[0].PK_EmployeeID;
LsVenta.FK_PaymentID = Payment.PK_PaymentID;
LsVenta.FK_Invoice_ID = Invoice.InvoicingID;
LsVenta.FK_QuotationID = LsVenta.FK_QuotationID;
LsVenta.FK_ProductID = obj.FK_ProductID.Value;
LsVenta.FK_ShopPlace = obj.FK_ShopPlaceID.Value;
NegocioRefsell.Update(LsVenta);
if (cotizacion == null)
{
cotizacion = NegocioCotizacion.Insert(orden.PK_OrderID, subtotal.ToString(), iva.ToString(), total.ToString(), Folio, URL, obj.EstimatedTipe, empleado[0].PK_EmployeeID);
}
else
{
cotizacion.Folio = Folio;
cotizacion.IVA = iva.ToString();
cotizacion.SubTotal = subtotal.ToString();
cotizacion.Total = total.ToString();
cotizacion.URL = URL;
cotizacion.ModifyDate = DateTime.UtcNow;
cotizacion.TypeQuotation = obj.EstimatedTipe;
cotizacion.FK_EmployeeID = empleado[0].PK_EmployeeID;
NegocioCotizacion.Update(cotizacion);
}
}
string[] lstADR = { "T002", "T012", "T024" };
foreach (var jko in obj.BillingDetails)
{
string ADRAnt = "";
int ADRCount = 0;
bool AddFlete = false;
if (jko.SparePartsDescription != Folio)
{
switch (jko.SparePartsDescription)
{
case "Fletes":
{
var DetailCotation2 = NegocioCotizacionDetalle.GetList(cotizacion.PK_QuotationID);
foreach (var items in DetailCotation2)
{
if (lstADR.Contains(items.Origen))
{
if (ADRAnt != items.Origen)
{
ADRAnt = items.Origen;
ADRCount++;
if (ADRCount < 3)
{
AddFlete = true;
}
else
{
AddFlete = false;
}
}
if (AddFlete)
{
items.Flete = true;
items.CostoFlete = jko.Price;
NegocioCotizacionDetalle.Update(items);
AddFlete = false;
}
}
}
}
break;
default:
var newDetailquotion = NegocioCotizacionDetalle.GetDetail(cotizacion.PK_QuotationID, jko.ProductID);
if (newDetailquotion == null)
{
NegocioCotizacionDetalle.Insert(cotizacion.PK_QuotationID, jko.ProductID, jko.Quantity.ToString(), jko.RefManID, jko.Origins, jko.Price);
}
break;
}
}
}
var Refacciones = new BusinessMabe().Orden_Venta(obj.ODS, obj.ServiceTypes, Folio);
}
return(obj.ODS);
}
public string ExportExcel(DataTable dt, string type, string selectDepth, string selectEntityID)
{
ExcelFile excelFile = new ExcelFile();
string entityTitle = null;
var tmpath = HttpContext.Current.Server.MapPath("templet\\数据管理\\" + type + "1" + ".xls");
excelFile.LoadXls(tmpath);
ExcelWorksheet sheet = excelFile.Worksheets[0];
DataTable dtEntity = null; //单位信息表
dtEntity = SQLHelper.ExecuteRead(CommandType.Text, "SELECT [ID],[Name],[ParentID],[Depth] from [Entity]", "2");
String parentid = null;
switch (selectDepth)
{
case "1":
entityTitle = "台州交警局";
break;
case "2":
for (int h = 0; h < dtEntity.Rows.Count; h++)
{
if (dtEntity.Rows[h][0].ToString() == selectEntityID)
{
entityTitle = dtEntity.Rows[h][1].ToString();
break;
}
}
break;
case "3":
for (int h = 0; h < dtEntity.Rows.Count; h++)
{
if (dtEntity.Rows[h][0].ToString() == selectEntityID)
{
entityTitle = dtEntity.Rows[h][1].ToString();
parentid = dtEntity.Rows[h][2].ToString();
break;
}
}
for (int h = 0; h < dtEntity.Rows.Count; h++)
{
if (dtEntity.Rows[h][0].ToString() == parentid)
{
entityTitle = dtEntity.Rows[h][1].ToString() + entityTitle;
break;
}
}
break;
default:
break;
}
string title = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
for (int i = 0; i < dt.Rows.Count; i++)
{
ExcelRow row = sheet.Rows[i + 2];
row.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
row.Cells["A"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["B"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["C"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["D"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["E"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["F"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["G"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["H"].Style.Borders.SetBorders(MultipleBorders.Outside, Color.FromArgb(0, 0, 0), LineStyle.Thin);
row.Cells["A"].Value = i + 1;
for (int h = 0; h < dtEntity.Rows.Count; h++)
{
if (dtEntity.Rows[h][0].ToString() == dt.Rows[i]["EntityId"].ToString())
{
row.Cells["C"].Value = dtEntity.Rows[h][1].ToString();
parentid = dtEntity.Rows[h][2].ToString();
break;
}
}
for (int h = 0; h < dtEntity.Rows.Count; h++)
{
if (dtEntity.Rows[h][0].ToString() == parentid)
{
row.Cells["B"].Value = dtEntity.Rows[h][1].ToString();
break;
}
}
if (type == "1")
{
row.Cells["D"].Value = dt.Rows[i]["PlateNumber"].ToString();
}
else
{
row.Cells["D"].Value = dt.Rows[i]["DevId"].ToString();
}
if (dt.Rows[i]["在线时长"].ToString() != "")
{
row.Cells["F"].Value = Convert.ToDouble((Convert.ToDouble(dt.Rows[i]["在线时长"].ToString()) / 3600).ToString("0.00"));
}
else
{
row.Cells["F"].Value = 0;
}
row.Cells["E"].Value = dt.Rows[i]["Contacts"].ToString();
row.Cells["G"].Value = dt.Rows[i]["状态"].ToString();
row.Cells["H"].Value = title;
}
switch (type)
{
case "1":
sheet.Rows[0].Cells[0].Value = title + entityTitle + "车载视频日报详情";
break;
case "2":
sheet.Rows[0].Cells[0].Value = title + entityTitle + "对讲机日报详情";
break;
case "3":
sheet.Rows[0].Cells[0].Value = title + entityTitle + "拦截仪日报详情";
break;
case "4":
sheet.Rows[0].Cells[0].Value = title + entityTitle + "移动警务日报详情";
break;
case "5":
sheet.Rows[0].Cells[0].Value = title + entityTitle + "执法记录仪日报详情";
break;
default:
break;
}
tmpath = HttpContext.Current.Server.MapPath("upload\\sjtj\\" + sheet.Rows[0].Cells[0].Value + ".xls");
excelFile.SaveXls(tmpath);
return(sheet.Rows[0].Cells[0].Value + ".xls");
}
public string CreateContract(ModelViewContrat obj)
{
var NegocioOrden = new BusinessOrder();
var NegocioCliente = new BusinessClient();
var NegocioContrato = new BusinessContrat();
var objAlerta = new BusinessNotification();
var Cliente = NegocioCliente.GetByClientID(obj.NumbClient);
SpreadsheetInfo.SetLicense("EQU2-3K5L-UZDC-SDYN");
string Origin = GlobalConfiguration.MabeAttachmentsLocal + "FormatoContrato.xlsx";
List <string> result = obj.EMails.Split(new char[] { ';' }).ToList();
ExcelFile ef = ExcelFile.Load(Origin);
ExcelWorksheet ws = ef.Worksheets[0];
ws.Cells["N15"].Value = obj.Folio;
//ws.Cells["L7"].Value = obj.ODS;
ws.Cells["M20"].Value = obj.Period;
ws.Cells["M21"].Value = obj.StartDate;
ws.Cells["T21"].Value = obj.EndDate;
ws.Cells["M30"].Value = obj.NumbClient;
ws.Cells["M31"].Value = Cliente.FirstName + " " + Cliente.LastName.ToUpper();
ws.Cells["M32"].Value = Cliente.StreetAddress;
ws.Cells["M33"].Value = Cliente.BoroughAddress;
ws.Cells["M34"].Value = Cliente.MunicipalityAddress;
ws.Cells["M35"].Value = Cliente.PostalCodeAddress;
ws.Cells["M36"].Value = obj.Town;
ws.Cells["M37"].Value = Cliente.PhoneNumber1;
ws.Cells["M38"].Value = obj.Totals;
ws.Cells["M48"].Value = obj.Model;
ws.Cells["M49"].Value = obj.Description;
ws.Cells["M50"].Value = obj.Serie;
ws.Cells["R145"].Value = obj.Town;
ws.Cells["V145"].Value = DateTime.Today.ToString("yyyyMMdd");
string file = "ContratoPoliza" + obj.Folio + ".pdf";
string quotion = "Polizas_" + DateTime.Today.ToString("yyyyMMdd");
string Destiny = GlobalConfiguration.MabeAttachmentsLocal + quotion + "/" + file;
string Cotizaciones = new DirectoryInfo(GlobalConfiguration.MabeAttachmentsLocal).ToString() + quotion;
if (!(Directory.Exists(Cotizaciones)))
{
Directory.CreateDirectory(Cotizaciones);
}
ef.Save(Destiny);
string URL = GlobalConfiguration.urlRequest + "Content/Attachments/" + quotion + "/" + file;
var orden = NegocioOrden.GetByOrderID(obj.FK_OrderID);
var Contrato = NegocioContrato.GetByOrderFolio(orden.PK_OrderID, obj.Folio);
if (obj.EMails != "")
{
if (Contrato == null)
{
Contrato = NegocioContrato.Insert(orden.PK_OrderID, obj.Folio, URL);
}
else
{
Contrato.Fk_OrderID = orden.PK_OrderID;
Contrato.Folio = obj.Folio;
Contrato.Ruta = URL;
NegocioContrato.Update(Contrato);
}
string sb = File.ReadAllText(GlobalConfiguration.LocateBodyMail + "MailContratoPoliza.txt");
sb = sb.Replace("#%Nombre%#", Cliente.FirstName + " " + Cliente.LastName);
sb = sb.Replace("#%Folio%#", obj.Folio);
sb = sb.Replace("#%Modelo%#", obj.Model);
sb = sb.Replace("#%Descripcion%#", obj.Description.ToUpper());
sb = sb.Replace("#%Serie%#", obj.Serie);
sb = sb.Replace("#%Compra%#", DateTime.Today.ToString("dd/MM/yyyy"));
objAlerta.SendMailExchange(GlobalConfiguration.exchangeUserCotiza, GlobalConfiguration.exchangePwdCotiza, result, "Contrato Póliza", sb.ToString(), Destiny);
}
else
{
string Tipo = "Poliza";
var EnvioSMS = SendNotification(obj.PhoneNumber, Tipo, URL);
return(EnvioSMS);
}
return(URL);
}
private void AddData(ExcelFile Xls)
{
string TemplateFile = "template.xls";
if (cbXlsxTemplate.Checked)
{
if (!XlsFile.SupportsXlsx)
{
throw new Exception("Xlsx files are not supported in this version of the .NET framework");
}
TemplateFile = "template.xlsm";
}
// Open an existing file to be used as template. In this example this file has
// little data, in a real situation it should have as much as possible. (Or even better, be a report)
Xls.Open(Path.Combine(PathToExe, TemplateFile));
//Find the cell where we want to fill the data. In this case, we have created a named range "data" so the address
//is not hardcoded here.
TXlsNamedRange DataCell = Xls.GetNamedRange("Data", -1);
//Add a chart with totals
AddChart(DataCell, Xls);
//Note that "DataCell" will change because we inserted rows above it when creating the chart. But we will keep using the old one.
//Add the captions. This should probably go into the template, but in a dynamic environment it might go here.
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left, "Country");
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left + 1, "Quantity");
//Add a rectangle around the cells
TFlxApplyFormat ApplyFormat = new TFlxApplyFormat();
ApplyFormat.SetAllMembers(false);
ApplyFormat.Borders.SetAllMembers(true); //We will only apply the borders to the existing cell formats
TFlxFormat fmt = Xls.GetDefaultFormat;
fmt.Borders.Left.Style = TFlxBorderStyle.Double;
fmt.Borders.Left.Color = Color.Black;
fmt.Borders.Right.Style = TFlxBorderStyle.Double;
fmt.Borders.Right.Color = Color.Black;
fmt.Borders.Top.Style = TFlxBorderStyle.Double;
fmt.Borders.Top.Color = Color.Black;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Double;
fmt.Borders.Bottom.Color = Color.Black;
Xls.SetCellFormat(DataCell.Top - 1, DataCell.Left, DataCell.Top, DataCell.Left + 1, fmt, ApplyFormat, true); //Set last parameter to true so it draws a box.
//Freeze panes
Xls.FreezePanes(new TCellAddress(DataCell.Top, 1));
Random Rnd = new Random();
//Fill the data
int z = 0;
int OutlineLevel = 0;
for (int r = 0; r <= DataRows; r++)
{
//Fill the values.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left, Country[z % Country.Length]); //For non C# users, "%" means "mod" or modulus in other languages. It is the rest of the integer division.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left + 1, Rnd.Next(1000));
//Add the country to the outline
Xls.SetRowOutlineLevel(DataCell.Top + r, OutlineLevel);
//increment the country randomly
if (Rnd.Next(3) == 0)
{
z++;
OutlineLevel = 0; //Break the group and create a new one.
}
else
{
OutlineLevel = 1;
}
}
//Make the "+" signs of the outline appear at the top.
Xls.OutlineSummaryRowsBelowDetail = false;
//Collapse the outline to the first level.
Xls.CollapseOutlineRows(1, TCollapseChildrenMode.Collapsed);
//Add Data Validation for the first column, it must be a country.
TDataValidationInfo dv = new TDataValidationInfo(
TDataValidationDataType.List, //We will use a built in list.
TDataValidationConditionType.Between, //This parameter does not matter since it is a list. It will not be used.
"=\"" + GetCountryList() + "\"", //We could have used a range of cells here with the values (like "=C1..C4") Instead, we directly entered the list in the formula.
null, //no need for a second formula, not used in List
false,
true,
true, //Note that as we entered the data directly in FirstFormula, we need to set this to true
true,
"Unknown country",
"Please make sure that the country is in the list",
false, //We will not use an input box, so this is false and the 2 next entries are null
null,
null,
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left, DataCell.Top + DataRows, DataCell.Left), dv);
//Add Data Validation for the second column, it must be an integer between 0 and 1000.
dv = new TDataValidationInfo(
TDataValidationDataType.WholeNumber, //We will request a number.
TDataValidationConditionType.Between,
"=0", //First formula marks the first part of the "between" condition.
"=1000", //Second formula is the second part.
false,
false,
false,
true,
"Invalid Quantity",
null, //We will leave the default error message.
true,
"Quantity:",
"Please enter a quantity between 0 and 1000",
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left + 1, DataCell.Top + DataRows, DataCell.Left + 1), dv);
//Search country "Unknown" and replace it by "no".
//This does not make any sense here (we could just have entered "no" to begin)
//but it shows how to do it when modifying an existing file
Xls.Replace("Unknown", "no", TXlsCellRange.FullRange(), true, false, true);
//Autofit the rows. As we keep the row height automatic this will not show when opening in Excel, but will work when directly printing from FlexCel.
Xls.AutofitRowsOnWorkbook(false, true, 1);
Xls.Recalc(); //Calculate the SUMIF formulas so we can sort by them. Note that FlexCel automatically recalculates before saving,
//but in this case we haven't saved yet, so the sheet is not recalculated. You do not normally need to call Recalc directly.
//Sort the data. As in the case with replace, this does not make much sense. We could have entered the data sorted to begin
//But it shows how you can use the feature.
//Find the cell where the chart goes.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
Xls.Sort(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top + Country.Length, ChartRange.Left + 1),
true, new int[] { 2 }, new TSortOrder[] { TSortOrder.Descending }, null);
//Protect the Sheet
TSheetProtectionOptions Sp = new TSheetProtectionOptions(false); //Create default protection options that allows everything.
Sp.InsertColumns = false; //Restrict inserting columns.
Xls.Protection.SetSheetProtection("flexcel", Sp);
//Set a modify password. Note that this does *not* encrypt the file.
Xls.Protection.SetModifyPassword("flexcel", true, "flexcel");
Xls.Protection.OpenPassword = "******"; //OpenPasword is the only password that will actually encrypt the file, so you will not be able to open it with flexcel if you do not know the password.
//Select cell A1
Xls.SelectCell(1, 1, true);
}
protected void btnExport_Click(object sender, ImageClickEventArgs e)
{ // Create the workbook
SearchStudents();
ExcelFile ef = new ExcelFile();
ef.DefaultFontName = "Calibri";
ExcelWorksheet ws = ef.Worksheets.Add("Results");
FormatExcelExport(ws, dataTableStudents);
ef.Save(Response, "ExportData.xlsx");
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("TemplateUse.xlsx");
// Get template sheet.
ExcelWorksheet templateSheet = ef.Worksheets[0];
// Copy template sheet.
for (int i = 0; i < 4; i++)
{
ef.Worksheets.AddCopy("Invoice " + (i + 1), templateSheet);
}
// Delete template sheet.
ef.Worksheets.Remove(0);
DateTime startTime = DateTime.Now;
// Go to the first Monday from today.
while (startTime.DayOfWeek != DayOfWeek.Monday)
{
startTime = startTime.AddDays(1);
}
Random rnd = new Random();
// For each sheet.
for (int i = 0; i < 4; i++)
{
// Get sheet.
ExcelWorksheet ws = ef.Worksheets[i];
// Set some fields.
ws.Cells["J5"].SetValue(14 + i);
ws.Cells["J6"].SetValue(DateTime.Now);
ws.Cells["J6"].Style.NumberFormat = "m/dd/yyyy";
ws.Cells["D12"].Value = "ACME Corp";
ws.Cells["D13"].Value = "240 Old Country Road, Springfield, IL";
ws.Cells["D14"].Value = "USA";
ws.Cells["D15"].Value = "Joe Smith";
ws.Cells["E18"].Value = String.Format(startTime.ToShortDateString() + " until " + startTime.AddDays(11).ToShortDateString());
for (int j = 0; j < 10; j++)
{
ws.Cells[21 + j, 1].SetValue(startTime); // Set date.
ws.Cells[21 + j, 1].Style.NumberFormat = "dddd, mmmm dd, yyyy";
ws.Cells[21 + j, 4].SetValue(rnd.Next(6, 9)); // Work hours.
// Skip Saturday and Sunday.
startTime = startTime.AddDays(j == 4 ? 3 : 1);
}
// Skip Saturday and Sunday.
startTime = startTime.AddDays(2);
ws.Cells["B36"].Value = "Payment via check.";
}
ef.Save("Sheet Copying_Deleting.xlsx");
}
public async Task <BaseResult> ExportToXlsx(SmartTableParam param)
{
var rs = new BaseResult()
{
Result = Result.Success
};
SmartTableResult <WardItem> wards = Search(param).Result;
List <WardItem> list = wards.Items.ToList();
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Quản lí xã, phường");
var style = worksheet.Rows[0].Style;
style.Font.Weight = ExcelFont.BoldWeight;
style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[4].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[5].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[6].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[7].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[0].SetWidth(50, LengthUnit.Pixel);
worksheet.Columns[1].SetWidth(150, LengthUnit.Pixel);
worksheet.Columns[2].SetWidth(150, LengthUnit.Pixel);
worksheet.Columns[3].SetWidth(150, LengthUnit.Pixel);
worksheet.Columns[5].SetWidth(100, LengthUnit.Pixel);
worksheet.Columns[6].SetWidth(150, LengthUnit.Pixel);
worksheet.Columns[7].SetWidth(150, LengthUnit.Pixel);
worksheet.Cells["A1"].Value = "ID";
worksheet.Cells["B1"].Value = "Tên Xã/Phường";
worksheet.Cells["C1"].Value = "Huyện/Quận";
worksheet.Cells["D1"].Value = "Tỉnh/Thành Phố";
worksheet.Cells["E1"].Value = "Mã";
worksheet.Cells["F1"].Value = "Miền";
worksheet.Cells["G1"].Value = "Ngày Tạo";
worksheet.Cells["H1"].Value = "Ngày Cập Nhật";
worksheet.Tables.Add("Table1", "A1:H" + (list.Count + 1).ToString(), true);
for (int r = 1; r <= list.Count; r++)
{
var item = list[r - 1];
worksheet.Cells[r, 0].Value = item.Id;
worksheet.Cells[r, 1].Value = item.Name;
worksheet.Cells[r, 2].Value = item.District.Name;
worksheet.Cells[r, 3].Value = item.District.City.Name;
worksheet.Cells[r, 4].Value = item.Code;
worksheet.Cells[r, 5].Value = item.CityRealmStr;
worksheet.Cells[r, 6].Value = item.CreatedTimeDisplay;
worksheet.Cells[r, 7].Value = item.UpdatedTimeDisplay;
}
string fileName = "ExportFile.xlsx";
workbook.Save(fileName);
rs.Message = "Thành Công!";
return(rs);
}
/// <summary>
/// Convert the DEFAULT column width to pixels. This is different from <see cref="ColMult"/>, that goes in a column by column basis.
/// </summary>
public static int DefColWidthAdapt(int width, ExcelFile workbook)
{
return(Convert.ToInt32(Math.Round((width + 0.43) * 256 * 8f / 7f)));
}
private bool write_spreadsheet(string filename, DataSet space_utilized, string code1, string code2 )
{
try
{
string worksheet_name = code1;
if (code2.Length > 0)
worksheet_name = code1 + " intersect " + code2;
// Create the excel file and worksheet
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet excelSheet = excelFile.Worksheets.Add(worksheet_name);
excelFile.Worksheets.ActiveWorksheet = excelSheet;
// Create the header cell style
CellStyle headerStyle = new CellStyle();
headerStyle.HorizontalAlignment = HorizontalAlignmentStyle.Right;
headerStyle.FillPattern.SetSolid(Color.Yellow);
headerStyle.Font.Weight = ExcelFont.BoldWeight;
headerStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the title cell style
CellStyle titleStyle = new CellStyle();
titleStyle.HorizontalAlignment = HorizontalAlignmentStyle.Left;
titleStyle.Font.Weight = ExcelFont.BoldWeight;
titleStyle.Font.Size = 12 * 20;
// Set the two decimal places number format style
CellStyle numberStyle = new CellStyle();
numberStyle.NumberFormat = "#,#0.00";
numberStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Set the no decimal places number format style
CellStyle numberStyle2 = new CellStyle();
numberStyle2.NumberFormat = "#,#0";
numberStyle2.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Set the default style
CellStyle defaultStyle = new CellStyle();
defaultStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
excelSheet.Columns[0].Width = 4 * 256;
// Write the online utilized space
int column_selector = 1;
if (space_utilized.Tables[0].Rows.Count > 0)
{
excelSheet.Cells[1, column_selector].Value = "Total size of items online for " + worksheet_name + " by Month/Year";
excelSheet.Cells[1, column_selector].Style = titleStyle;
excelSheet.Cells[3, column_selector].Value = "Year";
excelSheet.Cells[3, column_selector].Style = headerStyle;
excelSheet.Cells[3, column_selector + 1].Value = "Month";
excelSheet.Cells[3, column_selector + 1].Style = headerStyle;
excelSheet.Cells[3, column_selector + 2].Value = "Added Online (KB)";
excelSheet.Cells[3, column_selector + 2].Style = headerStyle;
excelSheet.Cells[3, column_selector + 3].Value = "Total Online (MB)";
excelSheet.Cells[3, column_selector + 3].Style = headerStyle;
excelSheet.Cells[3, column_selector + 4].Value = "Total Online (GB)";
excelSheet.Cells[3, column_selector + 4].Style = headerStyle;
int row = 4;
double total_size = 0;
int year = Convert.ToInt16(space_utilized.Tables[0].Rows[0][0]);
int month = Convert.ToInt16(space_utilized.Tables[0].Rows[0][0]);
foreach (DataRow thisRow in space_utilized.Tables[0].Rows)
{
int this_month = Convert.ToInt32(thisRow[1]);
int this_year = Convert.ToInt32(thisRow[0]);
while (( this_month > month + 1 ) || ( this_year > year ))
{
month++;
if (month > 12)
{
month = 0;
year++;
}
else
{
excelSheet.Cells[row, column_selector].Value = year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = 0;
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
row++;
}
}
excelSheet.Cells[row, column_selector].Value = this_year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = this_month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = Convert.ToInt32(thisRow[2]);
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
total_size = total_size + (Convert.ToDouble(thisRow[2]) / 1024);
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
month = this_month;
year = this_year;
row++;
}
// Set the correct widths
excelSheet.Columns[column_selector].Width = 8 * 256;
excelSheet.Columns[column_selector + 1].Width = 8 * 256;
excelSheet.Columns[column_selector + 2].Width = 20 * 256;
excelSheet.Columns[column_selector + 3].Width = 20 * 256;
excelSheet.Columns[column_selector + 4].Width = 20 * 256;
// Set the border
excelSheet.Cells.GetSubrange("B4", "F" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("B4", "F4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
// excelSheet.Cells.GetSubrange("B4", "F" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thick);
// Add to the column selector
column_selector = 7;
}
// Write the archived utilized space
if (( space_utilized.Tables.Count > 1 ) && ( space_utilized.Tables[1].Rows.Count > 0))
{
excelSheet.Cells[1, column_selector].Value = "Total size of items archived with CNS for " + worksheet_name + " by Month/Year";
excelSheet.Cells[1, column_selector].Style = titleStyle;
excelSheet.Cells[3, column_selector].Value = "Year";
excelSheet.Cells[3, column_selector].Style = headerStyle;
excelSheet.Cells[3, column_selector + 1].Value = "Month";
excelSheet.Cells[3, column_selector + 1].Style = headerStyle;
excelSheet.Cells[3, column_selector + 2].Value = "Added Archive (MB)";
excelSheet.Cells[3, column_selector + 2].Style = headerStyle;
excelSheet.Cells[3, column_selector + 3].Value = "Total Archive (MB)";
excelSheet.Cells[3, column_selector + 3].Style = headerStyle;
excelSheet.Cells[3, column_selector + 4].Value = "Total Archive (GB)";
excelSheet.Cells[3, column_selector + 4].Style = headerStyle;
int row = 4;
double total_size = 0;
int year = Convert.ToInt16(space_utilized.Tables[1].Rows[0][0]);
int month = Convert.ToInt16(space_utilized.Tables[1].Rows[0][0]);
foreach (DataRow thisRow in space_utilized.Tables[1].Rows)
{
int this_month = Convert.ToInt16(thisRow[1]);
int this_year = Convert.ToInt16(thisRow[0]);
while ((this_month > month + 1) || (this_year > year))
{
month++;
if (month > 12)
{
month = 0;
year++;
}
else
{
excelSheet.Cells[row, column_selector].Value = year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = 0;
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
row++;
}
}
excelSheet.Cells[row, column_selector].Value = this_year;
excelSheet.Cells[row, column_selector].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 1].Value = this_month;
excelSheet.Cells[row, column_selector + 1].Style = defaultStyle;
excelSheet.Cells[row, column_selector + 2].Value = Convert.ToInt32(thisRow[2]);
excelSheet.Cells[row, column_selector + 2].Style = numberStyle2;
total_size = total_size + (Convert.ToDouble(thisRow[2]));
excelSheet.Cells[row, column_selector + 3].Value = total_size;
excelSheet.Cells[row, column_selector + 3].Style = numberStyle2;
excelSheet.Cells[row, column_selector + 4].Value = total_size / 1024;
excelSheet.Cells[row, column_selector + 4].Style = numberStyle;
month = this_month;
year = this_year;
row++;
}
// Set the correct widths
excelSheet.Columns[column_selector].Width = 8 * 256;
excelSheet.Columns[column_selector + 1].Width = 8 * 256;
excelSheet.Columns[column_selector + 2].Width = 20 * 256;
excelSheet.Columns[column_selector + 3].Width = 20 * 256;
excelSheet.Columns[column_selector + 4].Width = 20 * 256;
// Set the border
if (column_selector > 1)
{
excelSheet.Cells.GetSubrange("H4", "L4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("H4", "L" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
}
else
{
excelSheet.Cells.GetSubrange("B4", "F4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("B4", "F" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
}
}
// Save the file
if (filename.ToUpper().IndexOf(".XLSX") > 0)
{
excelFile.SaveXlsx(filename);
}
else
{
excelFile.SaveXls(filename);
}
return true;
}
catch ( Exception ee )
{
return false;
}
}
private void AddChart(ExcelFile xls)
{
//This code is adapted from APIMate.
//Objects
TShapeProperties ChartOptions1 = new TShapeProperties();
ChartOptions1.Anchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, 1, 215, 1, 608, 30, 228, 17, 736);
ChartOptions1.ShapeName = "Lines of code";
ChartOptions1.Print = true;
ChartOptions1.Visible = true;
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fLockText, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.LockRotation, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fAutoTextMargin, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fillColor, 134217806);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.wzName, "Lines of code");
ExcelChart Chart1 = xls.AddChart(ChartOptions1, TChartType.Area, new ChartStyle(102), false);
TDataLabel Title = new TDataLabel();
Title.PositionZeroBased = null;
ChartFillOptions TextFillOptions = new ChartFillOptions(new TShapeFill(new TSolidFill(TDrawingColor.FromRgb(0x80, 0x80, 0x80)), true, TFormattingType.Subtle, TDrawingColor.FromRgb(0x00, 0x00, 0x00, new TColorTransform(TColorTransformType.Alpha, 0)), false));
TChartTextOptions LabelTextOptions = new TChartTextOptions(new TFlxChartFont("Calibri Light", 320, TExcelColor.FromArgb(0x80, 0x80, 0x80), TFlxFontStyles.Bold, TFlxUnderline.None, TFontScheme.Major), THFlxAlignment.center, TVFlxAlignment.center, TBackgroundMode.Transparent, TextFillOptions);
Title.TextOptions = LabelTextOptions;
TDataLabelOptions LabelOptions = new TDataLabelOptions();
Title.LabelOptions = LabelOptions;
ChartLineOptions ChartLineOptions = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle));
ChartFillOptions ChartFillOptions = new ChartFillOptions(new TShapeFill(new TNoFill(), false, TFormattingType.Subtle, null, false));
Title.Frame = new TChartFrameOptions(ChartLineOptions, ChartFillOptions, false);
TRTFRun[] Runs;
Runs = new TRTFRun[1];
Runs[0].FirstChar = 0;
TFlxFont fnt;
fnt = xls.GetDefaultFont;
fnt.Name = "Calibri Light";
fnt.Size20 = 320;
fnt.Color = TExcelColor.FromArgb(0x80, 0x80, 0x80);
fnt.Style = TFlxFontStyles.Bold;
fnt.Family = 0;
fnt.CharSet = 1;
fnt.Scheme = TFontScheme.Major;
Runs[0].FontIndex = xls.AddFont(fnt);
TRichString LabelValue1 = new TRichString("FlexCel: Lines of code over time", Runs, xls);
Title.LabelValues = new Object[] { LabelValue1 };
Chart1.SetTitle(Title);
Chart1.Background = new TChartFrameOptions(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85)), 9525, TDrawingColor.FromTheme(TThemeColor.Light1), false);
TChartFrameOptions PlotAreaFrame;
ChartLineOptions = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle));
ChartFillOptions = new ChartFillOptions(new TShapeFill(new TPatternFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85)), TDrawingColor.FromTheme(TThemeColor.Light1), TDrawingPattern.ltDnDiag), true, TFormattingType.Subtle, null, false));
PlotAreaFrame = new TChartFrameOptions(ChartLineOptions, ChartFillOptions, false);
TChartPlotAreaPosition PlotAreaPos = new TChartPlotAreaPosition(true, TChartRelativeRectangle.Automatic, TChartLayoutTarget.Inner, true);
Chart1.PlotArea = new TChartPlotArea(PlotAreaFrame, PlotAreaPos, false);
Chart1.SetChartOptions(1, new TAreaChartOptions(false, TStackedMode.Stacked, null));
int LastYear = 0;
double shade = 1;
for (int i = 2; i < 190; i++)
{
ChartSeries Series = new ChartSeries(
"=" + new TCellAddress("Data", 1, i, true, true).CellRef,
"=" + new TCellAddress("Data", 2, i, true, true).CellRef + ":" + new TCellAddress("Data", 189, i, true, true).CellRef,
"=Data!$A$2:$A$189");
//We will display every year in a single color. Each month gets its own shade.
int xf = -1;
int Year = FlxDateTime.FromOADate(((double)xls.GetCellValue(2, 1, i, ref xf)), false).Year;
if (LastYear != Year)
{
shade = 1;
}
else if (shade > 0.3)
{
shade -= 0.05;
}
LastYear = Year;
TDrawingColor SeriesColor = TDrawingColor.FromTheme(TThemeColor.Accent1 + Year % 6,
new TColorTransform(TColorTransformType.Shade, shade));
ChartSeriesFillOptions SeriesFill = new ChartSeriesFillOptions(new TShapeFill(new TSolidFill(SeriesColor), true, TFormattingType.Subtle, null, false), null, false, false);
ChartSeriesLineOptions SeriesLine = new ChartSeriesLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle), false);
Series.Options.Add(new ChartSeriesOptions(-1, SeriesFill, SeriesLine, null, null, null, true));
Chart1.AddSeries(Series);
}
Chart1.PlotEmptyCells = TPlotEmptyCells.Zero;
Chart1.ShowDataInHiddenRowsAndCols = false;
TFlxChartFont AxisFont = new TFlxChartFont("Calibri", 180, TExcelColor.FromArgb(0x59, 0x59, 0x59), TFlxFontStyles.None, TFlxUnderline.None, TFontScheme.Minor);
TAxisLineOptions AxisLine = new TAxisLineOptions();
AxisLine.MainAxis = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis = false;
TAxisTickOptions AxisTicks = new TAxisTickOptions(TTickType.Outside, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb(0x59, 0x59, 0x59), 0);
TAxisRangeOptions AxisRangeOptions = new TAxisRangeOptions(12, 1, false, false, false);
TBaseAxis CatAxis = new TCategoryAxis(0, 0, 12, TDateUnits.Days, 12, TDateUnits.Days, TDateUnits.Months, 0, TCategoryAxisOptions.AutoMin | TCategoryAxisOptions.AutoMax | TCategoryAxisOptions.DateAxis | TCategoryAxisOptions.AutoCrossDate | TCategoryAxisOptions.AutoDate, AxisFont, "yyyy\\-mm\\-dd;@", true, AxisLine, AxisTicks, AxisRangeOptions, null, TChartAxisPos.Bottom, 1);
AxisFont = new TFlxChartFont("Calibri", 180, TExcelColor.FromArgb(0x59, 0x59, 0x59), TFlxFontStyles.None, TFlxUnderline.None, TFontScheme.Minor);
AxisLine = new TAxisLineOptions();
AxisLine.MainAxis = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle));
AxisLine.MajorGridLines = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis = false;
AxisTicks = new TAxisTickOptions(TTickType.None, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb(0x59, 0x59, 0x59), 0);
CatAxis.NumberFormat = "yyyy-mm";
CatAxis.NumberFormatLinkedToSource = false;
TBaseAxis ValAxis = new TValueAxis(0, 0, 0, 0, 0, TValueAxisOptions.AutoMin | TValueAxisOptions.AutoMax | TValueAxisOptions.AutoMajor | TValueAxisOptions.AutoMinor | TValueAxisOptions.AutoCross, AxisFont, "General", true, AxisLine, AxisTicks, null, TChartAxisPos.Left);
Chart1.SetChartAxis(new TChartAxis(0, CatAxis, ValAxis));
}
/// <summary> Adds the controls for this result viewer to the place holder on the main form </summary>
/// <param name="placeHolder"> Main place holder ( "mainPlaceHolder" ) in the itemNavForm form into which the the bulk of the result viewer's output is displayed</param>
/// <param name="Tracer"> Trace object keeps a list of each method executed and important milestones in rendering </param>
/// <returns> Sorted tree with the results in hierarchical structure with volumes and issues under the titles and sorted by serial hierarchy </returns>
public override void Add_HTML(PlaceHolder placeHolder, Custom_Tracer Tracer)
{
if (Tracer != null)
{
Tracer.Add_Trace("Export_File_ResultsViewer.Add_HTML", "Rendering results in text view");
}
//// Sort this by BIbID
//resultTable.Sort("BibID");
//// Determine which rows to display
//int lastRow = base.LastRow;
//int startRow = base.StartRow(lastRow);
//// prepare to step through each title to display
//List<SobekCM_Item_Collection.SobekCM_Item_Row> itemRows = new List<SobekCM_Item_Collection.SobekCM_Item_Row>();
// Prepare to build an output
StringBuilder resultsBldr = new StringBuilder(5000);
int currentPage = RequestSpecificValues.Current_Mode.Page.HasValue ? RequestSpecificValues.Current_Mode.Page.Value : 1;
if (currentPage < 2)
{
resultsBldr.Append("<br />" + Environment.NewLine + "<div class=\"SobekHomeText\">" + Environment.NewLine + "<blockquote>" + Environment.NewLine );
resultsBldr.Append("This option allows you to export the list of results which match your search or browse to an excel document or CSV file for download.<br /><br />");
resultsBldr.Append("Select the file type below to create the report:");
resultsBldr.Append("<blockquote>");
//currentMode.Page = 2;
//resultsBldr.Append("<a href=\"" + currentMode.Redirect_URL() + "\">Excel Spreadsheet file (XLSX)</a><br /><br />");
RequestSpecificValues.Current_Mode.Page = 3;
resultsBldr.Append("<a href=\"" + UrlWriterHelper.Redirect_URL(RequestSpecificValues.Current_Mode) + "\">Excel Spreadsheet file (XLS)</a><br /><br />");
RequestSpecificValues.Current_Mode.Page = 4;
resultsBldr.Append("<a href=\"" + UrlWriterHelper.Redirect_URL(RequestSpecificValues.Current_Mode) + "\">Comma-seperated value text file (CSV)</a><br /><br />");
//currentMode.Page = 5;
//resultsBldr.Append("<a href=\"" + currentMode.Redirect_URL() + "\">HTML Table (HTML)</a><br /><br />");
resultsBldr.Append("</blockquote>");
resultsBldr.Append("</blockquote>");
resultsBldr.Append("<br /><br />");
resultsBldr.Append("</div>");
// Add this to the page
Literal mainLiteral = new Literal {Text = resultsBldr.ToString()};
placeHolder.Controls.Add(mainLiteral);
}
else
{
string filename = RequestSpecificValues.Current_Mode.Instance_Name;
// Set the Gembox spreadsheet license key
string from_db = String.Empty;
string key = String.Empty;
if (UI_ApplicationCache_Gateway.Settings.Additional_Settings.ContainsKey("Spreadsheet Library License"))
{
try
{
key = UI_ApplicationCache_Gateway.Settings.Additional_Settings["Spreadsheet Library License"];
SecurityInfo thisDecryptor = new SecurityInfo();
string encryptedPassword = thisDecryptor.DecryptString(from_db, "*h3kj(83", "unsalted");
}
catch (Exception )
{
}
}
SpreadsheetInfo.SetLicense(key);
// Create the excel file and worksheet
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet excelSheet = excelFile.Worksheets.Add(RequestSpecificValues.Current_Mode.Instance_Name);
excelFile.Worksheets.ActiveWorksheet = excelSheet;
// Create the header cell style
CellStyle headerStyle = new CellStyle
{ HorizontalAlignment = HorizontalAlignmentStyle.Left, Font = {Weight = ExcelFont.BoldWeight} };
headerStyle.FillPattern.SetSolid(Color.Yellow);
headerStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the title cell style
CellStyle titleStyle = new CellStyle
{ HorizontalAlignment = HorizontalAlignmentStyle.Left, Font = {Weight = ExcelFont.BoldWeight, Size = 12*20} };
// Set the default style
CellStyle defaultStyle = new CellStyle();
defaultStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
const int columnSelector = 0;
int row = 0;
if (RequestSpecificValues.Current_Mode.Page != 4)
{
// Add the title
excelSheet.Cells[1, columnSelector].Value = "Search or browse result list";
excelSheet.Cells[1, columnSelector].Style = titleStyle;
row = 3;
}
// Add the column headers
excelSheet.Cells[row, columnSelector].Value = "System ID";
excelSheet.Cells[row, columnSelector].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 1].Value = "Link";
excelSheet.Cells[row, columnSelector + 1].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 2].Value = "Group Title";
excelSheet.Cells[row, columnSelector + 2].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 3].Value = "Item Title";
excelSheet.Cells[row, columnSelector + 3].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 4].Value = "Date";
excelSheet.Cells[row, columnSelector + 4].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 5].Value = "Author";
excelSheet.Cells[row, columnSelector + 5].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 6].Value = "Publisher";
excelSheet.Cells[row, columnSelector + 6].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 7].Value = "Format";
excelSheet.Cells[row, columnSelector + 7].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 8].Value = "Edition";
excelSheet.Cells[row, columnSelector + 8].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 9].Value = "Subjects";
excelSheet.Cells[row, columnSelector + 9].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 10].Value = "Measurement";
excelSheet.Cells[row, columnSelector + 10].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 11].Value = "Style / Period";
excelSheet.Cells[row, columnSelector + 11].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 12].Value = "Technique";
excelSheet.Cells[row, columnSelector + 12].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 13].Value = "Institution";
excelSheet.Cells[row, columnSelector + 13].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 14].Value = "Donor";
excelSheet.Cells[row, columnSelector + 14].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 15].Value = "OCLC";
excelSheet.Cells[row, columnSelector + 15].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 16].Value = "ALEPH";
excelSheet.Cells[row, columnSelector + 16].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 17].Value = "Serial1";
excelSheet.Cells[row, columnSelector + 17].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 18].Value = "Serial2";
excelSheet.Cells[row, columnSelector + 18].Style = headerStyle;
excelSheet.Cells[row, columnSelector + 19].Value = "Serial3";
excelSheet.Cells[row, columnSelector + 19].Style = headerStyle;
// Set the correct widths
excelSheet.Columns[columnSelector].Width = 12 * 256;
excelSheet.Columns[columnSelector + 1].Width = 8 * 256;
excelSheet.Columns[columnSelector + 2].Width = 40 * 256;
excelSheet.Columns[columnSelector + 3].Width = 40 * 256;
excelSheet.Columns[columnSelector + 4].Width = 20 * 256;
excelSheet.Columns[columnSelector + 5].Width = 14 * 256;
excelSheet.Columns[columnSelector + 6].Width = 14 * 256;
excelSheet.Columns[columnSelector + 7].Width = 20 * 256;
excelSheet.Columns[columnSelector + 8].Width = 20 * 256;
excelSheet.Columns[columnSelector + 9].Width = 20 * 256;
excelSheet.Columns[columnSelector + 10].Width = 20 * 256;
excelSheet.Columns[columnSelector + 11].Width = 20 * 256;
excelSheet.Columns[columnSelector + 12].Width = 20 * 256;
excelSheet.Columns[columnSelector + 13].Width = 20 * 256;
excelSheet.Columns[columnSelector + 14].Width = 20 * 256;
excelSheet.Columns[columnSelector + 15].Width = 20 * 256;
excelSheet.Columns[columnSelector + 16].Width = 20 * 256;
excelSheet.Columns[columnSelector + 17].Width = 20 * 256;
excelSheet.Columns[columnSelector + 18].Width = 20 * 256;
excelSheet.Columns[columnSelector + 19].Width = 20 * 256;
row++;
// Add each row
foreach (iSearch_Title_Result titleResult in RequestSpecificValues.Paged_Results)
{
for (int item_count = 0; item_count < titleResult.Item_Count; item_count++)
{
iSearch_Item_Result itemResult = titleResult.Get_Item(item_count);
excelSheet.Cells[row, columnSelector].Value = titleResult.BibID + "_" + itemResult.VID;
excelSheet.Cells[row, columnSelector + 1].Value = RequestSpecificValues.Current_Mode.Base_URL + titleResult.BibID + "/" + itemResult.VID;
excelSheet.Cells[row, columnSelector + 2].Value = titleResult.GroupTitle.Replace("<i>","").Replace("</i>","").Replace("&","&");
excelSheet.Cells[row, columnSelector + 3].Value = itemResult.Title.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 4].Value = itemResult.PubDate.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 5].Value = titleResult.Author.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 6].Value = titleResult.Publisher.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 7].Value = titleResult.Format.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 8].Value = titleResult.Edition.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 9].Value = titleResult.Subjects.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 10].Value = titleResult.Measurement.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 11].Value = titleResult.Style_Period.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 12].Value = titleResult.Technique.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 13].Value = titleResult.Institution.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 14].Value = titleResult.Donor.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//if (titleResult.OCLC_Number > 1)
// excelSheet.Cells[row, columnSelector + 15].Value = titleResult.OCLC_Number.ToString();
//if (titleResult.ALEPH_Number > 1)
// excelSheet.Cells[row, columnSelector + 16].Value = titleResult.ALEPH_Number.ToString();
//excelSheet.Cells[row, columnSelector + 17].Value = itemResult.Level1_Text.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 18].Value = itemResult.Level2_Text.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
//excelSheet.Cells[row, columnSelector + 19].Value = itemResult.Level3_Text.Replace("<i>", "").Replace("</i>", "").Replace("&", "&");
row++;
}
}
// Set the border
excelSheet.Cells.GetSubrange("A4", "T" + row).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("A4", "t4").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
if (Tracer != null)
{
Tracer.Add_Trace("Export_File_ResultsViewer.Add_HTML", "Clearing response");
}
// Clear any response until now
HttpContext.Current.Response.Clear();
// Output in proper format to the user
switch (RequestSpecificValues.Current_Mode.Page)
{
//case 2:
// System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats";
// System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xlsx");
// // With XLSX it is a bit more complicated as MS Packaging API can't write
// // directly to Response.OutputStream. Therefore we use temporary MemoryStream.
// System.IO.MemoryStream ms = new System.IO.MemoryStream();
// excelFile.SaveXlsx(ms);
// ms.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
// break;
case 3:
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
excelFile.SaveXls(HttpContext.Current.Response.OutputStream);
break;
case 4:
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
excelFile.SaveCsv(HttpContext.Current.Response.OutputStream, CsvType.CommaDelimited);
break;
//case 5:
// System.Web.HttpContext.Current.Response.ContentType = "text/html";
// System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report18.html");
// System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(System.Web.HttpContext.Current.Response.OutputStream, new UTF8Encoding(false));
// excelFile.SaveHtml(writer, null, true);
// writer.Close();
// break;
}
if (Tracer != null)
{
Tracer.Add_Trace("Export_File_ResultsViewer.Add_HTML", "Ending response");
}
HttpContext.Current.Response.End();
}
}
public void CreateWorkSheet(ExcelFile ex, DateTime dateFrom, DateTime dateTo)
{
var daySpan = dateTo.Subtract(dateFrom).Days;
for (var i = 0; i <= daySpan; i++)
{
var day = dateFrom.Add(new TimeSpan(i, 0, 0, 0));
var worksheet = ex.Worksheets.AddCopy(day.ToString("dd-MMM-yyyy"), ex.Worksheets[0]);
ICriterion finalCriterion = Expression.Ge("Id", 0);
finalCriterion = Expression.And(finalCriterion, Expression.Eq("Status", StatusType.Approved));
finalCriterion = Expression.And(finalCriterion, Expression.Not(Expression.Eq("IsTransferred", true)));
finalCriterion = Expression.And(finalCriterion, Expression.Eq("Deleted", false));
var timeSpan = new TimeSpan(0, 23, 59, 59);
var dateFrom2DayBookingCriterion = Expression.Ge("StartDate", day);
var dateTo2DayBookingCriterion = Expression.Le("StartDate", day.Date.Add(timeSpan));
var dateFrom3DayBookingCriterion = Expression.Le("StartDate", day);
var dateTo3DayBookingCriterion = Expression.Ge("EndDate", day.Date.Add(timeSpan));
var dateFromTo2DayBookingCriterion = Expression.And(dateFrom2DayBookingCriterion, dateTo2DayBookingCriterion);
var dateFromTo3DayBookingCriterion = Expression.And(dateFrom3DayBookingCriterion, dateTo3DayBookingCriterion);
var dateFromToCriterion = Expression.Or(dateFromTo2DayBookingCriterion, dateFromTo3DayBookingCriterion);
finalCriterion = Expression.And(finalCriterion, dateFromToCriterion);
var bookingList = Module.GetObject <Booking>(finalCriterion, 0, 0);
var currentRowSheet = 1;
if (bookingList.Count > 0)
{
for (int l = 0; l < bookingList.Count; l++)
{
var customerListVn = new List <Customer>();
var customerList = new List <Customer>();
var booking = bookingList[l];
var bookingRoomslist = Module.GetObject <BookingRoom>(Expression.Eq("Book", booking), 0, 0);
for (int k = 0; k < bookingRoomslist.Count; k++)
{
var bookingRooms = bookingRoomslist[k];
var realCustomersList = bookingRooms.RealCustomers;
for (int j = 0; j < realCustomersList.Count; j++)
{
var customer = realCustomersList[j] as Customer;
if (customer != null)
{
customerList.Add(customer);
}
else
{
throw new Exception("customer = null");
}
}
}
FillDataToSheet(worksheet, customerList, 1, booking, ref currentRowSheet);
}
}
}
ex.Worksheets[0].Delete();
}
