public static void Main()
{
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string sourceDir = RunExamples.Get_SourceDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook(sourceDir + "sampleNamesTable.xlsx");
// Instantiating a WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner(workbook);
// Accessing the range having name "Names"
var range = designer.Workbook.Worksheets.GetRangeByName("Names");
// Instantiating the ExportTableOptions object
ExportTableOptions options = new ExportTableOptions();
// Setting the ExportColumnName flag to true shows that first line is header and not part of data
options.ExportColumnName = true;
// Exporting data with the selected information
var dataTable = range.ExportDataTable(options);
Console.WriteLine("ExportRangeWithFlagToSkipColumnName executed successfully.\r\n");
}
public static DataTable GetSpecifiedTable(string wbPath)
{
Workbook workbook = new Workbook(wbPath);
Cells cells = workbook.Worksheets[0].Cells;
ExportTableOptions exportTableOptions = new ExportTableOptions();
exportTableOptions.ExportAsString = true;
exportTableOptions.ExportColumnName = true;
DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, exportTableOptions);
return(dt);
}
public static void Run()
{
// ExStart:ExportExcelDataToDataTableWithoutFormatting
// Create workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access cell A1
Cell cell = worksheet.Cells["A1"];
// Put value inside the cell
cell.PutValue(0.012345);
// Format the cell that it should display 0.01 instead of 0.012345
Style style = cell.GetStyle();
style.Number = 2;
cell.SetStyle(style);
// Display the cell values as it displays in excel
Console.WriteLine("Cell String Value: " + cell.StringValue);
// Display the cell value without any format
Console.WriteLine("Cell String Value without Format: " + cell.StringValueWithoutFormat);
// Export Data Table Options with FormatStrategy as CellStyle
ExportTableOptions opts = new ExportTableOptions();
opts.ExportAsString = true;
opts.FormatStrategy = CellValueFormatStrategy.CellStyle;
// Export Data Table
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, 1, 1, opts);
// Display the value of very first cell
Console.WriteLine("Export Data Table with Format Strategy as Cell Style: " + dt.Rows[0][0].ToString());
// Export Data Table Options with FormatStrategy as None
opts.FormatStrategy = CellValueFormatStrategy.None;
dt = worksheet.Cells.ExportDataTable(0, 0, 1, 1, opts);
// Display the value of very first cell
Console.WriteLine("Export Data Table with Format Strategy as None: " + dt.Rows[0][0].ToString());
// ExEnd:ExportExcelDataToDataTableWithoutFormatting
}
static void Main()
{
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string inputPath = dataDir + "Sample.xlsx";
Workbook workbook = new Workbook(inputPath);
Worksheet worksheet = workbook.Worksheets[0];
ExportTableOptions opts = new ExportTableOptions();
opts.PlotVisibleColumns = true;
int totalRows = worksheet.Cells.MaxRow + 1;
int totalColumns = worksheet.Cells.MaxColumn + 1;
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, totalRows, totalColumns, opts);
}
public static void Run()
{
// ExStart:ExportExcelDataToDataTableWithoutFormatting
// Create workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access cell A1
Cell cell = worksheet.Cells["A1"];
// Put value inside the cell
cell.PutValue(0.012345);
// Format the cell that it should display 0.01 instead of 0.012345
Style style = cell.GetStyle();
style.Number = 2;
cell.SetStyle(style);
// Display the cell values as it displays in excel
Console.WriteLine("Cell String Value: " + cell.StringValue);
// Display the cell value without any format
Console.WriteLine("Cell String Value without Format: " + cell.StringValueWithoutFormat);
// Export Data Table Options with FormatStrategy as CellStyle
ExportTableOptions opts = new ExportTableOptions();
opts.ExportAsString = true;
opts.FormatStrategy = CellValueFormatStrategy.CellStyle;
// Export Data Table
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, 1, 1, opts);
// Display the value of very first cell
Console.WriteLine("Export Data Table with Format Strategy as Cell Style: " + dt.Rows[0][0].ToString());
// Export Data Table Options with FormatStrategy as None
opts.FormatStrategy = CellValueFormatStrategy.None;
dt = worksheet.Cells.ExportDataTable(0, 0, 1, 1, opts);
// Display the value of very first cell
Console.WriteLine("Export Data Table with Format Strategy as None: " + dt.Rows[0][0].ToString());
// ExEnd:ExportExcelDataToDataTableWithoutFormatting
}
public static void Run()
{
string sourceDir = RunExamples.Get_SourceDirectory();
Workbook workbook = new Workbook(sourceDir + "sampleIgnoreHiddenColumnsDataTable.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
ExportTableOptions opts = new ExportTableOptions();
opts.ExportColumnName = true;
opts.PlotVisibleColumns = true;
int totalRows = worksheet.Cells.MaxRow + 1;
int totalColumns = worksheet.Cells.MaxColumn + 1;
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, totalRows, totalColumns, opts);
Console.WriteLine("IgnoreHiddenColumnsDataTable executed successfully.");
}
static void Main()
{
//ExStart:1
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string inputPath = dataDir + "Sample.xlsx";
Workbook workbook = new Workbook(inputPath);
Worksheet worksheet = workbook.Worksheets[0];
ExportTableOptions opts = new ExportTableOptions();
opts.PlotVisibleColumns = true;
int totalRows = worksheet.Cells.MaxRow + 1;
int totalColumns = worksheet.Cells.MaxColumn + 1;
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, totalRows, totalColumns, opts);
//ExEnd:1
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir+ "aspose-sample.xlsx";
//Load the source workbook
Workbook workbook = new Workbook(filePath);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Specify export table options
ExportTableOptions exportOptions = new ExportTableOptions();
exportOptions.PlotVisibleRows = true;
exportOptions.ExportColumnName = true;
//Export the data from worksheet with export options
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 10, 4, exportOptions);
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
string filePath = dataDir+ "aspose-sample.xlsx";
//Load the source workbook
Workbook workbook = new Workbook(filePath);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Specify export table options
ExportTableOptions exportOptions = new ExportTableOptions();
exportOptions.PlotVisibleRows = true;
exportOptions.ExportColumnName = true;
//Export the data from worksheet with export options
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 10, 4, exportOptions);
}
void workCheck_DoWork(object sender, DoWorkEventArgs e)
{
try
{
ExportTableOptions opts = new ExportTableOptions();
opts.ExportAsString = true;
workCheck.ReportProgress(0, "开始处理...");
string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true);
Workbook template = new Workbook();
Cells TemplateCells = template.Worksheets[0].Cells;
foreach (var item in listAllExcel)
{
Workbook workbook = new Workbook(item);
foreach (var sheet in workbook.Worksheets)
{
workCheck.ReportProgress(0, "开始处理:" + item + "/" + sheet.Name);
if (!IsExcuteSheet(sheet.Name))
{
workCheck.ReportProgress(0, "此sheet名称不在待处理表中，将忽略...---" + sheet.Name);
continue;
}
Cells cells = sheet.Cells;
DataTable table = cells.ExportDataTable(StartRow - 1, 0, cells.MaxDataRow - StartRow - EndRow + 3, cells.MaxDataColumn + 1, opts);
// DataTable table = cells.ExportDataTable(StartRow - 1, 0, 5, cells.MaxDataColumn + 1, opts);
//TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "yyyy/MM/dd");
TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "mm/dd/yyyy", false);
}
}
TemplateCells.DeleteBlankRows();
DataTable tableSource = TemplateCells.ExportDataTable(0, 0, TemplateCells.MaxDataRow + 1, TemplateCells.MaxDataColumn + 1, opts);
tableSource = ReturnMergeData(tableSource);
TemplateCells.Clear();
TemplateCells.ImportDataTable(tableSource, false, 0, 0, tableSource.Rows.Count, tableSource.Columns.Count, true, "mm/dd/yyyy", false);
template.Save(txtResult.Text);
workCheck.ReportProgress(0, "处理完成");
}
catch (Exception ex)
{
workCheck.ReportProgress(0, ex.Message);
}
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
string filePath = dataDir + "aspose-sample.xlsx";
//Load the source workbook
Workbook workbook = new Workbook(filePath);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Specify export table options
ExportTableOptions exportOptions = new ExportTableOptions();
exportOptions.PlotVisibleRows = true;
exportOptions.ExportColumnName = true;
//Export the data from worksheet with export options
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 10, 4, exportOptions);
}
public static void Run()
{
// ExStart:RenameDuplicateColumnsAutomaticallyWhileExportingWorksheetData
//Create a workbook.
Workbook wb = new Workbook();
//Access first worksheet.
Worksheet ws = wb.Worksheets[0];
//Write the same column name in columns A, B and C.
string columnName = "People";
ws.Cells["A1"].PutValue(columnName);
ws.Cells["B1"].PutValue(columnName);
ws.Cells["C1"].PutValue(columnName);
//Insert data in column A, B and C.
ws.Cells["A2"].PutValue("Data");
ws.Cells["B2"].PutValue("Data");
ws.Cells["C2"].PutValue("Data");
//Create ExportTableOptions and specify that you want to rename
//duplicate column names automatically via RenameStrategy property.
ExportTableOptions opts = new ExportTableOptions();
opts.ExportColumnName = true;
opts.RenameStrategy = RenameStrategy.Letter;
//Export data to data table, duplicate column names will be renamed automatically.
System.Data.DataTable dataTable = ws.Cells.ExportDataTable(0, 0, 4, 3, opts);
//Now print the column names of the data table generated by Aspose.Cells while exporting worksheet data.
for (int i = 0; i < dataTable.Columns.Count; i++)
{
Console.WriteLine(dataTable.Columns[i].ColumnName);
}
// ExEnd:RenameDuplicateColumnsAutomaticallyWhileExportingWorksheetData
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
// Load the source workbook
Workbook workbook = new Workbook(sourceDir + "sampleExportVisibleRowsData.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Specify export table options
ExportTableOptions exportOptions = new ExportTableOptions();
exportOptions.PlotVisibleRows = true;
exportOptions.ExportColumnName = true;
// Export the data from worksheet with export options
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 10, 4, exportOptions);
Console.WriteLine("ExportVisibleRowsData executed successfully.\r\n");
}
/// <summary>
/// 将 WorkSheet 转换为 DataTable
/// </summary>
public static DataTable WorkSheetToDataTable(Worksheet worksheet, ExcelToTableOptions excelToTableOptions)
{
var dt = new DataTable();
if (null == worksheet)
{
return(dt);
}
if (null == excelToTableOptions)
{
excelToTableOptions = new ExcelToTableOptions();
}
var options = new ExportTableOptions();
options.ExportColumnName = excelToTableOptions.IsConvertFirstRowToColumnName;
options.ExportAsString = true;
options.PlotVisibleRows = excelToTableOptions.IsOnlyVisibleRows;
var displayRange = worksheet.Cells.MaxDisplayRange;
var readTotalCount = displayRange.RowCount - excelToTableOptions.StartRowIndex;
if (readTotalCount <= 0)
{
return(dt);
}
dt = worksheet.Cells.ExportDataTable(excelToTableOptions.StartRowIndex, 0, readTotalCount, displayRange.ColumnCount, options);
dt.TableName = worksheet.Name;
if (excelToTableOptions.IgnoreEmptyDataRows)
{
for (var i = dt.Rows.Count - 1; i >= 0; i--)
{
if (dt.Rows[i].ItemArray.All(k => DBNull.Value == k || string.IsNullOrWhiteSpace(Convert.ToString(k))))
{
dt.Rows.RemoveAt(i);
}
}
}
return(dt);
}
public static void Main()
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "aspose-sample.xlsx";
//Load the source workbook
Workbook workbook = new Workbook(filePath);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Specify export table options
ExportTableOptions exportOptions = new ExportTableOptions();
exportOptions.PlotVisibleRows = true;
exportOptions.ExportColumnName = true;
//Export the data from worksheet with export options
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 10, 4, exportOptions);
//ExEnd:1
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Load sample Excel file
Workbook wb = new Workbook(sourceDir + "sampleExportTableAsHtmlString.xlsx");
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Specify export table options and set ExportAsHtmlString to true
ExportTableOptions opts = new ExportTableOptions();
opts.ExportColumnName = false;
opts.ExportAsHtmlString = true;
//Export the cells data to data table with the specified export table options
DataTable dt = ws.Cells.ExportDataTable(0, 0, 3, 3, opts);
//Print the cell html string value that is in third row and second column
Console.WriteLine(dt.Rows[2][1].ToString());
}
void workCheck_DoWork(object sender, DoWorkEventArgs e)
{
try
{
workCheck.ReportProgress(0, "开始处理...");
string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true);
Workbook template = new Workbook();
Cells TemplateCells = template.Worksheets[0].Cells;
int k = 0;
// DataTable TemplateTable = TemplateCells.ExportDataTable(StartRow - 1, 0, TemplateCells.MaxDataRow - EndRow + 1, TemplateCells.MaxDataColumn + 1);
foreach (var item in listAllExcel)
{
Workbook workbook = new Workbook(item);
ExportTableOptions opts = new ExportTableOptions();
opts.ExportAsString = true;
foreach (var sheet in workbook.Worksheets)
{
if (!IsExcuteSheet(sheet.Name))
{
workCheck.ReportProgress(0, "此sheet名称不在待处理表中，将忽略...---" + sheet.Name);
continue;
}
workCheck.ReportProgress(0, "开始处理:" + item + "/" + sheet.Name);
Cells cells = sheet.Cells;
DataTable table;
k++;
if (k == listAllExcel.Length)
{
//最后一个表保留表头
table = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1 - EndRow + 1, cells.MaxDataColumn + 1, opts);
}
else
{
table = cells.ExportDataTable(StartRow - 1, 0, cells.MaxDataRow - EndRow + 1, cells.MaxDataColumn + 1, opts);
}
for (int i = 0; i < table.Rows.Count; i++)
{
//去除无效数据行
if (table.Rows[i][pdKeyColumn - 1] == null || string.IsNullOrEmpty(table.Rows[i][pdKeyColumn - 1].ToString()))
{
table.Rows.RemoveAt(i);
//调整索引减1
i--;
continue;
}
}
//TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "yyyy/MM/dd");
TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "yyyy/MM/dd");
}
}
template.Save(txtResult.Text);
workCheck.ReportProgress(0, "处理完成");
}
catch (Exception ex)
{
workCheck.ReportProgress(0, ex.Message);
}
}
