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
}
