c#使用开源控件读卡excel

最新推荐文章于 2025-03-31 12:43:04 发布

小二码农

最新推荐文章于 2025-03-31 12:43:04 发布

阅读量1.8k

点赞数

CC 4.0 BY-SA版权

分类专栏： C#、Vs2005 文章标签： c# string excel null file dataset

本文链接：https://blog.youkuaiyun.com/liangzhonglin/article/details/6747844

C#、Vs2005 专栏收录该内容

41 篇文章

订阅专栏

本文详细介绍了如何使用C#和NPOI组件进行Excel文件的读写操作，包括解决读取Excel 2007格式的问题，以及提供了一个将数据表转换为Excel文件的方法。此外，还提供了从Excel文件读取数据到数据表的功能，并通过实例展示了如何实现这一过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

因工作关系，使用c#读写excel文件，试过myxls, 与NPOI这两个组件，读写excel文件都没问题，不过都读不了excel2007格式的，目前还未找到开源能读excel2007的。

如果只是读或者写，这两个组件都可以。但是如果是读写一起进行，那myxls组件在保存时就会有问题，需要先读出原来的excel文件，再删除，然后再保存到这个文件里。这样不合理。npoi没这个问题，先这个文件里读出来之后，可以再保存回这个文件，在此说明。

使用npoi读写excel

using System;
using System.Collections.Generic;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.IO;
using System.Data;
using NPOI;
using NPOI.SS.UserModel;

namespace SyncTool
{
    class CExcel
    {
        public string m_LastError = "";

        private CellType GetCellType( string type )
        {
            CellType cell = CellType.STRING;

            if (type == "System.Double" || type=="System.Int32" )
            {
                cell = CellType.NUMERIC;
            }
            return cell;
        }

        private void SetCellValue(Cell cell, string type, string data)
        {
            CellType cellType = CellType.STRING;

            if (type == "System.Double" || type == "System.Int32" || type=="System.Decimal")
            {
                cellType = CellType.NUMERIC;
                cell.SetCellType(cellType);
                cell.SetCellValue(double.Parse(data));
            }
            else
            {
                cell.SetCellType(cellType);
                cell.SetCellValue(data);
            }
        }

        public bool DatatableToExcel( ref DataTable dtExcel, string excelPath )
        {
            try
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();

                ////create a entry of DocumentSummaryInformation
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "Austec";
                hssfworkbook.DocumentSummaryInformation = dsi;

                ////create a entry of SummaryInformation
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "图书发退库";
                hssfworkbook.SummaryInformation = si;

                //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
                //So we insert three sheet just like what Excel does
                Sheet sheet = hssfworkbook.CreateSheet(dtExcel.TableName);

                int cellCount= dtExcel.Columns.Count;

                Row column = sheet.CreateRow(0);
                for( int j=0; j<cellCount; j++)
                {
                    Cell cell = column.CreateCell(j);
                    cell.SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString()));
                    cell.SetCellValue(dtExcel.Columns[j].ColumnName);
                    sheet.SetColumnWidth(j, 23 * 256);
                }
                for( int i=0; i<dtExcel.Rows.Count; i++)
                {
                    Row row = sheet.CreateRow(i+1);
                    for( int j=0; j<cellCount; j++)
                    {
                        Cell cell = row.CreateCell(j);
                        SetCellValue(cell, dtExcel.Columns[j].DataType.ToString(), dtExcel.Rows[i][j].ToString());
                    }
                }

                //Write the stream data of workbook to the root directory
                FileStream file = new FileStream(excelPath, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                return true;
            }
            catch (System.Exception ex)
            {
                m_LastError = "读取Excel失败:" + ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 读取单元格的数据，并转化成字符串值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private string GetCellValue(Cell cell)
        {
            string value = "";
            if(cell != null)
            {
                switch (cell.CellType ) 
                {
                    case CellType.STRING:
                        value = cell.StringCellValue;
                        break;
                    case CellType.NUMERIC:
                        value = cell.NumericCellValue.ToString();
                        break;
                    case CellType.BOOLEAN:
                        value = cell.BooleanCellValue.ToString();
                        break;
                    case   CellType.FORMULA:
                        value = cell.CellFormula;
                        break;
                    case CellType.BLANK:
                        value="";
                        break;
                    default:
                        break;
                }
            }
            return value;
        }

        /// <summary>
        /// 读取Excel文件内容到表里
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldNameList"></param>
        /// <param name="dtExcel"></param>
        /// <returns></returns>
        public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel)
        {
            dtExcel = new DataTable(tableName);
            sheetName = sheetName.Trim();
            try
            {
                FileStream file= new FileStream(excelPath, FileMode.Open);
                HSSFWorkbook workbook = new HSSFWorkbook(file);//创建工作簿对象

                NPOI.SS.UserModel.Sheet sheet = null;
                if (sheetName.Length == 0)
                {
                    sheet = workbook.GetSheetAt(0);
                }
                else
                {
                    sheet = workbook.GetSheet(sheetName);
                }

                int sheetHaveHeader = 0;
                int cellCount = 0;

                //创建表标题
                if (fieldNameList.Trim().Length>0 )
                {
                    string[] fieldList = fieldNameList.Split(',');
                    foreach ( string field in fieldList )
                    {
                        dtExcel.Columns.Add(new DataColumn(field.Trim()));
                    }
                    cellCount = fieldList.Length;
                }
                else
                {
                    sheetHaveHeader = 1;
                    NPOI.SS.UserModel.Row headerRow = sheet.GetRow(0);
                    cellCount = headerRow.LastCellNum;

                    //读取并生成标题行，这里能成功执行
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(GetCellValue(headerRow.GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL)));
                        dtExcel.Columns.Add(column);
                    }
                }
                

                //逐个读取单元格，这里就不能正确读取到                                       
                for (int i = (sheet.FirstRowNum + sheetHaveHeader); i <= sheet.LastRowNum; i++)
                {
                    NPOI.SS.UserModel.Row row = sheet.GetRow(i);
                    DataRow dataRow = dtExcel.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        dataRow[j] = GetCellValue(row.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL));

                    }
                    dtExcel.Rows.Add(dataRow);
                }
                file.Close();
                workbook = null;
                sheet = null;
                return true;

            }
            catch (System.Exception ex)
            {
                m_LastError = "读取Excel失败:" + ex.Message;
                return false;
            }           
        }


        public bool DataTableInsertIntoExcel(string excelPath, string sheetName, ref DataTable dtExcel)
        {
            try
            {
                bool bFindXlsFile = File.Exists(excelPath);
                FileStream file = null;
                HSSFWorkbook workbook = null;
                if (bFindXlsFile)
                {
                    file = new FileStream(excelPath, FileMode.Open, FileAccess.Read);
                    workbook = new HSSFWorkbook(file);//创建工作簿对象
                    file.Close();
                }
                else
                {
                    workbook = new HSSFWorkbook();
                }

                NPOI.SS.UserModel.Sheet sheet = null;
                sheet= workbook.GetSheet(sheetName);
                if (sheet == null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }

                int cellCount = dtExcel.Columns.Count;

                Row column = sheet.CreateRow(0);
                for (int j = 0; j < cellCount; j++)
                {
                    Cell cell = column.CreateCell(j);
                    cell.SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString()));
                    cell.SetCellValue(dtExcel.Columns[j].ColumnName);
                    sheet.SetColumnWidth(j, 23 * 256);
                }
                for (int i = 0; i < dtExcel.Rows.Count; i++)
                {
                    Row row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < cellCount; j++)
                    {
                        Cell cell = row.CreateCell(j);
                        SetCellValue(cell, dtExcel.Columns[j].DataType.ToString(), dtExcel.Rows[i][j].ToString());
                    }
                }

                //Write the stream data of workbook to the root directory
                file = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(file);
                file.Close();
                workbook = null;
                sheet = null;
                return true;
            }
            catch (System.Exception ex)
            {
                m_LastError = "读取Excel失败:" + ex.Message;
                return false;
            }
        }
    }
}

使用myxls读写excel

private bool ReadExcel( string csFilePath, out DataTable dt )
        {
            // 建立表名
            dt = new DataTable("client");
            string[] strFields = { "name", "code", "letter" };
            foreach (string field in strFields)
            {
                DataColumn col = new DataColumn(field);
                dt.Columns.Add(field);
            }

            if ( !File.Exists(csFilePath))
            {
                m_LastError = "未找到客户代码文件!";
                return false;
            }
            /// 读取Excel文件
            XlsDocument xls = new XlsDocument(csFilePath);
            Worksheet sheet = xls.Workbook.Worksheets[0];

            for (ushort i = 1; i < sheet.Rows.Count; i++)
            {
                DataRow row = dt.NewRow();
                for (ushort j = 1; j <=2 ; j++)      //sheet.Rows[j].CellCount
                {
                    row[j-1] = sheet.Rows[i].GetCell(j).Value.ToString().Trim();
                }
                row[2] = CLetterCode.GetChineseLetterCode(row[0].ToString());
                dt.Rows.Add(row);
            }

            return true;
        }

        public bool SaveDBtoExcel(DataSet ds, string xlsPath, string xlsName)
        {
            //XlsDocument xls = new XlsDocument();
            //xls.FileName = xlsName;
            //XF xf = xls.NewXF();// 格式对象 
            //xf.Font.FontName = "宋体";
            ////  xf.HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;//左右居中
            //// xf.VerticalAlignment = org.in2bits.MyXls.VerticalAlignments.Centered;//上线居中
            ////  xf.BottomLineStyle = ushort.Parse("2");//下边线粗
            ////   xf.BottomLineColor = org.in2bits.MyXls.Colors.Default0C;//下边线颜色
            //xf.Font.Height = 260;//自体高度，也是大小 

            //foreach (DataTable dt in ds.Tables)
            //{
            //    Worksheet sheet = xls.Workbook.Worksheets.Add(dt.TableName);
            //    for (ushort n = 1; n <= dt.Columns.Count; n++)
            //    {
            //        ColumnInfo colInfo = new ColumnInfo(xls, sheet);
            //        colInfo.ColumnIndexStart = n;
            //        colInfo.ColumnIndexEnd = n;
            //        if (n < dt.Columns.Count)
            //        {
            //            colInfo.Width = 15 * 256;
            //        }
            //        else
            //        {
            //            colInfo.Width = 23 * 256;
            //        }
            //        sheet.AddColumnInfo(colInfo);
            //    }

            //    Cells cells = sheet.Cells;
            //    for (ushort i = 0; i < dt.Rows.Count; i++)
            //    {
            //        for (ushort j = 0; j < dt.Columns.Count; j++)
            //        {
            //            Cell cell = cells.AddValueCellXF(i + 1, j + 1, dt.Rows[i][j].ToString(), xf);
            //        }
            //    }
            //}

            //xls.Save(xlsPath, true);

            return true;
        }