C# excel操作

使用库

Spire.Xls

下载

示例数据

代码示例

1.删除列

代码

        private static void DeleteExcelColumns1(string excelPath) 
        {
            if (excelPath.Length == 0) 
            {
                Console.WriteLine("excel文件路径为空");
            }
            else
            {
                Console.WriteLine("删除列方法1：保留第一列，删除其他列");
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(excelPath);
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                int ColumnsLength = sheet.Columns.Count();
                //删除除了第一行以外的其他列，因为删除的操作是在上一步的结果上进行的操作
                for (int i = 2; i <= ColumnsLength; i++)
                {
                    sheet.DeleteColumn(2);
                }
                //设置数据自适应列宽
                sheet.AllocatedRange.AutoFitColumns();
                //修改后的保存为csv文件
                sheet.SaveToFile(Path.GetDirectoryName(excelPath)+"/删除列方法1"+Path.GetFileNameWithoutExtension(excelPath)+".csv", ",", Encoding.UTF8);
            }           
        }
        private static void DeleteExcelColumns2(string excelPath)
        {
            if (excelPath.Length == 0)
            {
                Console.WriteLine("excel文件路径为空");
            }
            else
            {
                Console.WriteLine("删除列方法2：保留第一列，删除其他列");
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(excelPath);
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                sheet.DeleteColumn(2,9);
                //设置数据自适应列宽
                sheet.AllocatedRange.AutoFitColumns();
                //修改后的保存为csv文件
                sheet.SaveToFile(Path.GetDirectoryName(excelPath) + "/删除列方法2" + Path.GetFileNameWithoutExtension(excelPath) + ".csv", ",", Encoding.UTF8);
            }
        }

结果

2.删除行

代码

        private static void DeleteExcelRow1(string excelPath)
        {
            if (excelPath.Length == 0)
            {
                Console.WriteLine("excel文件路径为空");
            }
            else
            {
                Console.WriteLine("删除行方法1：保留第一行，删除其他行");
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(excelPath);
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                int ColumnsLength = sheet.Rows.Count();
                //删除除了第一行以外的其他列，因为删除的操作是在上一步的结果上进行的操作
                for (int i = 2; i <= ColumnsLength; i++)
                {
                    sheet.DeleteRow(2);
                }
                //设置数据自适应列宽
                sheet.AllocatedRange.AutoFitColumns();
                //修改后的保存为csv文件
                sheet.SaveToFile(Path.GetDirectoryName(excelPath) + "/删除行方法1" + Path.GetFileNameWithoutExtension(excelPath) + ".csv", ",", Encoding.UTF8);
            }
        }
        private static void DeleteExcelRow2(string excelPath)
        {
            if (excelPath.Length == 0)
            {
                Console.WriteLine("excel文件路径为空");
            }
            else
            {
                Console.WriteLine("删除行方法2：保留第一行，删除其他行");
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(excelPath);
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                sheet.DeleteRow(2, 9);
                //设置数据自适应列宽
                sheet.AllocatedRange.AutoFitColumns();
                //修改后的保存为csv文件
                sheet.SaveToFile(Path.GetDirectoryName(excelPath) + "/删除行方法2" + Path.GetFileNameWithoutExtension(excelPath) + ".csv", ",", Encoding.UTF8);
            }
        }
        private static void DeleteExcelRow3(string excelPath)
        {
            if (excelPath.Length == 0)
            {
                Console.WriteLine("excel文件路径为空");
            }
            else
            {
                Console.WriteLine("删除行方法3：保留第一行，删除其他行");
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(excelPath);
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                int[] delrows = { 2, 3 ,4,5,6,7,8,9,10};
                sheet.DeleteRows(delrows);
                //设置数据自适应列宽
                sheet.AllocatedRange.AutoFitColumns();
                //修改后的保存为csv文件
                sheet.SaveToFile(Path.GetDirectoryName(excelPath) + "/删除行方法3" + Path.GetFileNameWithoutExtension(excelPath) + ".csv", ",", Encoding.UTF8);
            }
        }

结果

修改表

代码

        private static void ChangeExcelHeader1(string excelPath)
        {
            if (excelPath.Length == 0)
            {
                Console.WriteLine("excel文件路径为空");
            }
            else
            {
                Console.WriteLine("修改表头");
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(excelPath);
                //获取第一张工作表
                Worksheet sheet = workbook.Worksheets[0];
                string value0 = sheet.Cells[0].Value;
                Console.WriteLine(value0);
                string value11 = sheet.Cells[11].Value;
                Console.WriteLine(value11);
                sheet.Cells[1].Value = "12";
                string valuesA1 = sheet.Range["A1"].Value;
                Console.WriteLine(valuesA1);
                sheet.Range["C1"].Value = "13";
                //设置数据自适应列宽
                sheet.AllocatedRange.AutoFitColumns();
                //修改后的保存为csv文件
                sheet.SaveToFile(Path.GetDirectoryName(excelPath) + "/修改表头" + Path.GetFileNameWithoutExtension(excelPath) + ".csv", ",", Encoding.UTF8);
            }
        }

结果

参考文献

C#/VB.NET 删除 Excel 中的行和列