Exemplo n.º 1
0
 public void SaveFile()
 {
     using (FileStream stream = File.OpenWrite(this.Path))
     {
         _workbook.Write(stream);
     }
     _sheet.Dispose();
     _workbook.Dispose();
 }
Exemplo n.º 2
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream Export()
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="strHeaderText">Excel表头文本(例如:车辆列表)</param>
        public MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = "零风";      //填加xls文件作者信息
                si.ApplicationName          = "盟友1.10";  //填加xls文件创建程序信息
                si.LastAuthor               = "最后保存者信息"; //填加xls文件最后保存者信息
                si.Comments                 = "作者信息";    //填加xls文件作者信息
                si.Title                    = "标题信息";    //填加xls文件标题信息
                si.Subject                  = "主题信息";    //填加文件主题信息
                si.CreateDateTime           = System.DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow       headerRow = sheet.CreateRow(1);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            string strName = "";
                            switch (column.Caption)
                            {
                            case "id":
                                strName = "编号";
                                break;

                            case "jobNumber":
                                strName = "工号";
                                break;

                            case "userName":
                                strName = "名称";
                                break;

                            case "parent_id":
                                strName = "父id";
                                break;

                            case "stock":
                                strName = "产品";
                                break;

                            case "level":
                                strName = "等级";
                                break;

                            case "identity":
                                strName = "身份证";
                                break;

                            case "dateTime":
                                strName = "录入时间";
                                break;

                            case "shuxin":
                                strName = "属性";
                                break;

                            case "superiorNumber":
                                strName = "上级数量";
                                break;

                            case "subordinateNumber":
                                strName = "下级数量";
                                break;

                            case "merchantNumber":
                                strName = "商户数量";
                                break;

                            default:
                                break;
                            }
                            headerRow.CreateCell(column.Ordinal).SetCellValue(strName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                    case "System.String":    //字符串类型
                        newCell.SetCellValue(drValue);
                        break;

                    case "System.DateTime":    //日期类型
                        System.DateTime dateV;
                        System.DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle;    //格式化显示
                        break;

                    case "System.Boolean":    //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case "System.Int16":    //整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case "System.Decimal":    //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case "System.DBNull":    //空值处理
                        newCell.SetCellValue("");
                        break;

                    default:
                        newCell.SetCellValue("");
                        break;
                    }
                }
                #endregion

                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                sheet.Dispose();
                return(ms);
            }
        }
Exemplo n.º 3
0
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        if (Extension == ".xls")
        {
            //string notxlsx = ("This is not an xlsx file");
            //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);

            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))

                hssfworkbook = new HSSFWorkbook(file);



            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();

            //Counts the number of cells in a row and determines the columns from that.
            int counter = sheet.GetRow(0).Cells.Count;
            // J < number of columns needs to be exact at this moment
            for (int j = 0; j < counter; j++)
            {
                // set each column to a - ** letters
                // dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());

                //Get first row and set the headers for each cell
                //dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
                //Get each cell value in row 0 and return its string for a column name.
                dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
            }

            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr  = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);


                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            //Hackish way to remove the bad first row made by getting column names
            dt.Rows.RemoveAt(0);
            GridView1.Caption    = Path.GetFileName(FilePath);
            GridView1.DataSource = dt;
            //Bind the data
            GridView1.DataBind();
            sheet.Dispose();
            hssfworkbook.Dispose();
        }
        else
        {
            //Create a new epplus package using openxml
            var pck = new OfficeOpenXml.ExcelPackage();

            //load the package with the filepath I got from my fileuploader above on the button
            //pck.Load(new System.IO.FileInfo(FilePath).OpenRead());

            //stream the package
            FileStream stream = new FileStream(FilePath, FileMode.Open);
            pck.Load(stream);

            //So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
            //Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
            var ws = pck.Workbook.Worksheets[1];


            //This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
            if (ws == null)
            {
                ws = pck.Workbook.Worksheets.Add("Sheet1");
                // Obiviously I didn't add anything to the sheet so probably can count on it being blank.
            }

            //I created this datatable for below.
            DataTable tbl = new DataTable();

            //My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
            var hdr = bool.Parse(isHDR);
            Console.WriteLine(hdr);

            //Set the bool value for from above.
            var hasHeader = hdr;

            //Setup the table based on the value from my bool
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }
            var startRow = hasHeader ? 2 : 1;
            for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                var row   = tbl.NewRow();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
                tbl.Rows.Add(row);
            }
            //Bind Data to GridView
            //I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl
            GridView1.Caption    = Path.GetFileName(FilePath);
            GridView1.DataSource = tbl;
            //Bind the data
            GridView1.DataBind();

            pck.Save();
            pck.Dispose();
            stream.Close();
            // string pathD = FilePath;
            FilePath = null;
            stream   = null;
            // var fileToDelete = new FileInfo(pathD);
            // fileToDelete.Delete();
        }
    }
Exemplo n.º 4
0
 public void Dispose()
 {
     _sheet.Dispose();
     _workbook.Dispose();
 }