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"); }