导入操作
public static int DataTableToExcel(string TargetFileNamePath, System.Data.DataTable sourceData, string TemplateSheetName, string sheetName, bool IsWriteColumnName, bool Overwrite, string startCell, Dictionary<string, object> ConvertColumnTypes = null)
{
if (!File.Exists(TargetFileNamePath))
{
var stream = File.Create(TargetFileNamePath);
stream.Close();
}
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(TargetFileNamePath);
// 创建工作表
Aspose.Cells.Worksheet sheet1 = book.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = book.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
else
{
sheet1 = book.Worksheets.Add(sheetName);
}
}
// 单元格
Cells cells = sheet1.Cells;
int startColumn = 0;
int startRow = 0;
if (!string.IsNullOrEmpty(startCell))
{
var cell = cells[startCell];
startColumn = cell.Column;
startRow = cell.Row;
}
if (IsWriteColumnName == true)
{
// 生成行 列名行
for (int i = 0; i < sourceData.Columns.Count; i++)
{
cells[startRow, i + startColumn].PutValue(sourceData.Columns[i].ColumnName);
}
for (int i = 0; i < sourceData.Rows.Count; i++)
{
for (int k = 0; k < sourceData.Columns.Count; k++)
{
cells[1 + i + startRow, k + startColumn].PutValue(sourceData.Rows[i][k].ToString()); //添加数据
}
}
}
else
{
// 生成数据行
for (int i = 0; i < sourceData.Rows.Count; i++)
{
for (int k = 0; k < sourceData.Columns.Count; k++)
{
cells[i + startRow, k + startColumn].PutValue(sourceData.Rows[i][k].ToString()); //添加数据
}
}
}
// 自适应宽
sheet1.AutoFitColumns();
// 保存
book.Save(TargetFileNamePath);
return sourceData.Rows.Count;
}
导出操作
/// <summary>
/// Excel读取Datable
/// </summary>
/// <param name="file">导入路径</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file, bool isFirstRowColumn, string sheetName = "")
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.ReadWrite))
{
DataTable dt = new DataTable();
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(fs);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
if (isFirstRowColumn)
{
dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, new ExportTableOptions { ExportColumnName = true, SkipErrorValue = true });
}
else
{
dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, new ExportTableOptions { ExportColumnName = false, SkipErrorValue = true });
}
}
return dt;
}
}
public static DataTable ExcelToTable(string file, bool isFirstRowColumn, string range, bool includeDown, string sheetName = "")
{
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.ReadWrite))
{
DataTable dt = new DataTable();
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(fs);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
Range rangeCell = cells.CreateRange(range);
int totalRows = rangeCell.RowCount;
if (includeDown)
{
totalRows = cells.MaxDataRow - rangeCell.FirstRow + 1;
}
if (isFirstRowColumn)
{
dt = cells.ExportDataTable(rangeCell.FirstRow, rangeCell.FirstColumn, totalRows, rangeCell.ColumnCount, new ExportTableOptions { ExportColumnName = true , SkipErrorValue = true });
}
else
{
dt = cells.ExportDataTable(rangeCell.FirstRow, rangeCell.FirstColumn, totalRows, rangeCell.ColumnCount, new ExportTableOptions { ExportColumnName = false, SkipErrorValue = true });
}
}
return dt;
}
}
删除指定列行
/// <summary>
/// 删除指定列
/// </summary>
/// <param name="file"></param>
/// <param name="columnIndex"></param>
/// <param name="totalColumns"></param>
/// <param name="sheetName"></param>
public static void DeleteColumn(string file, int columnIndex, int totalColumns, string sheetName = "")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(file);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
cells.DeleteColumns(columnIndex, totalColumns, false);
}
wb.Save(file);
}
public static void DeleteRow(string file, int rowIndex, int totalRows, string sheetName = "")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(file);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
cells.DeleteRows(rowIndex, totalRows, false);
}
wb.Save(file);
}
改变单元格大小 ,样式
public static void SetRowStyle(string file, int[] rows, int startColumnIndex, int? endColumnIndex, Color backgroundColor, Color forgroundColor, string sheetName = "")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(file);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
if (backgroundColor.Name != "0")
{
style.ForegroundColor = backgroundColor;
}
if (forgroundColor.Name != "0")
{
style.Font.Color = forgroundColor;
}
style.Pattern = BackgroundType.Solid;
foreach (int rowIndex in rows)
{
Row row = cells.GetRow(rowIndex - 1);
Range range = cells.CreateRange(rowIndex - 1, startColumnIndex - 1, 1, row.LastCell.Column - startColumnIndex + 2);//第一行第一列单元格
range.ApplyStyle(style, new StyleFlag() { All = true });
}
}
wb.Save(file);
}
public static void SetCellSize(string file, string range, int? width, int? height, string sheetName = "")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(file);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
Range rangeCell = cells.CreateRange(range);//第一行第一列单元格
if (height != null)
{
for (int r = rangeCell.FirstRow; r < rangeCell.FirstRow + rangeCell.RowCount; r++)
{
cells.SetRowHeight(r, Convert.ToDouble(height));
}
}
if (width != null)
{
for (int c = rangeCell.FirstColumn; c < rangeCell.FirstColumn + rangeCell.ColumnCount; c++)
{
cells.SetColumnWidth(c, Convert.ToDouble(width));
}
}
}
wb.Save(file);
}
单元格替换,赋值
public static void ReplaceCellValue(string file, object oldvalue, object newvalue, string sheetName = "")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(file);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Aspose.Cells.Cells cells = sheet1.Cells;
foreach (Aspose.Cells.Cell c in cells)
{
if (c.Value != null)
{
if (c.Value.GetType().Equals(oldvalue.GetType()))
{
if (c.Value.Equals(oldvalue))
{
c.Value = newvalue;
}
}
else
{
try
{
var value = Convert.ChangeType(oldvalue, c.Value.GetType());
if (c.Value.Equals(value))
{
c.Value = Convert.ChangeType(newvalue, c.Value.GetType());
}
}
catch (Exception e)
{
}
}
}
}
}
wb.Save(file);
}
public static void SetCellValue(string file, string range, object value, string sheetName = "")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(file);
//wb.Save(file);
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[0];
if (!string.IsNullOrEmpty(sheetName))
{
var sheet2 = wb.Worksheets[sheetName];
if (sheet2 != null)
{
sheet1 = sheet2;
}
}
if (sheet1 != null)
{
Cells cells = sheet1.Cells;
cells[range].PutValue(value);
}
wb.Save(file);
}
插入图表
private static ChartType getChartType(ExcelChartType chartType)
{
if(chartType== ExcelChartType.柱状图)
{
return ChartType.Column;
}
else if(chartType == ExcelChartType.线状图)
{
return ChartType.Line;
}
else
{
return ChartType.Pie;
}
}
/// <summary>
/// 创建excel图表
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <param name="chartType"></param>
/// <param name="beginRow"></param>
/// <param name="beginColumn"></param>
/// <param name="endRow"></param>
/// <param name="endColumn"></param>
/// <param name="chartTitle"></param>
/// <param name="seriesData"></param>
/// <param name="seriesName"></param>
/// <param name="seriesCategory"></param>
public static void CreateChart(string filePath,string sheetName,ExcelChartType chartType,int beginRow,int beginColumn,int endRow,int endColumn,string chartTitle,string[] seriesData, string[] seriesName,string seriesCategory)
{
Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook(filePath);
//获取sheet
Aspose.Cells.Worksheet sheet = workBook.Worksheets[sheetName];
//图形位置 ChartType图形选择,起始行,起始列
sheet.Charts.Add(getChartType(chartType), beginRow, beginColumn, endRow, endColumn);
//创建图形
Aspose.Cells.Charts.Chart chart = sheet.Charts[0];
//标题
chart.Title.Text = chartTitle;
chart.Title.Font.IsBold = true;
chart.Title.Font.Size = 12;//字体大小
chart.Name = chartTitle;
chart.CategoryAxis.MajorGridLines.IsVisible = false;//设置竖网格线是否可见
foreach(var series in seriesData)
{
chart.NSeries.Add($"{series}", true);
}
for(int i=0;i< seriesName.Length; i++)
{
chart.NSeries[i].Name = $"{seriesName[i]}";
}
//绑定类别
chart.NSeries.CategoryData = $"{seriesCategory}";
//保存文件
workBook.Save(filePath);
}
创建sheet工作簿
public static void CreatSheet(string FilePath, string sheetName, int shitindex, bool isExistsCreate)
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(FilePath);
// Create a WorksheetCollection object with reference to the sheets of the Workbook
WorksheetCollection sheets = wb.Worksheets;
if (sheets[sheetName] != null)
{
throw new Exception("已存在名为:" + sheetName + "的表!");
}
shitindex = shitindex - 1;
if (shitindex < 0)
{
shitindex = 0;
}
// Copy data to a new sheet from an existing sheet within the Workbook
sheets.Insert(shitindex, SheetType.Worksheet, sheetName);
// Save the Excel file
wb.Save(FilePath);
}