public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) System.IO.Directory.CreateDirectory(dataDir); // Instantiating a "Products" DataTable object DataTable dataTable = new DataTable("Products"); // Adding columns to the DataTable object dataTable.Columns.Add("Product ID", typeof(Int32)); dataTable.Columns.Add("Product Name", typeof(string)); dataTable.Columns.Add("Units In Stock", typeof(Int32)); // Creating an empty row in the DataTable object DataRow dr = dataTable.NewRow(); // Adding data to the row dr[0] = 1; dr[1] = "Aniseed Syrup"; dr[2] = 15; // Adding filled row to the DataTable object dataTable.Rows.Add(dr); // Creating another empty row in the DataTable object dr = dataTable.NewRow(); // Adding data to the row dr[0] = 2; dr[1] = "Boston Crab Meat"; dr[2] = 123; // Adding filled row to the DataTable object dataTable.Rows.Add(dr); // Instantiate a new Workbook Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; // Create import options ImportTableOptions importOptions = new ImportTableOptions(); importOptions.IsFieldNameShown = true; importOptions.IsHtmlString = true; // Importing the values of 2nd column of the data table sheet.Cells.ImportData(dataTable, 1, 1, importOptions); // Save workbook book.Save(dataDir + "DataImport.out.xls"); // ExEnd:1 }
public Worksheet GenerateSummaryTableRow(Worksheet worksheet, ISummaryModel frmSummaryReport, int row) { worksheet.Cells.Merge(row, 1, 1, 2); worksheet.Cells.Merge(row, 3, 1, 4); ImportTableOptions tableOptions = new ImportTableOptions { CheckMergedCells = true, IsFieldNameShown = false }; worksheet.Cells.ImportCustomObjects(frmSummaryReport.SummaryRows.ToList(), row, 0, tableOptions); worksheet.Cells.ImportObjectArray( new object[] { "Total", "All Records", string.Empty, frmSummaryReport.TotalRowCount }, row + frmSummaryReport.SummaryRows.Count, 0, false); ApplyStyleToRow(worksheet, row, 0, frmSummaryReport.SummaryRows.Count, 7, _tableRowStyle); ApplyStyleToRow(worksheet, row + frmSummaryReport.SummaryRows.Count, 0, 1, 7, _tableTotalStyle); return(worksheet); }
public static void Run() { //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); //Output directory string outputDir = RunExamples.Get_OutputDirectory(); //Create the instance of Cells Data Table CellsDataTable cellsDataTable = new CellsDataTable(); //Load the sample workbook Workbook wb = new Workbook(sourceDir + "sampleImportTableOptionsShiftFirstRowDown.xlsx"); //Access first worksheet Worksheet ws = wb.Worksheets[0]; //Import data table options ImportTableOptions opts = new ImportTableOptions(); //We do now want to shift the first row down when inserting rows. opts.ShiftFirstRowDown = false; //Import cells data table ws.Cells.ImportData(cellsDataTable, 2, 2, opts); //Save the workbook wb.Save(outputDir + "outputImportTableOptionsShiftFirstRowDown-False.xlsx"); }
public static void Run() { //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); //Output directory string outputDir = RunExamples.Get_OutputDirectory(); Workbook workbook = new Workbook(sourceDir + "sampleMergedTemplate.xlsx"); List <Product> productList = new List <Product>(); //Creating collection of test items for (int i = 0; i < 3; i++) { Product product = new Product { ProductId = i, ProductName = "Test Product - " + i }; productList.Add(product); } ImportTableOptions tableOptions = new ImportTableOptions(); tableOptions.CheckMergedCells = true; tableOptions.IsFieldNameShown = false; //Insert data to excel template workbook.Worksheets[0].Cells.ImportCustomObjects((ICollection)productList, 1, 0, tableOptions); workbook.Save(outputDir + "sampleMergedTemplate_out.xlsx", SaveFormat.Xlsx); Console.WriteLine("ImportCustomObjectsToMergedArea executed successfully.\r\n"); }
public void WriteExcelRows <TModel>(Worksheet sheet, IEnumerable <TModel> rows, int firstRow) where TModel : class { ImportTableOptions tableOptions = new ImportTableOptions { IsFieldNameShown = false, InsertRows = false }; sheet.Cells.ImportCustomObjects((ICollection)rows, firstRow, 0, tableOptions); }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Instantiating a Workbook object Workbook workbook = new Workbook(); // Obtaining the reference of the worksheet Worksheet worksheet = workbook.Worksheets[0]; // Instantiating a "Products" DataTable object DataTable dataTable = new DataTable("Products"); // Adding columns to the DataTable object dataTable.Columns.Add("Product ID", typeof(Int32)); dataTable.Columns.Add("Product Name", typeof(string)); dataTable.Columns.Add("Units In Stock", typeof(Int32)); // Creating an empty row in the DataTable object DataRow dr = dataTable.NewRow(); // Adding data to the row dr[0] = 1; dr[1] = "Aniseed Syrup"; dr[2] = 15; // Adding filled row to the DataTable object dataTable.Rows.Add(dr); // Creating another empty row in the DataTable object dr = dataTable.NewRow(); // Adding data to the row dr[0] = 2; dr[1] = "Boston Crab Meat"; dr[2] = 123; // Adding filled row to the DataTable object dataTable.Rows.Add(dr); // Importing the contents of the data view to the worksheet ImportTableOptions options = new ImportTableOptions(); options.IsFieldNameShown = true; worksheet.Cells.ImportData(dataTable.DefaultView, 0, 0, options); // Saving the Excel file workbook.Save(dataDir + "output.xls"); // ExEnd:1 }
public static void Main(string[] args) { //ExStart:1 // The path to the documents directory. string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) { System.IO.Directory.CreateDirectory(dataDir); } //Instantiate a new Workbook Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; //Define List List <Person> list = new List <Person>(); list.Add(new Person("Mike", 25)); list.Add(new Person("Steve", 30)); list.Add(new Person("Billy", 35)); ImportTableOptions imp = new ImportTableOptions(); imp.InsertRows = true; //We pick a few columns not all to import to the worksheet //We pick a few columns not all to import to the worksheet sheet.Cells.ImportCustomObjects((System.Collections.ICollection)list, new string[] { "Name", "Age" }, true, 0, 0, list.Count, true, "dd/mm/yyyy", false); //Auto-fit all the columns book.Worksheets[0].AutoFitColumns(); //Save the Excel file book.Save(dataDir + "ImportedCustomObjects.out.xls"); }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) System.IO.Directory.CreateDirectory(dataDir); // Instantiate a new Workbook Workbook book = new Workbook(); Worksheet sheet= book.Worksheets[0]; // Define List List<Person> list = new List<Person>(); list.Add(new Person("Mike", 25)); list.Add(new Person("Steve", 30)); list.Add(new Person("Billy", 35)); ImportTableOptions imp = new ImportTableOptions(); imp.InsertRows = true; // We pick a few columns not all to import to the worksheet // We pick a few columns not all to import to the worksheet sheet.Cells.ImportCustomObjects((System.Collections.ICollection)list, new string[] { "Name","Age" }, true, 0, 0, list.Count, true, "dd/mm/yyyy", false); // Auto-fit all the columns book.Worksheets[0].AutoFitColumns(); // Save the Excel file book.Save(dataDir + "ImportedCustomObjects.out.xls"); }
public static void Main(string[] args) { //ExStart:1 // The path to the documents directory. string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); string output1Path = dataDir + "Output.out.xlsx"; string output2Path = dataDir + "Output.out.ods"; // Prepare a DataTable with some HTML formatted values DataTable dataTable = new DataTable("Products"); dataTable.Columns.Add("Product ID", typeof(Int32)); dataTable.Columns.Add("Product Name", typeof(string)); dataTable.Columns.Add("Units In Stock", typeof(Int32)); DataRow dr = dataTable.NewRow(); dr[0] = 1; dr[1] = "<i>Aniseed</i> Syrup"; dr[2] = 15; dataTable.Rows.Add(dr); dr = dataTable.NewRow(); dr[0] = 2; dr[1] = "<b>Boston Crab Meat</b>"; dr[2] = 123; dataTable.Rows.Add(dr); // Create import options ImportTableOptions importOptions = new ImportTableOptions(); importOptions.IsFieldNameShown = true; importOptions.IsHtmlString = true; // Create workbook Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; worksheet.Cells.ImportData(dataTable, 0, 0, importOptions); worksheet.AutoFitRows(); worksheet.AutoFitColumns(); workbook.Save(output1Path); workbook.Save(output2Path); //ExEnd:1 }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); string output1Path = dataDir + "Output.out.xlsx"; string output2Path = dataDir + "Output.out.ods"; // Prepare a DataTable with some HTML formatted values DataTable dataTable = new DataTable("Products"); dataTable.Columns.Add("Product ID", typeof(Int32)); dataTable.Columns.Add("Product Name", typeof(string)); dataTable.Columns.Add("Units In Stock", typeof(Int32)); DataRow dr = dataTable.NewRow(); dr[0] = 1; dr[1] = "<i>Aniseed</i> Syrup"; dr[2] = 15; dataTable.Rows.Add(dr); dr = dataTable.NewRow(); dr[0] = 2; dr[1] = "<b>Boston Crab Meat</b>"; dr[2] = 123; dataTable.Rows.Add(dr); // Create import options ImportTableOptions importOptions = new ImportTableOptions(); importOptions.IsFieldNameShown = true; importOptions.IsHtmlString = true; // Create workbook Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; worksheet.Cells.ImportData(dataTable, 0, 0, importOptions); worksheet.AutoFitRows(); worksheet.AutoFitColumns(); workbook.Save(output1Path); workbook.Save(output2Path); // ExEnd:1 }
public static void Run() { //List to hold data items List <DataItems> dis = new List <DataItems>(); //Define 1st data item and add it in list DataItems di = new DataItems(); di.Number1 = 2002; di.Number2 = 3502; di.Formula1 = "=SUM(A2,B2)"; di.Formula2 = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")"; dis.Add(di); //Define 2nd data item and add it in list di = new DataItems(); di.Number1 = 2003; di.Number2 = 3503; di.Formula1 = "=SUM(A3,B3)"; di.Formula2 = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")"; dis.Add(di); //Define 3rd data item and add it in list di = new DataItems(); di.Number1 = 2004; di.Number2 = 3504; di.Formula1 = "=SUM(A4,B4)"; di.Formula2 = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")"; dis.Add(di); //Define 4th data item and add it in list di = new DataItems(); di.Number1 = 2005; di.Number2 = 3505; di.Formula1 = "=SUM(A5,B5)"; di.Formula2 = "=HYPERLINK(\"https://www.aspose.com\",\"Aspose Website\")"; dis.Add(di); //Create workbook object Workbook wb = new Workbook(); //Access first worksheet Worksheet ws = wb.Worksheets[0]; //Specify import table options ImportTableOptions opts = new ImportTableOptions(); //Specify which field is formula field, here the last two fields are formula fields opts.IsFormulas = new bool[] { false, false, true, true }; //Import custom objects ws.Cells.ImportCustomObjects(dis, 0, 0, opts); //Calculate formula wb.CalculateFormula(); //Autofit columns ws.AutoFitColumns(); //Save the output Excel file wb.Save(outputDir + "outputSpecifyFormulaFieldsWhileImportingDataToWorksheet.xlsx"); Console.WriteLine("SpecifyFormulaFieldsWhileImportingDataToWorksheet executed successfully."); }
public static void ExportExcel(DataSet dsExport, string strFileName) { Workbook workbook = new Workbook(); int isheet = 0; bool bOverwrite = true; if (System.IO.File.Exists(strFileName)) { try { if (true) { System.IO.File.Delete(strFileName); } else { bOverwrite = false; } } catch (Exception ex) { return; } } foreach (DataTable dtExport in dsExport.Tables) { Worksheet wSheet; if (isheet == 0) { wSheet = workbook.Worksheets[0]; } else { wSheet = workbook.Worksheets[workbook.Worksheets.Add()]; } wSheet.Name = dtExport.TableName; ImportTableOptions importOptions = new ImportTableOptions(); importOptions.IsFieldNameShown = false; Range range = null; Style style = null; StyleFlag styleFlag = null; //DataTable dtExport; string[] lstColumnsID = null; string[] lstColumnsName = null; int iColLength, iRowLength, iNextRow = 0; int iBold = -1; int iCongThuc = -1; int iFore_Color = -1; int iBack_Color = -1; int iColor = -1; string strAddr1, strAddr2; iColLength = dtExport.Columns.Count; iRowLength = dtExport.Rows.Count; lstColumnsID = new string[iColLength]; lstColumnsName = new string[iColLength]; //Điền dữ liệu vào objColNames for (int i = 0; i < iColLength; i++) { lstColumnsID[i] = dtExport.Columns[i].ColumnName; lstColumnsName[i] = dtExport.Columns[i].ColumnName; } #region Create column header wSheet.Cells.ImportArray(lstColumnsName, iNextRow, 0, false); strAddr1 = wSheet.Cells[iNextRow, 0].Name; strAddr2 = wSheet.Cells[iNextRow, iColLength - 1].Name; range = wSheet.Cells.CreateRange(strAddr1 + ":" + strAddr2); style = workbook.Styles[workbook.Styles.Add()]; styleFlag = new StyleFlag(); styleFlag.All = true; //style.VerticalAlignment = TextAlignmentType.Center; //style.HorizontalAlignment = TextAlignmentType.Center; //style.Font.IsBold = true; style.IsTextWrapped = true; style.Number = 49; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].Color = Color.Black; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.LeftBorder].Color = Color.Black; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].Color = Color.Black; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].Color = Color.Black; range.ApplyStyle(style, styleFlag); //End column header #endregion #region Formating the columns before polulating the data style = workbook.Styles[workbook.Styles.Add()]; styleFlag = new StyleFlag(); styleFlag.All = true; iNextRow += 1; //End Format #endregion //workbook.Save(strFileName); //return; #region Polulating the data try { strAddr1 = wSheet.Cells[iNextRow, 0].Name; strAddr2 = wSheet.Cells[dtExport.Rows.Count, iColLength - 1].Name; range = wSheet.Cells.CreateRange(strAddr1 + ":" + strAddr2); style.IsTextWrapped = false; style.Number = 49; range.ApplyStyle(style, styleFlag); DataView dvExport = dtExport.DefaultView; DataTable dtTemp = dvExport.ToTable(false, lstColumnsID); wSheet.Cells.ImportData(dtTemp, iNextRow, 0, importOptions); //Creating a range of cells starting from "A1" cell to 3rd column in a row //range = wSheet.Cells.CreateRange(strAddr1 + ":" + strAddr2); //style = workbook.Styles[workbook.Styles.Add()]; //styleFlag = new StyleFlag(); //styleFlag.Borders = true; //style.Number = 49; //style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //style.Borders[BorderType.TopBorder].Color = Color.Black; //style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //style.Borders[BorderType.LeftBorder].Color = Color.Black; //style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //style.Borders[BorderType.BottomBorder].Color = Color.Black; //style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //style.Borders[BorderType.RightBorder].Color = Color.Black; //range.ApplyStyle(style, styleFlag); //Format Bold if (dtExport.Columns.Contains("BOLD")) { DataRow[] arrFotmat = dtExport.Select("BOLD=TRUE"); if (arrFotmat.Length != 0) { style = workbook.Styles[workbook.Styles.Add()]; style.Font.IsBold = true; styleFlag = new StyleFlag(); styleFlag.FontBold = true; } } } catch (Exception ex) { throw new Exception("Có lỗi xảy ra: " + ex.Message); } #endregion wSheet.AutoFitRows(); wSheet.AutoFitColumns(); isheet++; } //Ghi file lên đĩa if (bOverwrite) { try { if (System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(strFileName))) { //workbook.Settings.Password = "******"; workbook.Save(strFileName); } } catch (Exception ex) { throw new Exception("Không thể kết xuất được dữ liệu!" + ex.ToString()); } } }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) { System.IO.Directory.CreateDirectory(dataDir); } // Instantiating a "Products" DataTable object DataTable dataTable = new DataTable("Products"); // Adding columns to the DataTable object dataTable.Columns.Add("Product ID", typeof(Int32)); dataTable.Columns.Add("Product Name", typeof(string)); dataTable.Columns.Add("Units In Stock", typeof(Int32)); // Creating an empty row in the DataTable object DataRow dr = dataTable.NewRow(); // Adding data to the row dr[0] = 1; dr[1] = "Aniseed Syrup"; dr[2] = 15; // Adding filled row to the DataTable object dataTable.Rows.Add(dr); // Creating another empty row in the DataTable object dr = dataTable.NewRow(); // Adding data to the row dr[0] = 2; dr[1] = "Boston Crab Meat"; dr[2] = 123; // Adding filled row to the DataTable object dataTable.Rows.Add(dr); // Instantiate a new Workbook Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; // Create import options ImportTableOptions importOptions = new ImportTableOptions(); importOptions.IsFieldNameShown = true; importOptions.IsHtmlString = true; // Importing the values of 2nd column of the data table sheet.Cells.ImportData(dataTable, 1, 1, importOptions); // Save workbook book.Save(dataDir + "DataImport.out.xls"); // ExEnd:1 }