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();
}
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();
}
public SpreadsheetProcessor(Spreadsheet spreadsheet, ISpreadsheetValidator validator = null)
{
_spreadsheet = spreadsheet;
_memoryCache = new ExtendedLazy<Cell, object>[spreadsheet.RowCount, spreadsheet.ColumnCount];
_evaluateCellFunct = EvaluateCell;
_validator = validator ?? new RecursionDetectionValidator();
}
private void CheckRecursion(Spreadsheet spreadsheet, Cell current, ISet<CellAddress> stack)
{
try
{
var dependencies = PooledHashSet<CellAddress>.GetInstance();
try
{
GetDependencies(current.Expression, dependencies);
if (dependencies.Overlaps(stack))
throw new CircularCellRefereceException(Resources.CircularReference);
stack.Add(current.Address);
foreach (var address in dependencies)
{
CheckRecursion(spreadsheet, spreadsheet[address], stack);
}
stack.Remove(current.Address);
}
finally
{
dependencies.Free();
}
}
catch (CircularCellRefereceException ex)
{
throw SpreadsheetException.AddCellAddressToErrorStack(ex, current.Address);
}
}
public void SpreadsheetHelper_Save()
{
Spreadsheet doc = new Spreadsheet();
doc.CreateAndAppendWorksheet<TestClass>(tcs);
string filename = string.Format("{0:yyyyMMdd_hhmmss}testfile1.xlsx", DateTime.Now);
doc.Save(filename);
Assert.IsTrue(File.Exists(filename));
}
public override string CellText(Spreadsheet spreadsheet)
{
if (spreadsheet is TSVSpreadsheet)
{
return text;
}
throw new NotImplementedException();
}
public void SpreadsheetHelper_Doc_CreateHeader()
{
Spreadsheet doc = new Spreadsheet();
doc.CreateAndAppendWorksheet<TestClass>(tcs);
PropertyInfo[] props = typeof(TestClass).GetProperties();
int columnIndex = 1;
foreach (PropertyInfo prop in typeof(TestClass).GetProperties())
{
Assert.AreEqual(prop.Name,doc.doc.GetCellValueAsString(1,columnIndex));
columnIndex++;
}
}
public void Validate(Spreadsheet spreadsheet, Cell cell)
{
var hashset = PooledHashSet<CellAddress>.GetInstance();
try
{
CheckRecursion(spreadsheet, cell, hashset);
}
finally
{
hashset.Free();
}
}
/// <summary>
/// Opens a new spreadsheet GUI window.
/// </summary>
public Form1()
{
InitializeComponent();
_spreadsheet = null;
_filename = "Not Connected";
Text = "Spreadsheet Program - " + _filename;
// Registers my displaySelection method.
spreadsheetPanel1.SelectionChanged += DisplaySelection;
spreadsheetPanel1.SetSelection(0, 0);
}
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 void CalculateInvalidReferencedCellTest()
{
string[] initialRows = new string[2];
initialRows[0] = "3\t'Hello";
initialRows[1] = "=A1+B1\t=A1+1"; // Cell B1 contains text line which cannot be used as a part of expression
Spreadsheet spreadsheet = new Spreadsheet { Data = GetMatrix(initialRows) };
spreadsheet.Calculate();
string[] resultRows = new string[2];
resultRows[0] = "3\tHello";
resultRows[1] = "#Referenced cell cannot be part of expression\t4";
string[,] resultTable = GetMatrix(resultRows);
Assert.IsTrue(MatrixesAreEqual(spreadsheet.Data, resultTable));
}
public void CalculateNonExistentReferencesTest()
{
string[] initialRows = new string[2];
initialRows[0] = "3\t7";
initialRows[1] = "=A1+C1\t=B1+1"; // Cell C1 doesn't exist
Spreadsheet spreadsheet = new Spreadsheet { Data = GetMatrix(initialRows) };
spreadsheet.Calculate();
string[] resultRows = new string[2];
resultRows[0] = "3\t7";
resultRows[1] = "#Referenced cell doesn't exist\t8";
string[,] resultTable = GetMatrix(resultRows);
Assert.IsTrue(MatrixesAreEqual(spreadsheet.Data, resultTable));
}
public void SpreadsheetHelper_DisplayWidth()
{
TestClassWithDisplayWidth tc1 = new TestClassWithDisplayWidth();
Spreadsheet doc = new Spreadsheet();
doc.CreateAndAppendWorksheet<TestClassWithDisplayWidth>(new List<TestClassWithDisplayWidth>() { tc1 });
PropertyInfo[] props = typeof(TestClassWithDisplayWidth).GetProperties();
int columnIndex = 1;
foreach (PropertyInfo prop in typeof(TestClassWithDisplayWidth).GetProperties())
{
foreach (DisplayWidth dw in prop.GetCustomAttributes(typeof(DisplayWidth)))
{
Assert.AreEqual(dw.Width, doc.doc.GetColumnWidth(columnIndex));
break;
}
columnIndex++;
}
}
public void CalculateLoopReferencesTest()
{
string[] initialRows = new string[3];
initialRows[0] = "12\t=C2\t=A2*7\t'Sample"; // Third element contains loop reference
initialRows[1] = "=A1+B1*C1/5\t=A2*B1\t=B3-C3\t'Spread"; // First and second elements contains loop references
initialRows[2] = "'Test\t=4-3\t5\t'Sheet";
Spreadsheet spreadsheet = new Spreadsheet { Data = GetMatrix(initialRows) };
spreadsheet.Calculate();
string[] resultRows = new string[3];
resultRows[0] = "12\t-4\t#The cell contains loop reference\tSample";
resultRows[1] = "#The cell contains loop reference\t#The cell contains loop reference\t-4\tSpread";
resultRows[2] = "Test\t1\t5\tSheet";
string[,] resultTable = GetMatrix(resultRows);
Assert.IsTrue(MatrixesAreEqual(spreadsheet.Data, resultTable));
}
/// <summary>
/// Opens a new spreadsheet GUI window.
/// </summary>
/// <param name="filename">File to be opened.</param>
public Form1(String server, int port, string filename, string password, bool isCreate)
{
InitializeComponent();
try
{
_spreadsheet = new SocialSpreadSheet.Spreadsheet(server, port, filename, password, isCreate, isValid, normalize, "ps6");
_filename = filename;
// Registers my displaySelection method.
spreadsheetPanel1.SelectionChanged += DisplaySelection;
spreadsheetPanel1.SetSelection(0, 0);
RegisterHandlers();
}
catch (SpreadsheetReadWriteException ex)
{
MessageBox.Show("Spreadsheet could not open. " + ex.Message + " Please close this spreadsheet window.",
"Open Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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 MainForm()
{
InitializeComponent();
workbook = new Spreadsheet(numRows, numCols);
workbook.CellPropertyChanged += Cell_PropertyChanged; // Subrscribe to CellPropetyChanged event
int i = 0, character;
for (i = 0; i < numCols; i++) // Initialize columns
{
character = 'A' + i;
DataColumn col = new DataColumn(Convert.ToString((char)character));
dataGridView.Columns.Add("col" + Convert.ToString(i), Convert.ToString((char)character));
}
for (i = 1; i <= numRows; i++) // Initialize rows
{
dataGridView.Rows.Add();
dataGridView.Rows[i-1].HeaderCell.Value = Convert.ToString(i);
}
}
private void CreateProductsExcelReport()
{
List<Product> products;
using (var db = new ToysSqliteContext())
{
products = db.Products.Where(i => i.Id == i.Id).ToList();
}
if (File.Exists(ExcelPath))
{
File.Delete(ExcelPath);
}
Spreadsheet document = new Spreadsheet();
Worksheet sheet = document.Workbook.Worksheets.Add("Product Reports");
int counter = 2;
sheet.Cell(1, 1).Value = "Id";
sheet.Cell(1, 2).Value = "Sku";
sheet.Cell(1, 3).Value = "Description";
sheet.Cell(1, 4).Value = "WholesalePrice";
sheet.Cell(1, 5).Value = "RetailPrice";
sheet.Cell(1, 6).Value = "TradeDiscount";
sheet.Cell(1, 7).Value = "TradeDiscountRate";
foreach (var product in products)
{
sheet.Cell(counter, 1).Value = product.Id;
sheet.Cell(counter, 2).Value = product.Sku;
sheet.Cell(counter, 3).Value = product.Description;
sheet.Cell(counter, 4).Value = product.WholesalePrice;
sheet.Cell(counter, 5).Value = product.RetailPrice;
sheet.Cell(counter, 6).Value = product.TradeDiscount;
sheet.Cell(counter, 7).Value = product.TradeDiscountRate;
counter++;
}
document.SaveAs(ExcelPath);
}
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 IEnumerable<object> Evaluate(Spreadsheet spreadsheet, Func<Cell,object> evaluation)
{
return spreadsheet.AsParallel()
.AsOrdered()
.Select(evaluation);
}
public void Test2()
{
AbstractSpreadsheet s = new Spreadsheet();
s.GetCellContents("AA");
}
public void Test5()
{
AbstractSpreadsheet s = new Spreadsheet();
s.SetContentsOfCell("A1A", "1.5");
}
public void TestMethod3()
{
AbstractSpreadsheet s1 = new Spreadsheet();
s1.GetCellContents("hello");
}
public void TestMethod25()
{
string r = "[a-zA-Z][0-9]";
AbstractSpreadsheet s1 = new Spreadsheet(new System.Text.RegularExpressions.Regex(r));
}
public void TestMethod2()
{
AbstractSpreadsheet s1 = new Spreadsheet();
s1.GetCellContents(null);
}
public void TestMethod19()
{
AbstractSpreadsheet s1 = new Spreadsheet();
s1.SetContentsOfCell("A1", "=A1");
}
public void TestMethod1()
{
AbstractSpreadsheet s1 = new Spreadsheet();
}
static void Main(string[] args)
{
// Create new Spreadsheet object
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.RegistrationName = "demo";
spreadsheet.RegistrationKey = "demo";
// Add new worksheet
Worksheet sheet = spreadsheet.Workbook.Worksheets.Add("Sample");
// Add few random numbers
int length = 10;
Random rnd = new Random();
for (int i = 0; i < length; i++)
{
sheet.Cell(i, 0).Value = rnd.Next(10);
sheet.Cell(i, 1).Value = rnd.Next(10);
}
// Add charts to worksheet
Chart columnChart = sheet.Charts.AddChartAndFitInto(1, 3, 16, 9, ChartType.ColumnClustered);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
columnChart = sheet.Charts.AddChartAndFitInto(1, 10, 16, 16, ChartType.ColumnStacked);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
columnChart = sheet.Charts.AddChartAndFitInto(1, 17, 16, 23, ChartType.ColumnStacked100);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
columnChart = sheet.Charts.AddChartAndFitInto(17, 3, 32, 9, ChartType.ColumnClustered3D);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
columnChart = sheet.Charts.AddChartAndFitInto(17, 10, 32, 16, ChartType.ColumnStacked3D);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
columnChart = sheet.Charts.AddChartAndFitInto(17, 17, 32, 23, ChartType.ColumnStacked1003D);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
columnChart = sheet.Charts.AddChartAndFitInto(33, 10, 48, 16, ChartType.Column3D);
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 0, length - 1, 0)));
columnChart.SeriesCollection.Add(new Series(sheet.Range(0, 1, length - 1, 1)));
// Save it as XLS
spreadsheet.SaveAs("Output.xls");
// Close the document
spreadsheet.Close();
// Cleanup
spreadsheet.Dispose();
// Open generated XLS file in default associated application
Process.Start("Output.xls");
}
public SimpleXMLConverter(Spreadsheet document)
{
this.document = document;
}
public void Test17()
{
AbstractSpreadsheet s = new Spreadsheet();
Assert.IsFalse(s.GetNamesOfAllNonemptyCells().GetEnumerator().MoveNext());
}
public void SpreadsheetHelper()
{
Spreadsheet doc = new Spreadsheet();
Assert.IsNotNull(doc);
}
public void TestMethod39()
{
string path = @"..\..\testfiles\s10.xml";
AbstractSpreadsheet s1 = new Spreadsheet(new StreamReader(path), reg);
}
public void TestMethod6()
{
AbstractSpreadsheet s1 = new Spreadsheet();
s1.SetContentsOfCell(null, "5");
}
public void TestMethod7()
{
AbstractSpreadsheet s1 = new Spreadsheet();
s1.SetContentsOfCell("hello", "5");
}
static void GetData()
{
string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly }; //delete token folder to refresh scope
UserCredential credential;
using (var stream =
new FileStream(@"D:\_Working\_3PSolution\myproject\research\google_sheets_v4\client_id.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
if (credential != null)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
// The spreadsheet to request.
string spreadsheetId = "1Ecm_3kKV4Wgz8BpZhPeMez5fy2pZuNSr_BNojTmOPwU"; // TODO: Update placeholder value.
// The ranges to retrieve from the spreadsheet.
List <string> ranges = new List <string>(); // TODO: Update placeholder value.
ranges.Add("Sheet1!A1:F");
ranges.Add("Sheet2!A1:N");
ranges.Add("Sheet3!A1:N");
// True if grid data should be returned.
// This parameter is ignored if a field mask was set in the request.
bool includeGridData = false; // TODO: Update placeholder value.
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.Ranges = ranges;
request.IncludeGridData = includeGridData;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Spreadsheet response = request.Execute();
// Data.Spreadsheet response = await request.ExecuteAsync();
string str = JsonConvert.SerializeObject(response);
// TODO: Change code below to process the `response` object:
Console.WriteLine(str);
}
Console.ReadLine();
}
public void InvalidNameTest2()
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.SetContentsOfCell(null, "45");
}
public void Test4()
{
AbstractSpreadsheet s = new Spreadsheet();
s.SetContentsOfCell(null, "1.5");
}
public void joinSpreadsheet(string server, int port, string filename, string password, bool isCreate)
{
if (_spreadsheet == null)
{
_spreadsheet = new Spreadsheet(server, port, filename, password, isCreate, isValid, normalize, "ps6");
_filename = filename;
RegisterHandlers();
return;
}
// Something's already open.
SpreadsheetGuiApplicationContext.GetAppContext().RunForm(new Form1(server, port, filename, password, isCreate));
}
public void Test3()
{
AbstractSpreadsheet s = new Spreadsheet();
Assert.AreEqual("", s.GetCellContents("A2"));
}
static void SaveBuildMetadata(BuildMetadata buildMetadata, BuildTarget buildTarget)
{
foreach (var extraField in BuildSystemSettings.Instance.ExtraFields)
{
s_Headers.Add(extraField.Name);
}
if (string.IsNullOrEmpty(BuildSystemSettings.Instance.SpreadsheetId))
{
Debug.LogError("Versions Spreadsheet Id is empty");
return;
}
var spreadsheet = new Spreadsheet(BuildSystemSettings.Instance.SpreadsheetId);
spreadsheet.Load();
if (spreadsheet.SyncErrorMassage != null)
{
Debug.LogError(spreadsheet.SyncErrorMassage);
throw new Exception(spreadsheet.SyncErrorMassage);
}
var sheetName = $"{buildMetadata.Version} - {buildTarget}";
var versionsSheet = spreadsheet.Sheets.FirstOrDefault(sh => sheetName.Equals(sh.Name));
var rangeAppend = $"{sheetName}!A:K";
var buildNumber = 0;
if (versionsSheet == null)
{
spreadsheet.CreateGoogleSheet(sheetName);
spreadsheet.AppendGoogleCell(rangeAppend, s_Headers);
if (spreadsheet.SyncErrorMassage != null)
{
Debug.LogError(spreadsheet.SyncErrorMassage);
throw new Exception(spreadsheet.SyncErrorMassage);
}
}
else
{
var cell = versionsSheet.GetCell(versionsSheet.Rows.Count() - 1, 0);
buildNumber = cell != null
? versionsSheet.GetCell(versionsSheet.Rows.Count() - 1, 0).GetValue <int>()
: 0;
}
buildMetadata.BuildNumber = buildNumber + 1;
Debug.LogWarning("Setting build number to " + buildMetadata.BuildNumber);
PlayerSettings.Android.bundleVersionCode = buildMetadata.BuildNumber;
PlayerSettings.iOS.buildNumber = buildMetadata.BuildNumber.ToString();
var commitValue = buildMetadata.CommitShortHash;
if (!string.IsNullOrEmpty(BuildSystemSettings.Instance.GitHubRepository))
{
commitValue = $"=HYPERLINK(\"https://github.com/{BuildSystemSettings.Instance.GitHubRepository}/commit/{buildMetadata.CommitHash}\",\"{buildMetadata.CommitShortHash}\")";
}
if (UnityCloudBuildHooks.IsRunningOnUnityCloud)
{
var manifest = (TextAsset)Resources.Load("UnityCloudBuildManifest.json");
if (manifest != null)
{
var manifestDict = Json.Deserialize(manifest.text) as Dictionary <string, object>;
foreach (var kvp in manifestDict)
{
// Be sure to check for null values!
var value = kvp.Value != null?kvp.Value.ToString() : string.Empty;
foreach (var extraField in BuildSystemSettings.Instance.ExtraFields)
{
extraField.Value = extraField.Value.Replace("{" + kvp.Key + "}", value);
}
}
}
else
{
Debug.LogWarning("[IncrementBuildNumber] UnityCloudBuildManifest.json not found");
}
}
var appendList = new List <object>
{
buildMetadata.BuildNumber,
buildMetadata.Version,
buildMetadata.MachineName,
buildMetadata.BranchName,
buildMetadata.CommitMessage,
commitValue,
buildMetadata.BuildTime.ToString("G"),
buildMetadata.CommitTime.ToString("G")
};
foreach (var extraField in BuildSystemSettings.Instance.ExtraFields)
{
appendList.Add(extraField.Value);
}
spreadsheet.AppendGoogleCell(rangeAppend, appendList);
if (spreadsheet.SyncErrorMassage != null)
{
Debug.LogError(spreadsheet.SyncErrorMassage);
}
}
public void Test1()
{
AbstractSpreadsheet s = new Spreadsheet();
s.GetCellContents(null);
}
public void InvalidNameTest1()
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.SetContentsOfCell("466fg", "45");
}
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;
}
public void TestSetCellContentsSSExceptions2()
{
Spreadsheet sheet = new Spreadsheet();
sheet.SetCellContents("1A", "cs3500");
}
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 void TestSetCellContentsSDExceptions2()
{
Spreadsheet sheet = new Spreadsheet();
sheet.SetCellContents("1A", 3.7);
}
public void SpreadsheetHelper_Save_MissingPath()
{
Spreadsheet doc = new Spreadsheet();
doc.CreateAndAppendWorksheet<TestClass>(tcs);
doc.Save("");
}
public void TestSetContentsFormulaEmptyCircular()
{
Spreadsheet ss = new Spreadsheet();
ss.SetContentsOfCell("a1", "=a1+a2");
}
public void Test12()
{
AbstractSpreadsheet s = new Spreadsheet();
s.SetContentsOfCell("AZ", "=2");
}
public void testGetNamesOfEmptySpreadsheet()
{
Spreadsheet ss = new Spreadsheet();
Assert.AreEqual(0, new HashSet <string>(ss.GetNamesOfAllNonemptyCells()).Count);
}
public void SpreadsheetHelper_OrderProperties()
{
Spreadsheet doc = new Spreadsheet();
List<PropertyInfo> props = doc.OrderProperties(typeof(TestClassWithOrder));
Assert.AreEqual(props[0].Name, "TestString2");
}
public void TestMethod2()
{
var spreadsheet = new Spreadsheet(Resources.sample2);
Assert.AreEqual(9, spreadsheet.GetChecksumV2());
}
private void FailedEventHandler(object sender, FailedEventArgs message)
{
MessageBox.Show("Your request failed. " + message.Message.Last(), "Failure!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
if (message.Message.First().StartsWith("CREATE") || message.Message.First().StartsWith("JOIN"))
{
DeregisterHandlers();
_spreadsheet = null;
}
}
public void Test7()
{
AbstractSpreadsheet s = new Spreadsheet();
s.SetContentsOfCell("A8", (string)null);
}
public void InvalidTextParameter()
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.SetContentsOfCell("d4", null);
}
protected void Spreadsheet_ServerCsvExporting(object sender, Syncfusion.JavaScript.Web.SpreadsheetEventArgs e)
{
var args = e.Arguments;
Spreadsheet.Save(args["sheetModel"].ToString(), args["sheetData"].ToString(), args["fileName"].ToString(), ExportFormat.CSV);
}
private void CleanSpreadsheet()
{
DeregisterHandlers();
_spreadsheet = null;
spreadsheetPanel1.Invoke(new Action(() => { spreadsheetPanel1.Clear(); }));
CellNameBox.Invoke(new Action(() => { CellNameBox.Text = string.Empty; }));
ValueBox.Invoke(new Action(() => { ValueBox.Text = string.Empty; }));
ContentBox.Invoke(new Action(() => { ContentBox.Text = string.Empty; }));
this.Invoke(new Action(() => { this.Text = "Spreadsheet Program - Not Connected"; }));
}
public void CircularTest1()
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.SetContentsOfCell("a1", "=a1");
}
