c# 在 excel 文件与 datatable 间导入与导出数据-ag凯发旗舰厅

pm> install-package spire.xls

using spire.xls;
using system.data;
namespace exceltodatatable
{
    class program
    {
        static void main(string[] args)
        {
            // 创建一个 workbook 实例
            workbook wb = new workbook();
            // 加载 excel 文件
            wb.loadfromfile("示例.xlsx");
            // 获取一个工作表
            worksheet sheet = wb.worksheets[0];
            // 导出工作表数据到 datatable
            //datatable datatable = sheet.exportdatatable();
            // 或者导出指定单元格范围到 datatable
            exporttableoptions options = new exporttableoptions();
            options.computedformulavalue = true;
            options.exportcolumnnames = false;
            options.keepdataformat = true;
            options.renamestrategy = renamestrategy.letter;
            datatable datatable = sheet.range[2, 1, 4, 6].exportdatatable(options);
            // 输出 datatable 的列名
            for (int i = 0; i < datatable.columns.count; i  )
            {
                console.write(datatable.columns[i].columnname   "\t");
            }
            console.writeline();
            // 输出 datatable 的数据行
            foreach (datarow row in datatable.rows)
            {
                foreach (var item in row.itemarray)
                {
                    console.write(item   "\t");
                }
                console.writeline();
            }
        }
    }
}

using spire.xls;
using system.data;
namespace datatabletoexcel
{
    class program
    {
        static void main(string[] args)
        {
            // 定义数据集并创建 datatable 实例
            // 用新的产品和数量重定义数据数组
            string[,] data = new string[,]
            {
                { "类别", "书名", "作者", "出版社", "价格（元）" },
                { "小说", "平凡的世界", "路遥", "人民文学出版社", "45" },
                { "科技", "深入浅出计算机组成原理", "杨帆", "机械工业出版社", "79" },
                { "历史", "明朝那些事儿", "当年明月", "中国友谊出版公司", "39.8" },
                { "哲学", "存在与时间", "马丁•海德格尔", "生活•读书•新知三联书店", "58" },
            };
            datatable datatable = new datatable();
            // 获取列数
            int columncount = data.getlength(1);
            // 向 datatable 添加列
            for (int col = 0; col < columncount; col  )
            {
                datatable.columns.add(data[0, col]);
            }
            // 向 datatable 添加行
            for (int row = 1; row < data.getlength(0); row  )
            {
                datarow datarow = datatable.newrow();
                for (int col = 0; col < columncount; col  )
                {
                    datarow[col] = data[row, col];
                }
                datatable.rows.add(datarow);
            }
            // 创建 workbook 实例
            workbook workbook = new workbook();
            // 清除默认工作表并添加新工作表
            workbook.worksheets.clear();
            worksheet sheet = workbook.worksheets.add("salesreport");
            // 将 datatable 数据导入工作表
            sheet.insertdatatable(datatable, true, 1, 1);
            // 根据需要调整格式
            // 调整字体
            sheet.rows[0].style.font.size = 13;
            sheet.rows[0].style.font.isbold = true;
            sheet.rows[0].style.font.fontname = "微软雅黑";
            for (int j = 1; j < sheet.allocatedrange.rowcount; j  )
            {
                sheet.rows[j].style.font.size = 12;
                sheet.rows[j].style.font.fontname = "微软雅黑";
            }
            // 自动调整列宽
            for (int i = 1; i <= sheet.allocatedrange.columncount; i  )
            {
                sheet.autofitcolumn(i);
            }
            // 将工作簿保存到文件
            workbook.savetofile("output/datatable导入excel.xlsx", fileformat.version2016);
            workbook.dispose();
        }
    }
}