免费高效实用的.NET操作Excel组件NPOI(.NET组件介绍之六)

  很多的软件项目几乎都包含着对文档的操作,前面已经介绍过两款操作文档的组件,现在介绍一款文档操作的组件NPOI。

  NPOI可以生成没有安装在您的服务器上的Microsoft Office套件的Excel报表,并且在后台调用Microsoft Excel ActiveX更有效率;从Office文档中提取文本,以帮助您实现全文索引功能(大多数时候,此功能用于创建搜索引擎); 从Office文档提取图像; 生成包含公式的Excel工作表。

 一.NPOI组件概述:

     NPOI是完全免费使用; 涵盖Excel的大多数功能(单元格样式,数据格式,公式等);支持xls,xlsx,docx;设计为面向接口(看看NPOI.SS命名空间);支持不仅导出而且导入; .Net 2.0甚至为xlsx和docx(虽然我们也支持.NET 4.0); 来自世界各地的成功案例;巨大的基本例子;对隔离存储没有依赖。

     以上是NPOI的优点,其他一些优点可以不用太在意,估计很多人对“支持xls,xlsx,docx”这一特点感觉有些惊讶,因为在很多人的印象里面NPOI就是对Excel进行相关的操作,但是在这里突然看到了对docx也可以操作,这一特点可能让很多人感到欣喜,因为NPOI的的确确是一个很不错的组件,用过的人都说好,我也不例外。

    NPOI的运行要求:VS2010与.NET 4.0运行时;VS2005或VS2008与.NET 2.0运行时(SP1);vs2003与.NET 1.1;Mono;ASP.NET中的中等信任环境。

二.NPOI核心类和方法解析:

    以上是对NPOI的相关背景和使用环境做了一个简单的介绍,接下来我具体的看一下NPOI的一些核心类和方法,由于下载的是DLL文件,还是采用.NET Reflector对DLL文件进行反编译,以此查看源代码。

   如果需要具体的了解NPOI可以直接访问:http://npoi.codeplex.com/SourceControl/latest,提供了NPOI的源码和一些demo,由于本溪介绍的重点是NPOI对Excel的操作,所以下面的类和实例主要是对操作Excel的介绍,如果需要对docx的操作,可以具体查看相应的类demo。

   1.XSSFWorkbook类CreateSheet():创建表。

public ISheet CreateSheet(string sheetname)
{
    if (sheetname == null)
    {
        throw new ArgumentException("sheetName must not be null");
    }
    if (this.ContainsSheet(sheetname, this.sheets.Count))
    {
        throw new ArgumentException("The workbook already contains a sheet of this name");
    }
    if (sheetname.Length > 0x1f)
    {
        sheetname = sheetname.Substring(0, 0x1f);
    }
    WorkbookUtil.ValidateSheetName(sheetname);
    CT_Sheet sheet = this.AddSheet(sheetname);
    int index = 1;
    foreach (XSSFSheet sheet2 in this.sheets)
    {
        index = (int) Math.Max((long) (sheet2.sheet.sheetId + 1), (long) index);
    }
Label_0099:
    foreach (XSSFSheet sheet3 in this.sheets)
    {
        index = (int) Math.Max((long) (sheet3.sheet.sheetId + 1), (long) index);
    }
    string fileName = XSSFRelation.WORKSHEET.GetFileName(index);
    foreach (POIXMLDocumentPart part in base.GetRelations())
    {
        if ((part.GetPackagePart() != null) && fileName.Equals(part.GetPackagePart().PartName.Name))
        {
            index++;
            goto Label_0099;
        }
    }
    XSSFSheet item = (XSSFSheet) base.CreateRelationship(XSSFRelation.WORKSHEET, XSSFFactory.GetInstance(), index);
    item.sheet = sheet;
    sheet.id = item.GetPackageRelationship().Id;
    sheet.sheetId = (uint) index;
    if (this.sheets.Count == 0)
    {
        item.IsSelected = true;
    }
    this.sheets.Add(item);
    return item;
}

   2.XSSFSheet类Write():将文件流写入到excel。

internal virtual void Write(Stream stream)
{
    bool flag = false;
    if (this.worksheet.sizeOfColsArray() == 1)
    {
        CT_Cols colsArray = this.worksheet.GetColsArray(0);
        if (colsArray.sizeOfColArray() == 0)
        {
            flag = true;
            this.worksheet.SetColsArray(null);
        }
        else
        {
            this.SetColWidthAttribute(colsArray);
        }
    }
    if (this.hyperlinks.Count > 0)
    {
        if (this.worksheet.hyperlinks == null)
        {
            this.worksheet.AddNewHyperlinks();
        }
        CT_Hyperlink[] array = new CT_Hyperlink[this.hyperlinks.Count];
        for (int i = 0; i < array.Length; i++)
        {
            XSSFHyperlink hyperlink = this.hyperlinks[i];
            hyperlink.GenerateRelationIfNeeded(base.GetPackagePart());
            array[i] = hyperlink.GetCTHyperlink();
        }
        this.worksheet.hyperlinks.SetHyperlinkArray(array);
    }
    foreach (XSSFRow row in this._rows.Values)
    {
        row.OnDocumentWrite();
    }
    Dictionary<string, string> dictionary = new Dictionary<string, string>();
    dictionary[ST_RelationshipId.NamespaceURI] = "r";
    new WorksheetDocument(this.worksheet).Save(stream);
    if (flag)
    {
        this.worksheet.AddNewCols();
    }
}

  3.XSSFSheet类CreateRow():创建行。

public virtual IRow CreateRow(int rownum)
{
    CT_Row cTRow;
    XSSFRow row2 = this._rows.ContainsKey(rownum) ? this._rows[rownum] : null;
    if (row2 != null)
    {
        cTRow = row2.GetCTRow();
        cTRow.Set(new CT_Row());
    }
    else if ((this._rows.Count == 0) || (rownum > this.GetLastKey(this._rows.Keys)))
    {
        cTRow = this.worksheet.sheetData.AddNewRow();
    }
    else
    {
        int count = this.HeadMap(this._rows, rownum).Count;
        cTRow = this.worksheet.sheetData.InsertNewRow(count);
    }
    XSSFRow row3 = new XSSFRow(cTRow, this) {
        RowNum = rownum
    };
    this._rows[rownum] = row3;
    return row3;
}

  4.XSSFWorkbook类GetSheet:获取表。

public ISheet GetSheet(string name)
{
    foreach (XSSFSheet sheet in this.sheets)
    {
        if (name.Equals(sheet.SheetName, StringComparison.InvariantCultureIgnoreCase))
        {
            return sheet;
        }
    }
    return null;
}

  5.WorkbookFactory类:

  public class PropertySetFactory
  {
    public static PropertySet Create(DirectoryEntry dir, string name);
    public static PropertySet Create(Stream stream);
    public static SummaryInformation CreateSummaryInformation();
    public static DocumentSummaryInformation CreateDocumentSummaryInformation();
  }

  6.DocumentSummaryInformation:

[Serializable]
public class DocumentSummaryInformation : SpecialPropertySet
{
    // Fields
    public const string DEFAULT_STREAM_NAME = "\x0005DocumentSummaryInformation";

    // Methods
    public DocumentSummaryInformation(PropertySet ps);
    private void EnsureSection2();
    public void RemoveByteCount();
    public void RemoveCategory();
    public void RemoveCompany();
    public void RemoveCustomProperties();
    public void RemoveDocparts();
    public void RemoveHeadingPair();
    public void RemoveHiddenCount();
    public void RemoveLineCount();
    public void RemoveLinksDirty();
    public void RemoveManager();
    public void RemoveMMClipCount();
    public void RemoveNoteCount();
    public void RemoveParCount();
    public void RemovePresentationFormat();
    public void RemoveScale();
    public void RemoveSlideCount();

    // Properties
    public int ByteCount { get; set; }
    public string Category { get; set; }
    public string Company { get; set; }
    public CustomProperties CustomProperties { get; set; }
    public byte[] Docparts { get; set; }
    public byte[] HeadingPair { get; set; }
    public int HiddenCount { get; set; }
    public int LineCount { get; set; }
    public bool LinksDirty { get; set; }
    public string Manager { get; set; }
    public int MMClipCount { get; set; }
    public int NoteCount { get; set; }
    public int ParCount { get; set; }
    public string PresentationFormat { get; set; }
    public override PropertyIDMap PropertySetIDMap { get; }
    public bool Scale { get; set; }
    public int SlideCount { get; set; }
}



具体方法:
private void EnsureSection2()
{
    if (this.SectionCount < 2)
    {
        MutableSection section = new MutableSection();
        section.SetFormatID(SectionIDMap.DOCUMENT_SUMMARY_INFORMATION_ID2);
        this.AddSection(section);
    }
}

    以上只是对部分的类和方法做了简单的说明,需要了解更多的内容,可以进入官网下载源码,或者使用软件查看DLL文件。

三.NPOI操作实例:

   1.枚举(Excel单元格数据类型):

    /// <summary>
    /// 枚举(Excel单元格数据类型)
    /// </summary>
    public enum NpoiDataType
    {
        /// <summary>
        /// 字符串类型-值为1
        /// </summary>
        String,
        /// <summary>
        /// 布尔类型-值为2
        /// </summary>
        Bool,
        /// <summary>
        /// 时间类型-值为3
        /// </summary>
        Datetime,
        /// <summary>
        /// 数字类型-值为4
        /// </summary>
        Numeric,
        /// <summary>
        /// 复杂文本类型-值为5
        /// </summary>
        Richtext,
        /// <summary>
        /// 空白
        /// </summary>
        Blank,
        /// <summary>
        /// 错误
        /// </summary>
        Error
    }

    2. 将DataTable数据导入到excel中:

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="fileName">文件夹路径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
            {
                workbook = new XSSFWorkbook();
            }                
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
            {
                workbook = new HSSFWorkbook();
            }

            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                ISheet sheet;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                int j;
                int count;
                //写入DataTable的列名,写入单元格中
                if (isColumnWritten)
                {
                    var row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                //遍历循环datatable具体数据项
                int i;
                for (i = 0; i < data.Rows.Count; ++i)
                {
                    var row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                //将文件流写入到excel
                workbook.Write(fs);
                return count;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

   3.将excel中的数据导入到DataTable中:

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="fileName">文件路径</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            var data = new DataTable();
            IWorkbook workbook = null;
            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }

                ISheet sheet = null;
                if (workbook != null)
                {
                    //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                    sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                }
                if (sheet == null) return data;
                var firstRow = sheet.GetRow(0);
                //一行最后一个cell的编号 即总的列数
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell.StringCellValue;
                        if (cellValue == null) continue;
                        var column = new DataColumn(cellValue);
                        data.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最后一列的标号
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    //没有数据的行默认是null
                    if (row == null) continue;
                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理,没有数据的单元格都默认是null
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }

                return data;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

   4.读取Excel文件内容转换为DataSet:

       /// <summary>
        /// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]
        /// </summary>
        /// <param name="fileName">文件绝对路径</param>
        /// <param name="startRow">数据开始行数(1为第一行)</param>
        /// <param name="columnDataType">每列的数据类型</param>
        /// <returns></returns>
        public static DataSet ReadExcel(string fileName, int startRow, params NpoiDataType[] columnDataType)
        {
            var ds = new DataSet("ds");
            var dt = new DataTable("dt");
            var sb = new StringBuilder();
            using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                //使用接口,自动识别excel2003/2007格式
                var workbook = WorkbookFactory.Create(stream);
                //得到里面第一个sheet
                var sheet = workbook.GetSheetAt(0);
                int j;
                IRow row;
                //ColumnDataType赋值
                if (columnDataType.Length <= 0)
                {
                    //得到第i行
                    row = sheet.GetRow(startRow - 1);
                    columnDataType = new NpoiDataType[row.LastCellNum];
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var hs = row.GetCell(i);
                        columnDataType[i] = GetCellDataType(hs);
                    }
                }
                for (j = 0; j < columnDataType.Length; j++)
                {
                    var tp = GetDataTableType(columnDataType[j]);
                    dt.Columns.Add("c" + j, tp);
                }
                for (var i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
                {
                    //得到第i行
                    row = sheet.GetRow(i);
                    if (row == null) continue;
                    try
                    {
                        var dr = dt.NewRow();

                        for (j = 0; j < columnDataType.Length; j++)
                        {
                            dr["c" + j] = GetCellData(columnDataType[j], row, j);
                        }
                        dt.Rows.Add(dr);
                    }
                    catch (Exception er)
                    {
                        sb.Append(string.Format("第{0}行出错:{1}\r\n", i + 1, er.Message));
                    }
                }
                ds.Tables.Add(dt);
            }
            if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
            return ds;
        }

   5.从DataSet导出到2003:

        /// <summary>
        /// 从DataSet导出到MemoryStream流2003
        /// </summary>
        /// <param name="saveFileName">文件保存路径</param>
        /// <param name="sheetName">Excel文件中的Sheet名称</param>
        /// <param name="ds">存储数据的DataSet</param>
        /// <param name="startRow">从哪一行开始写入,从0开始</param>
        /// <param name="datatypes">DataSet中的各列对应的数据类型</param>
        public static bool CreateExcel2003(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
        {
            try
            {
                if (startRow < 0) startRow = 0;
                var wb = new HSSFWorkbook();
                var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "pkm";
                var si = PropertySetFactory.CreateSummaryInformation();
                si.Title =
                si.Subject = "automatic genereted document";
                si.Author = "pkm";
                wb.DocumentSummaryInformation = dsi;
                wb.SummaryInformation = si;
                var sheet = wb.CreateSheet(sheetName);
                //sheet.SetColumnWidth(0, 50 * 256);
                //sheet.SetColumnWidth(1, 100 * 256);
                ICell cell;
                int j;
                var maxLength = 0;
                var curLength = 0;
                object columnValue;
                var dt = ds.Tables[0];
                if (datatypes.Length < dt.Columns.Count)
                {
                    datatypes = new NpoiDataType[dt.Columns.Count];
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        var dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
                        switch (dtcolumntype)
                        {
                            case "string":
                                datatypes[i] = NpoiDataType.String;
                                break;
                            case "datetime":
                                datatypes[i] = NpoiDataType.Datetime;
                                break;
                            case "boolean":
                                datatypes[i] = NpoiDataType.Bool;
                                break;
                            case "double":
                                datatypes[i] = NpoiDataType.Numeric;
                                break;
                            default:
                                datatypes[i] = NpoiDataType.String;
                                break;
                        }
                    }
                }

                // 创建表头
                var row = sheet.CreateRow(0);
                //样式
                var style1 = wb.CreateCellStyle();
                //字体
                var font1 = wb.CreateFont();
                //字体颜色
                font1.Color = HSSFColor.White.Index;
                //字体加粗样式
                font1.Boldweight = (short)FontBoldWeight.Bold;
                //style1.FillBackgroundColor = HSSFColor.WHITE.index;                                                            
                style1.FillForegroundColor = HSSFColor.Green.Index;
                //GetXLColour(wb, LevelOneColor);// 设置图案色
                //GetXLColour(wb, LevelOneColor);// 设置背景色
                style1.FillPattern = FillPattern.SolidForeground;
                //样式里的字体设置具体的字体样式
                style1.SetFont(font1);
                //文字水平对齐方式
                style1.Alignment = HorizontalAlignment.Center;
                //文字垂直对齐方式
                style1.VerticalAlignment = VerticalAlignment.Center;
                row.HeightInPoints = 25;
                for (j = 0; j < dt.Columns.Count; j++)
                {
                    columnValue = dt.Columns[j].ColumnName;
                    curLength = Encoding.Default.GetByteCount(columnValue.ToString());
                    maxLength = (maxLength < curLength ? curLength : maxLength);
                    var colounwidth = 256 * maxLength;
                    sheet.SetColumnWidth(j, colounwidth);
                    try
                    {
                        //创建第0行的第j列
                        cell = row.CreateCell(j);
                        //单元格式设置样式
                        cell.CellStyle = style1;

                        try
                        {
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(columnValue.ToString());
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message);
                        }

                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                // 创建每一行
                for (var i = startRow; i < ds.Tables[0].Rows.Count; i++)
                {
                    var dr = ds.Tables[0].Rows[i];
                    //创建第i行
                    row = sheet.CreateRow(i + 1);
                    for (j = 0; j < dt.Columns.Count; j++)
                    {
                        columnValue = dr[j];
                        curLength = Encoding.Default.GetByteCount(columnValue.ToString());
                        maxLength = (maxLength < curLength ? curLength : maxLength);
                        var colounwidth = 256 * maxLength;
                        sheet.SetColumnWidth(j, colounwidth);
                        try
                        {
                            //创建第i行的第j列
                            cell = row.CreateCell(j);
                            // 插入第j列的数据
                            try
                            {
                                var dtype = datatypes[j];
                                switch (dtype)
                                {
                                    case NpoiDataType.String:
                                        {
                                            cell.SetCellType(CellType.Numeric);
                                            cell.SetCellValue(columnValue.ToString());
                                        }
                                        break;
                                    case NpoiDataType.Datetime:
                                        {
                                            cell.SetCellType(CellType.Numeric);
                                            cell.SetCellValue(columnValue.ToString());
                                        }
                                        break;
                                    case NpoiDataType.Numeric:
                                        {
                                            cell.SetCellType(CellType.Numeric);
                                            cell.SetCellValue(Convert.ToDouble(columnValue));
                                        }
                                        break;
                                    case NpoiDataType.Bool:
                                        {
                                            cell.SetCellType(CellType.Numeric);
                                            cell.SetCellValue(Convert.ToBoolean(columnValue));
                                        }
                                        break;
                                    case NpoiDataType.Richtext:
                                        {
                                            cell.SetCellType(CellType.Numeric);
                                            cell.SetCellValue(columnValue.ToString());
                                        }
                                        break;
                                }
                            }
                            catch (Exception ex)
                            {
                                cell.SetCellType(CellType.Numeric);
                                cell.SetCellValue(columnValue.ToString());
                                throw new Exception(ex.Message);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message);
                        }
                    }
                }
                //生成文件在服务器上
                using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    wb.Write(fs);
                }

                return true;
            }
            catch (Exception er)
            {
                throw new Exception(er.Message);
            }

        }

    6.从DataSet导出到MemoryStream流2007:

       /// <summary>
        /// 从DataSet导出到MemoryStream流2007
        /// </summary>
        /// <param name="saveFileName">文件保存路径</param>
        /// <param name="sheetName">Excel文件中的Sheet名称</param>
        /// <param name="ds">存储数据的DataSet</param>
        /// <param name="startRow">从哪一行开始写入,从0开始</param>
        /// <param name="datatypes">DataSet中的各列对应的数据类型</param>
        public static bool CreateExcel2007(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
        {
            try
            {
                if (startRow < 0) startRow = 0;
                var wb = new XSSFWorkbook();
                var sheet = wb.CreateSheet(sheetName);
                ICell cell;
                int j;
                var maxLength = 0;
                int curLength;
                object columnValue;
                var dt = ds.Tables[0];
                if (datatypes.Length < dt.Columns.Count)
                {
                    datatypes = new NpoiDataType[dt.Columns.Count];
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        var dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
                        switch (dtcolumntype)
                        {
                            case "string":
                                datatypes[i] = NpoiDataType.String;
                                break;
                            case "datetime":
                                datatypes[i] = NpoiDataType.Datetime;
                                break;
                            case "boolean":
                                datatypes[i] = NpoiDataType.Bool;
                                break;
                            case "double":
                                datatypes[i] = NpoiDataType.Numeric;
                                break;
                            default:
                                datatypes[i] = NpoiDataType.String;
                                break;
                        }
                    }
                }
                //创建表头
                var row = sheet.CreateRow(0);
                //样式
                var style1 = wb.CreateCellStyle();
                //字体
                var font1 = wb.CreateFont();
                //字体颜色
                font1.Color = HSSFColor.White.Index;
                //字体加粗样式
                font1.Boldweight = (short)FontBoldWeight.Bold;
                //style1.FillBackgroundColor = HSSFColor.WHITE.index;
                //GetXLColour(wb, LevelOneColor);
                // 设置图案色
                style1.FillForegroundColor = HSSFColor.Green.Index;
                //GetXLColour(wb, LevelOneColor);// 设置背景色
                style1.FillPattern = FillPattern.SolidForeground;
                //样式里的字体设置具体的字体样式
                style1.SetFont(font1);
                //文字水平对齐方式
                style1.Alignment = HorizontalAlignment.Center;
                //文字垂直对齐方式
                style1.VerticalAlignment = VerticalAlignment.Center;
                row.HeightInPoints = 25;
                for (j = 0; j < dt.Columns.Count; j++)
                {
                    columnValue = dt.Columns[j].ColumnName;
                    curLength = Encoding.Default.GetByteCount(columnValue.ToString());
                    maxLength = (maxLength < curLength ? curLength : maxLength);
                    var colounwidth = 256 * maxLength;
                    sheet.SetColumnWidth(j, colounwidth);
                    try
                    {
                        //创建第0行的第j列
                        cell = row.CreateCell(j);
                        //单元格式设置样式
                        cell.CellStyle = style1;

                        try
                        {
                            cell.SetCellValue(columnValue.ToString());
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message);
                        }

                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                // 创建每一行
                for (var i = startRow; i < ds.Tables[0].Rows.Count; i++)
                {
                    var dr = ds.Tables[0].Rows[i];
                    //创建第i行
                    row = sheet.CreateRow(i + 1);
                    for (j = 0; j < dt.Columns.Count; j++)
                    {
                        columnValue = dr[j];
                        curLength = Encoding.Default.GetByteCount(columnValue.ToString());
                        maxLength = (maxLength < curLength ? curLength : maxLength);
                        var colounwidth = 256 * maxLength;
                        sheet.SetColumnWidth(j, colounwidth);
                        try
                        {
                            //创建第i行的第j列
                            cell = row.CreateCell(j);
                            // 插入第j列的数据
                            try
                            {
                                var dtype = datatypes[j];
                                switch (dtype)
                                {
                                    case NpoiDataType.String:
                                        {
                                            cell.SetCellValue(columnValue.ToString());
                                        }
                                        break;
                                    case NpoiDataType.Datetime:
                                        {
                                            cell.SetCellValue(columnValue.ToString());
                                        }
                                        break;
                                    case NpoiDataType.Numeric:
                                        {
                                            cell.SetCellValue(Convert.ToDouble(columnValue));
                                        }
                                        break;
                                    case NpoiDataType.Bool:
                                        {
                                            cell.SetCellValue(Convert.ToBoolean(columnValue));
                                        }
                                        break;
                                    case NpoiDataType.Richtext:
                                        {
                                            cell.SetCellValue(columnValue.ToString());
                                        }
                                        break;
                                }
                            }
                            catch (Exception ex)
                            {
                                cell.SetCellValue(columnValue.ToString());
                                throw new Exception(ex.Message);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message);
                        }
                    }
                }
                //生成文件在服务器上
                using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    wb.Write(fs);
                }
                return true;
            }
            catch (Exception er)
            {
                throw new Exception(er.Message);
            }

        }

   7.读Excel-根据NpoiDataType创建的DataTable列的数据类型:

        /// <summary>
        /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型
        /// </summary>
        /// <param name="datatype"></param>
        /// <returns></returns>
        private static Type GetDataTableType(NpoiDataType datatype)
        {
            var tp = typeof(string);
            switch (datatype)
            {
                case NpoiDataType.Bool:
                    tp = typeof(bool);
                    break;
                case NpoiDataType.Datetime:
                    tp = typeof(DateTime);
                    break;
                case NpoiDataType.Numeric:
                    tp = typeof(double);
                    break;
                case NpoiDataType.Error:
                    tp = typeof(string);
                    break;
                case NpoiDataType.Blank:
                    tp = typeof(string);
                    break;
            }
            return tp;
        }


        /// <summary>
        /// 读Excel-得到不同数据类型单元格的数据
        /// </summary>
        /// <param name="datatype">数据类型</param>
        /// <param name="row">数据中的一行</param>
        /// <param name="column">哪列</param>
        /// <returns></returns>
        private static object GetCellData(NpoiDataType datatype, IRow row, int column)
        {
            switch (datatype)
            {
                case NpoiDataType.String:
                    try
                    {
                        return row.GetCell(column).DateCellValue;
                    }
                    catch
                    {
                        try
                        {
                            return row.GetCell(column).StringCellValue;
                        }
                        catch
                        {
                            return row.GetCell(column).NumericCellValue;
                        }
                    }
                case NpoiDataType.Bool:
                    try { return row.GetCell(column).BooleanCellValue; }
                    catch { return row.GetCell(column).StringCellValue; }
                case NpoiDataType.Datetime:
                    try { return row.GetCell(column).DateCellValue; }
                    catch { return row.GetCell(column).StringCellValue; }
                case NpoiDataType.Numeric:
                    try { return row.GetCell(column).NumericCellValue; }
                    catch { return row.GetCell(column).StringCellValue; }
                case NpoiDataType.Richtext:
                    try { return row.GetCell(column).RichStringCellValue; }
                    catch { return row.GetCell(column).StringCellValue; }
                case NpoiDataType.Error:
                    try { return row.GetCell(column).ErrorCellValue; }
                    catch { return row.GetCell(column).StringCellValue; }
                case NpoiDataType.Blank:
                    try { return row.GetCell(column).StringCellValue; }
                    catch { return ""; }
                default: return "";
            }
        }

        /// <summary>
        /// 获取单元格数据类型
        /// </summary>
        /// <param name="hs">单元格对象</param>
        /// <returns></returns>
        private static NpoiDataType GetCellDataType(ICell hs)
        {
            NpoiDataType dtype;
            DateTime t1;
            var cellvalue = "";

            switch (hs.CellType)
            {
                case CellType.Blank:
                    dtype = NpoiDataType.String;
                    cellvalue = hs.StringCellValue;
                    break;
                case CellType.Boolean:
                    dtype = NpoiDataType.Bool;
                    break;
                case CellType.Numeric:
                    dtype = NpoiDataType.Numeric;
                    cellvalue = hs.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                    break;
                case CellType.String:
                    dtype = NpoiDataType.String;
                    cellvalue = hs.StringCellValue;
                    break;
                case CellType.Error:
                    dtype = NpoiDataType.Error;
                    break;
                default:
                    dtype = NpoiDataType.Datetime;
                    break;
            }
            if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
            return dtype;
        }

四.总结:

    本文是接着上五篇介绍.NET组件,目的只是在于总结一些组件的用法,将文章作为一个引子,各位读者可以根据文章的介绍更加深入的去了解相关组件。有些地方写的有误,还望多多包涵和指正,欢迎大家给我建议介绍一些你们在项目中经常使用的组件,可以跟大家做一个分享。

.NET组件介绍系列:

  一款开源免费的.NET文档操作组件DocX(.NET组件介绍之一)

高效而稳定的企业级.NET Office 组件Spire(.NET组件介绍之二)

 最好的.NET开源免费ZIP库DotNetZip(.NET组件介绍之三)

免费开源的DotNet二维码操作组件ThoughtWorks.QRCode(.NET组件介绍之四)

免费开源的DotNet任务调度组件Quartz.NET(.NET组件介绍之五)

免费高效实用的Excel操作组件NPOI(.NET组件介绍之六)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏c#开发者

Modal popup dialog window with multiple parameters

Introduction This article shows a modal popup dialog window which passes and ret...

3115
来自专栏成长道路

org.apache.spark.sql.AnalysisException: Table or view not found: `traintext`.`train`; line 1 pos 14;

恭喜老铁,跟我遇到了一样的问题,接下来是解决方法: 遇到的问题: org.apache.spark.sql.AnalysisException: Table o...

8730
来自专栏乐沙弥的世界

[INS-30507] Empty ASM disk group

    最近的Oracle 11g RAC安装碰到了INS-30507错误,也就是在grid安装到创建ASM磁盘组的时候找不到任何候选磁盘,google了N多安...

843
来自专栏技术碎碎念

windows API 开发飞机订票系统 图形化界面 (三)

来吧,接下来是各个功能的函数的实现代码。 首先,程序运行时加载读入账户信息和航班信息。接下来就该读取文件了。 我把账户资料和航班信息储存在了.txt文件里 那么...

40514
来自专栏互联网开发者交流社区

温湿度系统(花葵、库房检测)

823
来自专栏Golang语言社区

使用WebAssembly和Go编写前端Web框架

JavaScript Frontend frameworks have undoubtedly helped to push the boundaries of...

823
来自专栏阿炬.NET

【要什么自行车】ASP.NET MVC4笔记03:调用编辑器 kindeditor

2935
来自专栏施炯的IoT开发专栏

转贴-WP7开发资源大收集

文章作者: jason huang 文章标签: Microsoft, Windows Phone 7, WP7 转贴链接: WP7开发资源大收集 这里收集...

1778
来自专栏pangguoming

Node.js 开发模式(设计模式)

Asynchronous code & Synchronous code As we have seen in an earlier post (here), ...

2917
来自专栏C#

一款开源免费的.NET文档操作组件DocX(.NET组件介绍之一)

    在目前的软件项目中,都会较多的使用到对文档的操作,用于记录和统计相关业务信息。由于系统自身提供了对文档的相关操作,所以在一定程度上极大的简化了软件...

2677

扫码关注云+社区