static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.Add("Sheet1");
// Merge cells
worksheet.Range("B6:D18").Merge();
// Write text
worksheet.Cell("C10").MergedWithCell.Value = "Bytescout.Spreadsheet";
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close document
document.Close();
// Open document in default xls viewer
Process.Start("Output.xls");
}
static void Main(string[] args)
{
const string fileName = "CSharpImportFromDataTable.xls";
// Create a new spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
// Get the data from the datatable that we want to import
DataTable periodicTable = GetDataTable();
// Import data into spreadheet
spreadsheet.ImportFromDataTable(periodicTable);
// Insert row with column captions
Worksheet worksheet = spreadsheet.Worksheets[0];
worksheet.Rows.Insert(0);
for (int colIndex = 0; colIndex < periodicTable.Columns.Count; colIndex++)
{
worksheet.Cell(0, colIndex).Value = periodicTable.Columns[colIndex].Caption;
}
// Save the spreadsheet
if (File.Exists(fileName))
{
File.Delete(fileName);
}
spreadsheet.SaveAs(fileName);
// Close spreadsheet
spreadsheet.Close();
// Open the spreadsheet
Process.Start(fileName);
}
static void Main(string[] args)
{
try
{
// create new Spreadsheet object
Spreadsheet spreadsheet = new Spreadsheet();
// add new worksheet
Worksheet sheet = spreadsheet.Workbook.Worksheets.Add("Sample");
// add a picture to worksheet
sheet.Pictures.Add("image.jpg", 100, 100);
sheet.Cell("A1").ValueAsHTML = "<b>Image from</b> from <i>Image.jpg</i>";
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save it as XLS
spreadsheet.SaveAs("Output.xls");
// close the document
spreadsheet.Close();
// open output XLS
Process.Start("Output.xls");
}
catch (Exception e) {
Console.WriteLine("CAN NOT EXECUTE: " + e.ToString());
Console.WriteLine("\nPress any key to exit");
Console.ReadKey();
}
}
static void Main(string[] args)
{
// Open existing Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("input.xls");
// Create copy of worksheet
document.Workbook.Worksheets.Copy(0, 1, "Copy of Sheet1");
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close document
document.Close();
// Open document in default xls viewer
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Open Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("SimpleReport.xls");
// Get Worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// delete output file if exists already
if (File.Exists("SimpleReport.htm"))
{
File.Delete("SimpleReport.htm");
}
// Export to HTML
worksheet.SaveAsHTML("SimpleReport.htm");
// Close Spreadsheet
document.Close();
// open generated HTML
Process.Start("SimpleReport.htm");
}
static void Main(string[] args)
{
const string inputFile = @"PeriodicTableOfElementsSpreadsheet.xls";
// Open and load spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.LoadFromFile(inputFile);
// Get the data from the spreadsheet
DataTable dt = spreadsheet.ExportToDataTable();
// Close spreadsheet
spreadsheet.Close();
// Display array
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Console.Write(dt.Rows[i][j] + " ");
}
Console.WriteLine();
}
// Pause
Console.ReadLine();
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("sample.xls");
// Move page
document.Workbook.Worksheets.Move(1, 0);
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
/// <summary>
/// shows how to convert XML data into XLS excel using Bytescout.Spreadsheet and Bytescout.Spreadsheet.Utils.SimpleXMLConverter
/// </summary>
private static void SampleXMLtoXLSConversion()
{
// read XML and convert into XLS (Excel) and save
Spreadsheet document = new Spreadsheet();
SimpleXMLConverter tools = new SimpleXMLConverter(document);
tools.LoadXML("AdvancedReport.xml");
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
static void Main(string[] args)
{
const string fileName = "CSharpImportFromJaggedArray.xls";
// Create a new spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
// Get the data from the jagged array that we want to import
string[][] periodicTable = GetJaggedArray();
// Import data into spreadheet
spreadsheet.ImportFromJaggedArray(periodicTable);
// Save the spreadsheet
if (File.Exists(fileName))
{
File.Delete(fileName);
}
spreadsheet.SaveAs(fileName);
// Close spreadsheet
spreadsheet.Close();
// Open the spreadsheet
Process.Start(fileName);
}
static void Main(string[] args)
{
// Create new spreadsheet (or open existing)
Spreadsheet doc = new Spreadsheet();
// Add worksheet
Worksheet worksheet = doc.Worksheets.Add();
// Put background image on the worksheet
worksheet.BackgroundPicture = Image.FromFile("image1.jpg");
// Delete output file if exists
if (File.Exists("output.xls"))
{
File.Delete("output.xls");
}
// Save document
doc.SaveAs("output.xls");
// Close spreadsheet
doc.Close();
// Open generated XLS document in default application
Process.Start("output.xls");
doc.Dispose();
}
static void Main(string[] args)
{
const string fileName = "CSharpImportFrom2DArray.xls";
// Create a new spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
// Get the data from the 2D array that we want to import
string[,] stockPrices = Get2DArray();
// Import data into spreadheet
spreadsheet.ImportFrom2DArray(stockPrices);
// Save the spreadsheet
if (File.Exists(fileName))
{
File.Delete(fileName);
}
spreadsheet.SaveAs(fileName);
// Close spreadsheet
spreadsheet.Close();
// Open the spreadsheet
Process.Start(fileName);
}
static void Main(string[] args)
{
// Open Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("AdvancedReport.xls");
// Get Worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Add new Column
worksheet.Columns.Insert(3, 1);
// Set values
worksheet.Rows[0][2].Value = "Age";
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Create new XLS document
Spreadsheet document = new Spreadsheet();
// Create new worksheet
Worksheet worksheet = document.Workbook.Worksheets.Add();
// Set column width
worksheet.Columns[1].Width = 300;
// Set column height
worksheet.Rows[1].Height = 100;
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open in default spreadsheets viewer/editor
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Add new worksheet
Worksheet worksheet = document.Workbook.Worksheets.Add("HelloWorld");
// Set cell value
worksheet.Cell(0, 0).Value = "Very, very, very, very long text";
// Shrink text
worksheet.Cell(0, 0).ShrinkToFit = true;
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Open spreadsheet from file
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("example.xls");
// Get first worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Hide formula in B9 cell
worksheet.Cell("B9").HiddenFormula = true;
// Protect the worksheet with password
worksheet.Protect("password");
// Delete output file if exists
if (File.Exists("changed.xls"))
{
File.Delete("changed.xls");
}
// Save document
document.SaveAs("changed.xls");
// Close spreadsheet
document.Close();
// Open generated XLS document in default program
Process.Start("changed.xls");
}
public static void Writer(List<XlsChanges> changes)
{
Spreadsheet document = new Spreadsheet();
Worksheet Sheet = document.Workbook.Worksheets.Add("Changes");
Sheet.Cell("A1").Value = "Previous name";
Sheet.Columns[0].Width = 250;
Sheet.Cell("B1").Value = "New name";
Sheet.Columns[1].Width = 250;
Sheet.Cell("D1").Value = "Previous value";
Sheet.Columns[3].Width = 250;
Sheet.Cell("E1").Value = "New value";
Sheet.Columns[4].Width = 250;
var i = 2;
foreach (var change in changes)
{
Sheet.Cell("A" + i).Value = change.OldName;
Sheet.Cell("B" + i).Value = change.NewName;
Sheet.Cell("D" + i).Value = change.OldValue;
Sheet.Cell("E" + i).Value = change.NewValue;
i++;
}
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
document.SaveAs("Output.xls");
document.Close();
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Add new worksheet
Worksheet worksheet = document.Workbook.Worksheets.Add("HelloWorld");
// Set cell value
worksheet.Cell(0, 0).Value = "Hello, World!";
// Add horizontal page break
worksheet.HPageBreaks.Add(new CellsRange("A10"));
// Add vertical page break
worksheet.VPageBreaks.Add(new CellsRange("F1"));
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// Open generated XLS document in default associated application
Process.Start("Output.xls");
}
static void Main(string[] args)
{
string[] arr = new string[0];
string[][] readFile = new string[0][];
string pathUrl = @"C:\Users\TaiPham\Desktop\PrivateIncludes";
Spreadsheet document = new Spreadsheet();
Worksheet Sheet = document.Workbook.Worksheets.Add("sheet1");
Sheet.Cell("A1").Value = "Path";
Sheet.Columns[0].Width = 250;
Sheet.Cell("B1").Value = "Line";
Sheet.Columns[1].Width = 250;
Sheet.Cell("C1").Value = "English";
Sheet.Columns[2].Width = 250;
Sheet.Cell("D1").Value = "pathRoot";
Sheet.Columns[3].Width = 250;
string[] tes = ReadAllFilesInDirectory(pathUrl, arr);
Console.WriteLine(pathUrl.Length);
readFile = IterateFiles(tes, pathUrl.Length);
int rowIndex = 2;
for (int i = 0; i < readFile.Length; i++)
{
Sheet.Cell(Convert.ToString("A" + rowIndex)).Value = readFile[i][0];
Sheet.Cell(Convert.ToString("B" + rowIndex)).Value = readFile[i][2];
Sheet.Cell(Convert.ToString("C" + rowIndex)).Value = readFile[i][1];
Sheet.Cell(Convert.ToString("D" + rowIndex)).Value = Convert.ToString(pathUrl + @"\" + readFile[i][0]);
rowIndex++;
}
document.SaveAs("Output.xls");
document.Close();
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("Sample.xls");
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");
// Check dates
for (int i = 0; i < 4; i++)
{
// Set current cell
Cell currentCell = worksheet.Cell(i, 0);
DateTime date = currentCell.ValueAsDateTime;
// Write Date
Console.WriteLine("{0}", date.ToShortDateString());
}
// Close document
document.Close();
// Write message
Console.Write("Press any key to continue...");
// Wait user input
Console.ReadKey();
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("Input.xls");
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets[0];
// Unmerge cells
worksheet.Range("B6:D18").UnMerge();
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Open spreadsheet from file
Spreadsheet document = new Spreadsheet();
document.LoadFromFile(@"example.xls");
//document.LoadFromFile(@"d:\2\1\Bytescout\Spreadsheet SDK\TestCase\Formats.xls");
// Get first worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Copy first two ("A-B") columns to the fourth ("D") column
worksheet.Columns.CopyAndPaste(0, 1, 12);
// Delete output file if exists
if (File.Exists("changed.xls"))
{
File.Delete("changed.xls");
}
// Save document
document.SaveAs("changed.xls");
// Close spreadsheet
document.Close();
// Open generated XLS document in default program
Process.Start("changed.xls");
}
static void Main(string[] args)
{
const string inputFile = @"StockPricesSpreadsheet.xls";
// Open and load spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.LoadFromFile(inputFile);
// Get the data from the spreadsheet
string[,] stockPrices = spreadsheet.ExportTo2DArray();
// Close spreadsheet
spreadsheet.Close();
// Display data in data table
for (int i = 0; i < stockPrices.GetLength(0); i++)
{
for (int j = 0; j < stockPrices.GetLength(1); j++)
{
Console.Write(stockPrices[i, j] + " ");
}
Console.WriteLine();
}
// Pause
Console.ReadLine();
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Add new worksheet
Worksheet worksheet = document.Workbook.Worksheets.Add("Sheet1");
// Apply rich text formatting
worksheet.Cell(0, 0).ValueAsHTML = "<b><u>Bold Underline</u>, and <i>bold italic</i></b> text";
worksheet.Cell(1, 0).ValueAsHTML = "<font color=Blue>Blue</font>, <font color=Green>Green</font> and other <b><font face=Tahoma color=Red>co<font><font face=Tahoma color=Green>lo<font><font face=Tahoma color=Blue>rs<font></b> <font color=black><b>are</b> <u>available</u></font>";
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Move page
Worksheet worksheet = document.Workbook.Worksheets.Add();
// Set unicode text
worksheet.Cell(0, 0).Value = "鍖鐳鑅";
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Add new worksheet
Worksheet worksheet = document.Workbook.Worksheets.Add("HelloWorld");
// Set cell value
worksheet.Cell(0, 0).Value = "Hello, World!";
if (File.Exists("Output.xlsx"))
{
File.Delete("Output.xlsx");
}
// Save document
document.SaveAs("Output.xlsx");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xlsx");
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("data.xls");
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");
// Check dates
for (int i = 0; i < 4; i++)
{
// Set current cell
Cell currentCell = worksheet.Cell(i, 0);
DateTime date = currentCell.ValueAsDateTime;
// Write Date
Console.WriteLine("{0}", date.ToShortDateString());
}
// Close document
document.Close();
// Write message
Console.Write("Press any key to continue...");
// Wait user input
Console.ReadKey();
}
static void Main(string[] args)
{
const string inputFile = @"ListOfPlanetsSpreadsheet.xls";
// Open and load spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.LoadFromFile(inputFile);
// Get the data from the spreadsheet
IList planets = new string[10, 10];
spreadsheet.ExportToList(planets);
// Close spreadsheet
spreadsheet.Close();
// Display array
string[,] planetsArray = planets as string[, ];
for (int i = 0; i < planetsArray.GetLength(0); i++)
{
for (int j = 0; j < planetsArray.GetLength(1); j++)
{
Console.Write(planetsArray[i, j] + " ");
}
Console.WriteLine();
}
// Pause
Console.ReadLine();
}
static void Main(string[] args)
{
// Open Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("AdvancedReport.xls");
// Get Worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Set cell value
worksheet.Cell(1, 1).Value = "Homer Jay Simpson 2";
// delete output file if exists already
if (File.Exists("AdvancedReport_Modified.xls"))
{
File.Delete("AdvancedReport_Modified.xls");
}
// Save document
document.SaveAs("AdvancedReport_Modified.xls");
// Close Spreadsheet
document.Close();
// open output document in default viewer
Process.Start("AdvancedReport_Modified.xls");
}
static void Main(string[] args)
{
// Open spreadsheet from file
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("template.xls");
// Get first worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Find macros and replace them with values
Replace(worksheet, "<PRODUCT_NAME1>", "Product 1", null);
Replace(worksheet, "<PRODUCT_NAME2>", "Product 2", null);
Replace(worksheet, "<PRICE1>", 24.99f, "0.00");
Replace(worksheet, "<PRICE2>", 29.99f, "0.00");
Replace(worksheet, "<QANTITY1>", 5, null);
Replace(worksheet, "<QANTITY2>", 10, null);
// Delete output file if exists
if (File.Exists("output.xls"))
{
File.Delete("output.xls");
}
// Save document
document.SaveAs("output.xls");
// Close spreadsheet
document.Close();
// Open generated XLS document in default application
Process.Start("output.xls");
}
static void Main(string[] args)
{
// Open spreadsheet from file
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("example.xls");
// Get first worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Select cell range
Range range = worksheet.Range("A2:B8");
// Copy range to C10 cell
range.CopyInto("C10");
// Delete output file if exists
if (File.Exists("changed.xls"))
{
File.Delete("changed.xls");
}
// Save document
document.SaveAs("changed.xls");
// Close spreadsheet
document.Close();
// Open generated XLS document in default program
Process.Start("changed.xls");
}
static void Main(string[] args)
{
// Open Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("AdvancedReport.xls");
// Get Worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Add new row
worksheet.Rows.Insert(6, 1);
// Set values
worksheet.Rows[6][0].Value = "New Name";
worksheet.Rows[6][1].Value = "New Full Name";
// delete output file if exists already
if (File.Exists("AdvancedReport_Modified.xls")){
File.Delete("AdvancedReport_Modified.xls");
}
// Save document
document.SaveAs("AdvancedReport_Modified.xls");
// Close Spreadsheet
document.Close();
// open in default spreadsheets viewer/editor
Process.Start("AdvancedReport_Modified.xls");
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("Data.xls");
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");
// Set new worksheet name
worksheet.Name = "Collected Data Sheet";
// delete output file if exists already
if (File.Exists("Output.xls"))
{
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
public ClientsInfoSqLiteExporter Export()
{
var clientsInfo = this.sqliteData.GetAll();
Spreadsheet document = new Spreadsheet();
// add new worksheet
Worksheet sheet = document.Workbook.Worksheets.Add("Client contacts");
// headers to indicate purpose of the column
sheet.Cell("A1").Value = "Company Name";
sheet.Cell("B1").Value = "Email";
sheet.Cell("C1").Value = "Address";
sheet.Cell("D1").Value = "Iban";
Color headerColor = Color.FromArgb(80, 80, 80);
sheet.Rows[0].Height = 35;
for (int i = 0; i < 4; i++)
{
sheet.Cell(0, i).FillPattern = PatternStyle.Solid;
sheet.Cell(0, i).FillPatternForeColor = headerColor;
sheet.Cell(0, i).FontColor = Color.White;
sheet.Cell(0, i).Font = new Font("Arial", 14, FontStyle.Bold);
sheet.Cell(0,i).AlignmentHorizontal = AlignmentHorizontal.Centered;
sheet.Cell(0,i).AlignmentVertical = AlignmentVertical.Centered;
sheet.Columns[i].Width = 250;
}
// delete output file if exists already
if (File.Exists(documentPath))
{
File.Delete(documentPath);
}
var row = 1;
foreach (var entity in clientsInfo)
{
sheet.Cell(row, 0).Value = entity.Name;
sheet.Cell(row, 1).Value = entity.Email;
sheet.Cell(row, 2).Value = entity.Address;
sheet.Cell(row, 3).Value = entity.Iban;
row++;
}
document.SaveAs(documentPath);
// Close Spreadsheet
document.Close();
return this;
}
public void Seed(string townName, DateTime date, string rootFolder, IEnumerable<SalesInfo> products)
{
Spreadsheet document = new Spreadsheet();
Worksheet sheet = document.Workbook.Worksheets.Add("SalesReport");
sheet.Cell(0, 0).Value = "Product";
sheet.Columns[0].Width = 250;
sheet.Cell(0, 1).Value = "Price";
sheet.Columns[1].Width = 250;
sheet.Cell(0, 2).Value = "Quantity";
sheet.Columns[2].Width = 250;
sheet.Cell(0, 3).Value = "Sum";
sheet.Columns[3].Width = 250;
var rowNumber = 1;
foreach (var product in products)
{
sheet.Cell(rowNumber, 0).Value = product.ProductId;
sheet.Cell(rowNumber, 1).Value = product.Price;
sheet.Cell(rowNumber, 2).Value = product.Quantity;
sheet.Cell(rowNumber, 3).Value = product.Sum;
++rowNumber;
}
sheet.Range(rowNumber, 0, rowNumber, 3).Merge();
sheet.Range(rowNumber, 0, rowNumber, 3).Value = products.Sum(x => x.Sum);
var folderName = rootFolder + date.ToString("yyyy MMM dd");
if (!Directory.Exists(folderName))
{
Directory.CreateDirectory(folderName);
}
var fileName = townName + ".xls";
var filePath = folderName + "/" + fileName;
if (File.Exists(filePath))
{
File.Delete(filePath);
}
document.SaveAs(filePath);
document.Close();
}
public static List<XlsFileStructure> Reader(string filePath)
{
Spreadsheet document = new Spreadsheet();
document.LoadFromFile(filePath);
Worksheet worksheet = document.Workbook.Worksheets.ByName("Template");
var fileData = new List<XlsFileStructure>();
for (var col = 0; col < worksheet.UsedRangeColumnMax; col++)
{
fileData.Add(new XlsFileStructure
{
Name = worksheet.Cell(1, col).ValueAsString,
Value = worksheet.Cell(2, col).ValueAsString
});
}
document.Close();
return fileData;
}
public void Generate(CombinationsContext combinationsContext, MySqlContext mySqlContext)
{
Spreadsheet document = new Spreadsheet();
Worksheet sheet = document.Workbook.Worksheets.Add("SalesReport");
sheet.Cell(0, 0).Value = "TOWN";
sheet.Cell(0, 1).Value = "STORE";
sheet.Cell(0, 2).Value = "DATE";
sheet.Cell(0, 3).Value = "SOLD LAPTOPS COUNT";
Color headerColor = Color.FromArgb(75, 172, 198);
sheet.Cell(0, 0).FillPattern = PatternStyle.Solid;
sheet.Cell(0, 1).FillPattern = PatternStyle.Solid;
sheet.Cell(0, 2).FillPattern = PatternStyle.Solid;
sheet.Cell(0, 3).FillPattern = PatternStyle.Solid;
sheet.Cell(0, 0).FillPatternForeColor = headerColor;
sheet.Cell(0, 1).FillPatternForeColor = headerColor;
sheet.Cell(0, 2).FillPatternForeColor = headerColor;
sheet.Cell(0, 3).FillPatternForeColor = headerColor;
sheet.Columns[0].Width = 300;
sheet.Columns[1].Width = 300;
sheet.Columns[2].Width = 300;
sheet.Columns[3].Width = 300;
var a = mySqlContext.GetAll<SalesReport>().ToList();
var row = 1;
var bs = combinationsContext.Stores.ToList();
foreach (var report in a)
{
sheet.Cell(row, 0).Value = report.Town;
sheet.Cell(row, 1).Value = bs.First(x => x.Town == report.Town).Store;
sheet.Cell(row, 2).Value = report.Quantity;
sheet.Cell(row, 3).Value = report.Date;
++row;
}
document.SaveAs("export.xls");
document.Close();
}
public static string GetMenuExcelFile(this IRepositoryAsync<MenuForWeek> repository, ForMenuExcelDto dto)
{
WeekMenuDto weekMenuDto = repository.MapWeekMenuDto(dto.WeekYear);
WorkingWeek wweek = repository.WorkWeekByWeekYear(dto.WeekYear);
string[] daynames = wweek.WorkingDays.Where(wd => wd.IsWorking).OrderBy(wd=>wd.DayOfWeek.Id).Select(wd => wd.DayOfWeek.Name).ToArray();
string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
int daycount = weekMenuDto.WorkWeekDays.Count(d => d);
int catLength = dishCategories.Length;
List<MenuForDayDto> mfdays = new List<MenuForDayDto>();
for (int i = 0; i < weekMenuDto.WorkWeekDays.Length; i++)
{
if (weekMenuDto.WorkWeekDays[i])
{
mfdays.Add(weekMenuDto.MfdModels[i]);
}
}
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.Add("Меню");
string endcolname = GetExcelColumnName(4);
string allstr = string.Format("A{0}:D{1}", 1, mfdays.Count*(dishCategories.Length + 1) + 3);
string titlerang = String.Format("A1:{0}1", GetExcelColumnName(4));
Range range = worksheet.Range(titlerang);
range.Merge();
worksheet.Cell("A1").MergedWithCell.Value = "Меню " + dto.MenuTitle;
range.AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Columns[0].Width = 180;
worksheet.Cell(1, 1).Value = "Наименование блюд";
worksheet.Columns[1].Width = 450;
worksheet.Columns[2].Width = 90;
worksheet.Columns[3].Width = 120;
worksheet.Rows[0].Height = 50;
worksheet.Rows[0].AlignmentVertical=AlignmentVertical.Centered;
worksheet.Rows[1].Height = 50;
worksheet.Rows[1].AlignmentVertical = AlignmentVertical.Centered;
worksheet.Range("A2:D2").FillPattern = PatternStyle.Solid;
worksheet.Range("A2:D2").FillPatternForeColor = Color.FromArgb(48, 127, 217);
worksheet.Range("C2:D2").Merge();
worksheet.Cell(1, 2).MergedWithCell.Value = "Цена, грн";
worksheet.Range("A2:D2").AlignmentHorizontal = AlignmentHorizontal.Centered;
for (int i = 0; i < daycount; i++)
{
MenuForDayDto mfd = mfdays[i];
int strcount = i*catLength + 2;
string colname = string.Format("A{0}:D{1}", strcount + 1 + i, strcount + 1 + i);
worksheet.Range(colname).Merge();
worksheet.Cell(strcount + i, 0).MergedWithCell.Value = daynames[i];
worksheet.Range(colname).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Range(colname).FillPattern = PatternStyle.Solid;
worksheet.Range(colname).FillPatternForeColor = Color.FromArgb(144, 164, 187);
for (int j = 0; j < mfd.Dishes.Count; j++)
{
worksheet.Cell(strcount + j + 1 + i, 0).Value = mfd.Dishes[j].Category;
worksheet.Cell(strcount + j + 1 + i, 0).Indent = 2;
worksheet.Cell(strcount + j + 1 + i, 1).Value = mfd.Dishes[j].Title;
worksheet.Cell(strcount + j + 1 + i, 1).Indent = 2;
bool deskexists = !string.IsNullOrEmpty(mfd.Dishes[j].Description);
if (deskexists)
{
worksheet.Cell(strcount + j + 1 + i, 1).Value = mfd.Dishes[j].Title + ":" +
mfd.Dishes[j].Description;
worksheet.Cell(strcount + j + 1 + i, 1).Font = new Font("Arial", 12, FontStyle.Bold);
}
worksheet.Cell(strcount + j + 1 + i, 1).Wrap = true;
worksheet.Cell(strcount + j + 1 + i, 2).Value = mfd.Dishes[j].Price;
worksheet.Cell(strcount + j + 1 + i, 2).NumberFormatString = "#,##0.00";
worksheet.Rows[strcount + j + 1 + i].Height = (uint) (!deskexists ? 70 : 90);
worksheet.Rows[strcount + j + 1 + i].AlignmentVertical = AlignmentVertical.Centered;
}
string sumdaytotal = string.Format("D{0}:D{1}", strcount + 2 + i, strcount + 1 + i + catLength);
worksheet.Range(sumdaytotal).Merge();
worksheet.Range(sumdaytotal).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Range(sumdaytotal).AlignmentVertical = AlignmentVertical.Centered;
worksheet.Cell(strcount + 1 + i, 3).Value = mfd.TotalPrice;
worksheet.Range(sumdaytotal).NumberFormatString = "#,##0.00";
}
string totalstr = string.Format("A{0}:C{0}", daycount*(catLength+1) + 3);
worksheet.Range(totalstr).Merge();
worksheet.Cell(daycount*(catLength + 1) + 2, 0).Value = "Всего ";
worksheet.Range(totalstr).AlignmentHorizontal = AlignmentHorizontal.Right;
worksheet.Cell(daycount * (catLength + 1) + 2, 3).Value = weekMenuDto.SummaryPrice;
worksheet.Cell(daycount * (catLength + 1) + 2, 3).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Cell(daycount * (catLength + 1) + 2, 3).NumberFormatString = "#,##0.00";
worksheet.Rows[daycount * (catLength + 1) + 2].Height = 60;
worksheet.Rows[daycount * (catLength + 1) + 2].AlignmentVertical = AlignmentVertical.Centered;
worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;
worksheet.Range(allstr).Font = new Font("Arial", 14, FontStyle.Bold);
//string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
// @"ACSDining.Web\ExcelFiles\Menu.xls";
string pathstr = string.Format("~/ExcelFiles/Меню_{0}.xls", YearWeekHelp.GetWeekTitle(repository, dto.WeekYear));
string _path = HostingEnvironment.MapPath(pathstr);
if (File.Exists(_path))
{
File.Delete(_path);
}
try
{
document.SaveAs(_path);
}
catch (Exception)
{
throw;
}
// Close document
document.Close();
return _path;
}
public static string GetPlanOrdersExcelFileWeekYearDto(this IRepositoryAsync<WeekOrderMenu> repository,
ForExcelDataDto feDto)
{
string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
List<PlannedWeekOrderMenu> weekOrderMenus =
repository.GetRepositoryAsync<PlannedWeekOrderMenu>().OrdersMenuByWeekYear(feDto.WeekYear);
List<PlanUserWeekOrderDto> userWeekOrders =
weekOrderMenus.Select(woDto => PlanUserWeekOrderDto.MapDto(repository.Context, woDto)).ToList();
string[] dayNames = repository.Context.GetDayNames(feDto.WeekYear, true).Result;
double[] weekDishPrices = repository.Context.GetWeekDishPrices(feDto.WeekYear).Result;
double[] summaryDishQuantities = repository.Context.GetFactSumWeekUserCounts(feDto.WeekYear).Result;
WorkingWeek workWeek = repository.GetRepositoryAsync<MenuForWeek>().WorkWeekByWeekYear(feDto.WeekYear);
int workDayCount = workWeek.WorkingDays.Count(wd => wd.IsWorking);
int catLength = repository.GetRepositoryAsync<DishType>().GetAll().Count;
int dishcount = workDayCount*catLength;
int orderscount = userWeekOrders.Count;
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.Add("Заявки плановые");
string titlerang = String.Format("A1:{0}1", GetExcelColumnName(dishcount + 3));
Range range = worksheet.Range(titlerang);
range.Merge();
worksheet.Cell("A1").MergedWithCell.Value = "Заявки плановые " + feDto.DataString;
range.AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Cell(2, 0).Value = "№";
worksheet.Range("A2:A5").Merge();
worksheet.Cell(2, 1).Value = "Ф.И.О.";
worksheet.Range("B2:B5").Merge();
worksheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Centered;
string str;
string colname;
string colname_2;
string endcolname = GetExcelColumnName(dishcount + 3);
string allstr = string.Format("A{0}:{2}{1}", 1, userWeekOrders.Count + 6, endcolname);
colname = GetExcelColumnName(dishcount + 2);
worksheet.Cell(2 + 1, 3).Value = "Цена за одну порцию, грн";
str = String.Format("C4:{0}4", colname);
worksheet.Range(str).Merge();
worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;
int i = dishcount + 3;
colname = GetExcelColumnName(i);
worksheet.Cell(1, i - 1).Value = "Стоимость заказа за неделю";
str = String.Format("{0}2:{1}5", colname, colname);
worksheet.Range(str).Merge();
worksheet.Range(str).Wrap = true;
worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Columns[i - 1].Width = 100;
worksheet.Cell(1, i - 1).ShrinkToFit = true;
i = userWeekOrders.Count + 5;
worksheet.Cell(i, 0).Value = "Всего заказано";
str = String.Format("A{0}:B{1}", i + 1, i + 1);
worksheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Right;
worksheet.Range(str).Merge();
worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;
for (int j = 0; j < dishcount; j++)
{
worksheet.Cell(4, 2 + j).Value = weekDishPrices[j];
if ((j) % 4 != 0)
{
worksheet.Cell(4, 2 + j).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(4, 2 + j).RightBorderStyle = LineStyle.Thin;
}
i = 0;
for (int[] j = { 0 }; j[0] < workDayCount; j[0]++)
{
colname = GetExcelColumnName(j[0] * catLength + 3);
colname_2 = GetExcelColumnName(j[0] * catLength + 6);
var elementAtOrDefault = workWeek.WorkingDays.Where(wd => wd.IsWorking).ElementAtOrDefault(j[0]);
if (elementAtOrDefault != null)
worksheet.Cell(1, j[0] * catLength + 3).Value = elementAtOrDefault.DayOfWeek.Name;
str = String.Format("{0}2:{1}2", colname, colname_2);
worksheet.Range(str).Merge();
}
i = 2;
for (int j = 0; j < workDayCount; j++)
{
for (int k = 0; k < catLength; k++)
{
colname = GetExcelColumnName(2 + 1 + j * catLength + k);
worksheet.Cell(2, 2 + j * catLength + k).Value = dishCategories[k];
worksheet.Range(colname + "3").Rotation = 90;
if ((k) % 4 != 0)
{
worksheet.Cell(2, 2 + j * catLength + k).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(2, 2 + j * catLength + k).RightBorderStyle = LineStyle.Thin;
}
}
str = string.Format("A1:{0}5", GetExcelColumnName(dishcount + 3));
i = 5;
Color contentColor = Color.FromArgb(224, 232, 241);
Color nullColor = Color.FromArgb(6, 84, 156);
for (int j = 0; j < userWeekOrders.Count; j++)
{
var itsevenrow = (i + j) % 2 != 0;
PlanUserWeekOrderDto userweekorder = userWeekOrders[j];
worksheet.Cell(i + j, 0).Value = j + 1;
worksheet.Cell(i + j, 1).Value = userweekorder.UserName;
if (itsevenrow)
{
worksheet.Cell(i + j, 0).FillPattern = PatternStyle.Solid;
worksheet.Cell(i + j, 0).FillPatternForeColor = contentColor;
worksheet.Cell(i + j, 1).FillPattern = PatternStyle.Solid;
worksheet.Cell(i + j, 1).FillPatternForeColor = contentColor;
}
worksheet.Cell(i + j, 1).ShrinkToFit = true;
for (int k = 0; k < dishcount + 1; k++)
{
var celval = userweekorder.UserWeekOrderDishes[k];
if (celval != 0.00)
{
worksheet.Cell(i + j, k + 2).Value = celval;
}
if (itsevenrow)
{
worksheet.Cell(i + j, k + 2).FillPattern = PatternStyle.Solid;
worksheet.Cell(i + j, k + 2).FillPatternForeColor = contentColor;
}
if ((k) % 4 != 0)
{
worksheet.Cell(i + j, k + 2).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(i + j, k + 2).RightBorderStyle = LineStyle.Thin;
}
}
i = userWeekOrders.Count + 5;
Color evcolor = Color.FromArgb(68, 240, 196);
for (int j = 0; j < workDayCount; j++)
{
for (int k = 0; k < catLength; k++)
{
Cell curCell = worksheet.Cell(i, j * catLength + k + 2);
curCell.Value = summaryDishQuantities[j * catLength + k];
if (j % 2 == 0)
{
curCell.FillPattern = PatternStyle.Solid;
curCell.FillPatternForeColor = evcolor;
}
if ((k) % 4 != 0)
{
worksheet.Cell(i, j * catLength + k + 2).LeftBorderStyle = LineStyle.Thin;
}
worksheet.Cell(i, j * catLength + k + 2).RightBorderStyle = LineStyle.Thin;
}
}
worksheet.Cell(i, dishcount + 2).Value = userWeekOrders.Sum(uo => uo.UserWeekOrderDishes[dishcount]);
string headerstr = string.Format("C{0}:{2}{1}", 1, 3, endcolname);
worksheet.Range(headerstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
string headerusnamesstr = string.Format("A{0}:B{1}", 1, 5);
worksheet.Range(headerusnamesstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Range(headerusnamesstr).AlignmentVertical = AlignmentVertical.Centered;
string usernames = string.Format("A{0}:B{1}", 6, userWeekOrders.Count + 5);
worksheet.Range(usernames).AlignmentHorizontal = AlignmentHorizontal.Left;
string userquantistr = string.Format("C{0}:{2}{1}", 5, userWeekOrders.Count + 6, endcolname);
//worksheet.Range(userquantistr).NumberFormatString = "#.#";
worksheet.Range(userquantistr).AlignmentHorizontal = AlignmentHorizontal.Centered;
string sumcol = string.Format("{0}{1}:{2}{3}", endcolname, 5, endcolname, userWeekOrders.Count + 6);
worksheet.Range(sumcol).NumberFormatString = "#,##0.00";
worksheet.Range(sumcol).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Columns[0].Width = 40;
worksheet.Columns[1].Width = 250;
//worksheet.Columns[1].AutoFit();
worksheet.Range(allstr).Font = new Font("Arial", 13, FontStyle.Bold);
for (int j = 0; j < userWeekOrders.Count + 6; j++)
{
worksheet.Rows[j].Height = (uint)((j != 2) ? 35 : 150);
worksheet.Rows[j].AlignmentVertical = AlignmentVertical.Centered;
}
//string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
// @"ACSDining.Web\ExcelFiles\ЗаявкиПлан.xls";
string pathstr = string.Format("~/ExcelFiles/ЗаявкиПлан_{0}.xls", YearWeekHelp.GetWeekTitle(repository.GetRepositoryAsync<MenuForWeek>(), feDto.WeekYear));
string _path = HostingEnvironment.MapPath(pathstr);
if (File.Exists(_path))
{
File.Delete(_path);
}
document.SaveAs(_path);
// Close document
document.Close();
return _path;
}
public static string GetExcelFileFromPaimentsModel(this IRepositoryAsync<WeekOrderMenu> repository,
ForExcelDataDto feDto)
{
WeekPaimentDto dto = WeekPaimentDto.GetMapDto(repository.GetRepositoryAsync<WeekPaiment>(), feDto.WeekYear);
string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
WorkingWeek workWeek = repository.GetRepositoryAsync<MenuForWeek>().WorkWeekByWeekYear(feDto.WeekYear);
int workDayCount = workWeek.WorkingDays.Count(wd => wd.IsWorking);
int catLength = repository.GetRepositoryAsync<DishType>().GetAll().Count;
List<UserWeekPaimentDto> paimentList = dto.UserWeekPaiments;
//Цены за каждое блюдо в меню на рабочей неделе
double[] unitPrices = dto.WeekDishPrices;
int dishcount = workDayCount*catLength;
//Выделяем память для искомых данных ( +1 для хранения суммы всех ожидаемых проплат)
double[] unitPricesTotal =
new double[dishcount + 1];
for (int i = 0; i < dishcount; i++)
{
unitPricesTotal[i] = dto.SummaryDishPaiments[i];
}
unitPricesTotal[dishcount] = dto.SummaryDishPaiments.Sum();
Spreadsheet document = new Spreadsheet();
//document.Workbook.Worksheets.DeleteAll();
// Get worksheet by name
Worksheet workSheet = document.Workbook.Worksheets.Add("Оплаты");
// I created Application and Worksheet objects before try/catch,
// so that i can close them in finnaly block.
// It's IMPORTANT to release these COM objects!!
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
if (workSheet != null)
{
string endcolname = GetExcelColumnName(dishcount + 6);
string allstr = string.Format("A1:{0}{1}", endcolname, paimentList.Count + 6);
workSheet.Range(allstr).Font = new Font("Arial", 13, FontStyle.Bold);
workSheet.Cell(1, 0).Value = "№";
workSheet.Range("A2:A5").Merge();
workSheet.Range("B2:B5").Merge();
workSheet.Cell(1, 1).MergedWithCell.Value = "Ф.И.О.";
workSheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Centered;
string titlerang = String.Format("A1:{0}1", GetExcelColumnName(dishcount + 6));
Range range = workSheet.Range(titlerang);
range.Merge();
workSheet.Cell("A1").MergedWithCell.Value = "Оплаты на " + feDto.DataString;
range.AlignmentHorizontal = AlignmentHorizontal.Centered;
int i = 0;
string str;
string colname;
string colname_2;
for (int[] j = {0}; j[0] < workDayCount; j[0]++)
{
colname = GetExcelColumnName(j[0]*catLength + 3);
colname_2 = GetExcelColumnName(j[0]*catLength + 6);
var elementAtOrDefault = workWeek.WorkingDays.Where(wd => wd.IsWorking).ElementAtOrDefault(j[0]);
if (elementAtOrDefault != null)
workSheet.Cell(1, j[0]*catLength + 3).Value = elementAtOrDefault.DayOfWeek.Name;
str = String.Format("{0}2:{1}2", colname, colname_2);
workSheet.Range(str).Merge();
}
i += dishcount + 2;
colname = GetExcelColumnName(i + 1);
str = String.Format("{0}2:{1}5", colname, colname);
workSheet.Range(str).Merge();
workSheet.Cell(2, i).MergedWithCell.Value = "Сумма к оплате ";
workSheet.Range(str).Rotation = 90;
workSheet.Columns[i].Width = 90;
i++;
colname = GetExcelColumnName(i + 1);
workSheet.Cell(2, i).Value = "Оплата за неделю";
str = String.Format("{0}2:{1}5", colname, colname);
workSheet.Range(str).Merge();
workSheet.Range(str).Rotation = 90;
workSheet.Columns[i].Width = 90;
i++;
colname = GetExcelColumnName(i + 1);
workSheet.Cell(2, i).Value = "Баланс";
str = String.Format("{0}2:{1}5", colname, colname);
workSheet.Range(str).Merge();
workSheet.Range(str).Rotation = 90;
workSheet.Columns[i].Width = 80;
i++;
colname = GetExcelColumnName(i + 1);
workSheet.Cell(2, i).Value = "Примечание";
str = String.Format("{0}2:{1}5", colname, colname);
workSheet.Range(str).Merge();
workSheet.Range(str).Rotation = 90;
workSheet.Columns[i].Width = 90;
colname = GetExcelColumnName(dishcount + 2);
workSheet.Cell(3, 2).Value = "Цена за одну порцию, грн";
str = String.Format("C4:{0}4", colname);
workSheet.Range(str).Merge();
workSheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;
i = paimentList.Count + 5;
str = String.Format("A{0}:B{1}", i + 1, i + 1);
workSheet.Range(str).Merge();
workSheet.Cell(i, 0).MergedWithCell.Value = "Итого";
workSheet.Cell(i, 0).AlignmentHorizontal = AlignmentHorizontal.Right;
workSheet.Columns[i].Width = 120;
workSheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
workSheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;
i = 2;
for (int j = 0; j < workDayCount; j++)
{
for (int k = 0; k < catLength; k++)
{
colname = GetExcelColumnName(3 + j*catLength + k);
workSheet.Cell(2, 2 + j*catLength + k).Value = dishCategories[k];
workSheet.Range(colname + "3").Rotation = 90;
if ((k) % 4 != 0)
{
workSheet.Cell(2, 2 + j * catLength + k).LeftBorderStyle = LineStyle.Thin;
}
workSheet.Cell(2, 2 + j * catLength + k).RightBorderStyle = LineStyle.Thin;
}
}
double[] dishprices = unitPrices;
for (int j = 0; j < dishcount; j++)
{
colname = GetExcelColumnName(i + j);
workSheet.Cell(4, i + j).Value = dishprices[j];
if ((j) % 4 != 0)
{
workSheet.Cell(4, j + 2).LeftBorderStyle = LineStyle.Thin;
}
workSheet.Cell(4, j + 2).RightBorderStyle = LineStyle.Thin;
}
i = paimentList.Count + 5;
for (int j = 0; j < dishcount; j++)
{
colname = GetExcelColumnName(j + 3);
workSheet.Cell(i, j + 2).Value = unitPricesTotal[j];
if ((j) % 4 != 0)
{
workSheet.Cell(i, j + 2).LeftBorderStyle = LineStyle.Thin;
}
workSheet.Cell(i, j + 2).RightBorderStyle = LineStyle.Thin;
}
colname = GetExcelColumnName(dishcount + 3);
workSheet.Cell(i, dishcount + 2).Value = unitPricesTotal[dishcount];
colname = GetExcelColumnName(dishcount + 4);
workSheet.Cell(i, dishcount + 3).Value = paimentList.Sum(up => up.Paiment);
colname = GetExcelColumnName(dishcount + 5);
workSheet.Cell(i, dishcount + 4).Value = paimentList.Sum(up => up.Balance);
i = 5;
Color contentColor = Color.FromArgb(227, 238, 245);
Color nullColor = Color.FromArgb(6, 84, 156);
for (int j = 0; j < paimentList.Count; j++)
{
bool ev = (i + j)%2 != 0;
UserWeekPaimentDto userpai = paimentList[j];
workSheet.Cell(i + j, 0).Value = j + 1;
workSheet.Cell(i + j, 1).Value = userpai.UserName;
workSheet.Cell(i + j, 1).ShrinkToFit = true;
if (ev)
{
workSheet.Cell(i + j, 0).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, 0).FillPatternForeColor = contentColor;
workSheet.Cell(i + j, 1).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, 1).FillPatternForeColor = contentColor;
}
for (int k = 0; k < dishcount; k++)
{
colname = GetExcelColumnName(k + 2);
bool itsnulval = userpai.WeekPaiments[k] == 0.00;
if (!itsnulval)
{
workSheet.Cell(i + j, k + 2).Value = userpai.WeekPaiments[k];
}
if (ev)
{
workSheet.Cell(i + j, k + 2).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, k + 2).FillPatternForeColor = contentColor;
}
if ((k) % 4 != 0 )
{
workSheet.Cell(i + j, k + 2).LeftBorderStyle = LineStyle.Thin;
}
workSheet.Cell(i + j, k + 2).RightBorderStyle = LineStyle.Thin;
}
colname = GetExcelColumnName(dishcount + 2);
workSheet.Cell(i + j, dishcount + 2).Value = paimentList[j].WeekPaiments[workDayCount*catLength];
if (ev)
{
workSheet.Cell(i + j, dishcount + 2).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, dishcount + 2).FillPatternForeColor = contentColor;
}
colname = GetExcelColumnName(dishcount + 3);
if (ev)
{
workSheet.Cell(i + j, dishcount + 3).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, dishcount + 3).FillPatternForeColor = contentColor;
}
colname = GetExcelColumnName(dishcount + 4);
workSheet.Cell(i + j, dishcount + 4).Value = paimentList[j].Balance;
if (ev)
{
workSheet.Cell(i + j, dishcount + 4).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, dishcount + 4).FillPatternForeColor = contentColor;
}
colname = GetExcelColumnName(dishcount + 5);
workSheet.Cell(i + j, dishcount + 5).Value = paimentList[j].Note;
if (ev)
{
workSheet.Cell(i + j, dishcount + 5).FillPattern = PatternStyle.Solid;
workSheet.Cell(i + j, dishcount + 5).FillPatternForeColor = contentColor;
}
workSheet.Cell(i + j, dishcount + 5).TopBorderStyle = LineStyle.Medium;
workSheet.Cell(i + j, dishcount + 5).RightBorderStyle = LineStyle.Medium;
workSheet.Cell(i + j, dishcount + 5).BottomBorderStyle = LineStyle.Medium;
}
colname = GetExcelColumnName(dishcount + 1);
string headerstr = string.Format("C{0}:{2}{1}", 1, 2, colname);
workSheet.Range(headerstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
string headerusnamesstr = string.Format("A{0}:B{1}", 2, 5);
workSheet.Range(headerusnamesstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
workSheet.Range(headerusnamesstr).AlignmentVertical = AlignmentVertical.Centered;
string usernames = string.Format("B{0}:B{1}", 6, paimentList.Count + 5);
workSheet.Range(usernames).AlignmentHorizontal = AlignmentHorizontal.Left;
string userquantistr = string.Format("C{0}:{2}{1}", 5, paimentList.Count + 6, endcolname);
//workSheet.Range(userquantistr).NumberFormatString = "0.0";
workSheet.Range(userquantistr).AlignmentHorizontal = AlignmentHorizontal.Centered;
string sumnotestart = GetExcelColumnName(dishcount + 2);
string sumnotend = GetExcelColumnName(dishcount + 5);
string sumcol = string.Format("{0}{1}:{2}{3}", sumnotestart, 5, sumnotend, paimentList.Count + 7);
workSheet.Range(sumcol).NumberFormatString = "#,##0.00";
workSheet.Range(sumcol).AlignmentHorizontal = AlignmentHorizontal.Centered;
workSheet.Columns[0].Width = 40;
workSheet.Columns[1].Width = 250;
//worksheet.Columns[1].AutoFit();
for (int j = 0; j < paimentList.Count + 6; j++)
{
workSheet.Rows[j].Height = (uint)((j != 2) ? 35 : 120);
workSheet.Rows[j].AlignmentVertical = AlignmentVertical.Centered;
}
//string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
// @"ACSDining.Web\ExcelFiles\Оплаты.xls";
string pathstr = string.Format("~/ExcelFiles/Оплаты_{0}.xls", YearWeekHelp.GetWeekTitle(repository.GetRepositoryAsync<MenuForWeek>(), dto.WeekYearDto));
string _path = HostingEnvironment.MapPath(pathstr);
// delete output file if exists already
if (File.Exists(_path))
{
File.Delete(_path);
}
document.SaveAs(_path);
// Close document
document.Close();
return _path;
}
}
catch (Exception ex)
{
throw;
}
return null;
}
