首页 新闻 会员 周边 捐助

C# 导出.xls文件的Excel

0
悬赏园豆:5 [待解决问题]

c#导出 .xls 里面两个sheet 都填数据 这个怎么写 有大神么 求指点

问题补充:

哪个库都行 求代码

敲代码带的主页 敲代码带 | 初学一级 | 园豆:0
提问于:2019-03-11 21:13
< >
分享
所有回答(6)
0

你用的什么类库,没有提供这样的功能吗,换个类库试试

会长 | 园豆:12463 (专家六级) | 2019-03-12 08:36
0

NPOI 第三方Excel得写库

jqw2009 | 园豆:2341 (老鸟四级) | 2019-03-12 08:48
0

NPOI操作excel导出导入比较好

风浪 | 园豆:2996 (老鸟四级) | 2019-03-12 10:24
0

百度一下 你就知道。

从今天开始 | 园豆:244 (菜鸟二级) | 2019-03-12 11:08
0
        public byte[] DataSetToExcel2007(DataSet ds, List<string> sheetName)
        {

            XSSFWorkbook workbook = new XSSFWorkbook();
            bool isAutoName = false;
            if (sheetName == null || sheetName.Count == 0)
                isAutoName = true;
            if (ds.Tables.Count > sheetName.Count)
            {
                isAutoName = true;
            }
            if (string.IsNullOrEmpty(strExcelFileName))
                strExcelFileName = "report";

            for(int i=0;i<ds.Tables.Count;i++)
            {
                DataTable dt = ds.Tables[i];
                string mySheetName = isAutoName ? "Sheet" + i : sheetName[i];
                ISheet sheet = workbook.CreateSheet(mySheetName);
                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                HeadercellStyle.VerticalAlignment = VerticalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);

                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 20;
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();
                //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看
                //cellStyle.DataFormat = XSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;

                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    DataRow.HeightInPoints = 18;
                    foreach (DataColumn Colitem in dt.Columns)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int j = 0; j < icolIndex; j++)
                {
                    sheet.AutoSizeColumn(j);
                }
            }

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            workbook.Write(ms);//xssfworkbook在write后会关闭流
            byte[] bytes = ms.ToArray();
            return bytes;
        }

能写代码就不bb,你要的时这个?需要引用NPOI

龙葛格 | 园豆:782 (小虾三级) | 2019-03-12 11:28
0

用Free Spire.XLS (http://www.e-iceblue.cn/Downloads/Free-Spire-XLS-NET.html )代码简单,可以将数据导入到指定的sheet

using Spire.Xls;
using System.Data.OleDb;
using System.Data;

namespace test
{
        class Program
        {
            static void Main(string[] args)
            {

               //链接数据库
                   OleDbConnection connection = new OleDbConnection();      
               connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
                       OleDbCommand command = new OleDbCommand();
               command.CommandText = "select * from parts";
               DataSet dataSet = new System.Data.DataSet();
               OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
               dataAdapter.Fill(dataSet); 
        
               //加载excel文档到
               Workbook book = new Workbook();
                book.LoadFromFile(@"C:\Users\Administrator\Desktop\ToExcel.xlsx");

               //获取第一个和第二个sheet
                Worksheet sheet1 = book.Worksheets[0];
                Worksheet sheet2 = book.Worksheets[1];

               //将不同的table分别导入两个sheet
                DataTable table1 = dataSet.Tables[0];
               sheet1.InsertDataTable(table1, true, 1, 1);
               DataTable table2 = dataSet.Tables[1];
              sheet2.InsertDataTable(table2, true, 1, 1);

               //保存excel文档
               book.Save();
          }
    }
}
KeepLearning_88 | 园豆:231 (菜鸟二级) | 2019-03-12 14:26
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册