Aspose.cells常用的导入导出等操作

导入操作

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


        }