Ejemplo n.º 1
0
        public static int GetSheetNumber(string outputFile)
        {
            int number = 0;

            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
                number = hssfworkbook.NumberOfSheets;
            }
            catch (Exception ex)
            {
                NBCMSLoggerManager.Error(ex.ToString());
            }
            return(number);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);

            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);

            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex)
                    {
                        NBCMSLoggerManager.Error(ex.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                NBCMSLoggerManager.Error(ex.ToString());
            }
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="dt">需更新的数据</param>
        public static void ExportDTtoExcel(string outputFile, string sheetname, DataTable dt)
        {
            var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            var hssfworkbook = new XSSFWorkbook(readfile);

            readfile.Close();
            var sheet1 = hssfworkbook.GetSheet(sheetname);

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i) == null)
                        {
                            sheet1.CreateRow(i);
                        }
                        if (sheet1.GetRow(i).GetCell(j) == null)
                        {
                            sheet1.GetRow(i).CreateCell(j);
                        }
                        sheet1.GetRow(i).GetCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    catch (Exception ex)
                    {
                        NBCMSLoggerManager.Error(ex.ToString());
                    }
                }
            }
            try
            {
                var writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                NBCMSLoggerManager.Error(ex.ToString());
            }
        }
Ejemplo n.º 4
0
        public static ArrayList GetSheetName(string outputFile)
        {
            ArrayList arrayList = new ArrayList();

            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
                for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                {
                    arrayList.Add(hssfworkbook.GetSheetName(i));
                }
            }
            catch (Exception ex)
            {
                NBCMSLoggerManager.Error(ex.ToString());
            }
            return(arrayList);
        }
Ejemplo n.º 5
0
        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param>
        /// <param name="needHeader"></param>
        /// <returns></returns>
        static DataTable ImportDt(ISheet sheet, int headerRowIndex, bool needHeader)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException("sheet");
            }
            var table = new DataTable();

            try
            {
                IRow headerRow;
                var  cellCount = 0;
                if (headerRowIndex < 0 || !needHeader)
                {
                    headerRow = sheet.GetRow(0) as XSSFRow;
                    if (headerRow != null)
                    {
                        cellCount = headerRow.LastCellNum;

                        for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                        {
                            var column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }
                    }
                }
                else
                {
                    headerRow = sheet.GetRow(headerRowIndex) as XSSFRow;
                    if (headerRow != null)
                    {
                        cellCount = headerRow.LastCellNum;

                        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                        {
                            if (i == 118)
                            {
                            }
                            if (headerRow.GetCell(i) == null)
                            {
                                if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                                {
                                    var column = new DataColumn(Convert.ToString("重复列名" + i));
                                    table.Columns.Add(column);
                                }
                                else
                                {
                                    var column = new DataColumn(Convert.ToString(i));
                                    table.Columns.Add(column);
                                }
                            }
                            else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                            {
                                var column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                var column = new DataColumn(headerRow.GetCell(i).ToString());
                                table.Columns.Add(column);
                            }
                        }
                    }
                }
                for (var i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    try
                    {
                        IRow row;
                        if (sheet.GetRow(i) == null)
                        {
                            row = sheet.CreateRow(i) as XSSFRow;
                        }
                        else
                        {
                            row = sheet.GetRow(i) as XSSFRow;
                        }

                        var dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j <= cellCount; j++)
                        {
                            try
                            {
                                if (row.GetCell(j) == null)
                                {
                                    continue;
                                }
                                switch (row.GetCell(j).CellType)
                                {
                                case CellType.String:
                                    var str = row.GetCell(j).StringCellValue;
                                    if (!string.IsNullOrEmpty(str))
                                    {
                                        dataRow[j] = str;
                                    }
                                    else
                                    {
                                        dataRow[j] = null;
                                    }
                                    break;

                                case CellType.Numeric:
                                    dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                    break;

                                case CellType.Boolean:
                                    dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                    break;

                                case CellType.Error:
                                    dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                    break;

                                default:
                                    dataRow[j] = "";
                                    break;
                                }
                            }
                            catch (Exception exception)
                            {
                                NBCMSLoggerManager.Error(exception.ToString());
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception exception)
                    {
                        NBCMSLoggerManager.Error(exception.ToString());
                    }
                }
            }
            catch (Exception exception)
            {
                NBCMSLoggerManager.Error(exception.ToString());
            }
            return(table);
        }