Beispiel #1
0
        // Creates Excel Workbook that is used for Export to Excel request.
        protected internal void CreateExcelBook()
        {
            ISDExcelBook = new ISDWorkbook();

            ISDExcelBook.Properties.Title = Title;

            ISDExcelSheet = new ISDWorksheet("Sheet1");
            ISDExcelBook.Worksheets.Add(ISDExcelSheet);

            ISDExcelRow = new ISDWorksheetRow();
            ISDExcelSheet.Table.Rows.Add(ISDExcelRow);
        }
Beispiel #2
0
        public void Save(System.IO.Stream OutputStream, System.Web.HttpResponse response)
        {
            string filename        = "Export_" + Guid.NewGuid().ToString();
            string completePathOne = GenerateUniquePath(filename, "xlsx");
            string completePathTwo = GenerateUniquePath(filename, "xls");
            string completePath    = null;
            string tableName       = null;

            ISDWorksheet ws = (ISDWorksheet)this.Worksheets[0];
            ISDTable     ta = ws.Table;

            tableName = ws.Name;
            ArrayList       rows = ta.Rows;
            ISDWorksheetRow row0 = null;

            if (rows.Count > 0)
            {
                row0 = (ISDWorksheetRow)rows[0];
            }

            ISDWorksheetRow row1 = row0;

            if (rows.Count > 1)
            {
                row1 = (ISDWorksheetRow)rows[1];
            }

            ArrayList cols     = ta.Columns;
            string    colDefs  = GetColumnDefinitions(cols, row0.Cells, row1.Cells, true);
            string    colNames = GetColumnDefinitions(cols, row0.Cells, row1.Cells, false);

            completePath = completePathTwo;

            HSSFWorkbook hssfwb = new HSSFWorkbook();

            IDataFormat format = hssfwb.CreateDataFormat();

            ISheet sh = hssfwb.CreateSheet("Sheet1");

            int rIndex = 0;

            IRow r = sh.CreateRow(rIndex);

            int c = 0;


            HSSFCellStyle[] styles = new HSSFCellStyle[row0.Cells.Count + 1];

            foreach (ISDWorksheetCell hCell in row0.Cells)
            {
                HSSFCellStyle style = (HSSFCellStyle)hssfwb.CreateCellStyle();
                ICell         ce    = r.CreateCell(c);

                ce.SetCellValue(hCell.Text);

                style.WrapText = true;
                styles[c]      = (HSSFCellStyle)hssfwb.CreateCellStyle();
                ce.CellStyle   = style;
                c += 1;
            }

            for (rIndex = 1; rIndex <= rows.Count - 1; rIndex++)
            {
                ISDWorksheetRow currentRow = (ISDWorksheetRow)rows[rIndex];

                r = sh.CreateRow(rIndex);

                c = 0;

                for (int i = 0; i <= currentRow.Cells.Count - 1; i++)
                {
                    //myValue = myValue.Replace("$", "").Replace(",", "")
                    ICell ce = r.CreateCell(c);

                    HSSFCellStyle    style = styles[i];
                    ISDWorksheetCell dCell = (ISDWorksheetCell)currentRow.Cells[i];

                    string formatStr = dCell.Format;
                    if (dCell.Type == ISDDataType.ISDInteger || dCell.Type == ISDDataType.ISDNumber)
                    {
                        ce.SetCellType(CellType.NUMERIC);

                        if (dCell.Value != null)
                        {
                            ce.SetCellValue(Convert.ToDouble(dCell.Value));
                        }

                        if (GetBuildInFormat(dCell.Format) > 0)
                        {
                            style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
                        }
                        else
                        {
                            System.Globalization.NumberFormatInfo info = System.Globalization.CultureInfo.CurrentCulture.NumberFormat;
                            if (string.IsNullOrEmpty(dCell.Format) || dCell.Format == null)
                            {
                                formatStr = "##0.00";
                            }
                            else if (dCell.Format.Contains("C") || dCell.Format.Contains("c"))
                            {
                                formatStr = info.CurrencySymbol + "##0.00";
                            }
                            else if (dCell.Format.Contains("P") || dCell.Format.Contains("p"))
                            {
                                formatStr = "##0.00" + info.PercentSymbol;
                            }
                            else if (dCell.Format.Contains(info.CurrencySymbol) || dCell.Format.Contains(info.PercentSymbol))
                            {
                                // use the user given display format
                            }
                            else
                            {
                                formatStr = "##0.00";
                            }
                            style.DataFormat = format.GetFormat(formatStr);
                        }
                    }
                    else if (dCell.Type == ISDDataType.ISDDateTime)
                    {
                        if (dCell.Value != null)
                        {
                            ce.SetCellType(CellType.NUMERIC);
                            ce.SetCellValue(Convert.ToDateTime(dCell.Value));
                        }

                        if (GetBuildInFormat(dCell.Format) > 0)
                        {
                            style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
                        }
                        else
                        {
                            System.Globalization.DateTimeFormatInfo info = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat;

                            // convert the date format understood by Excel
                            // see http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.71).aspx
                            switch (dCell.Format)
                            {
                            case "d":
                                formatStr = info.ShortDatePattern;
                                break;

                            case "D":
                                formatStr = info.LongDatePattern;
                                break;

                            case "t":
                                formatStr = info.ShortTimePattern;
                                break;

                            case "T":
                                formatStr = info.LongTimePattern;
                                break;

                            case "f":
                                formatStr = info.LongDatePattern + " " + info.ShortTimePattern;
                                break;

                            case "F":
                                formatStr = info.FullDateTimePattern;
                                break;

                            case "g":
                                formatStr = info.ShortDatePattern + " " + info.ShortTimePattern;
                                break;

                            case "G":
                                formatStr = info.ShortDatePattern + " " + info.LongTimePattern;
                                break;

                            case "M":
                            case "m":
                                formatStr = info.MonthDayPattern;
                                break;

                            case "R":
                            case "r":
                                formatStr = info.RFC1123Pattern;
                                break;

                            case "s":
                                formatStr = info.SortableDateTimePattern;
                                break;

                            case "u":
                                formatStr = info.UniversalSortableDateTimePattern;
                                break;

                            case "U":
                                formatStr = info.FullDateTimePattern;
                                break;

                            case "Y":
                            case "y":
                                formatStr = info.YearMonthPattern;
                                break;

                            default:
                                formatStr = info.ShortDatePattern;
                                break;
                            }

                            // some pattern above might return t but this is not recognized by Excel, so remove it
                            formatStr        = formatStr.Replace("t", "");
                            style.DataFormat = format.GetFormat(formatStr);
                        }
                    }
                    else
                    {
                        ce.SetCellType(CellType.STRING);
                        if (dCell.Value != null)
                        {
                            string myValue = dCell.Text;
                            if (myValue.Length > 255)
                            {
                                myValue = myValue.Substring(0, 255);
                            }
                            ce.SetCellValue(myValue);
                        }

                        if (GetBuildInFormat(dCell.Format) > 0)
                        {
                            style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
                        }
                        else
                        {
                            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("TEXT");
                            style.WrapText   = true;
                        }
                    }

                    ce.CellStyle = style;
                    c           += 1;
                }
            }

            MemoryStream ms = new MemoryStream();

            hssfwb.Write(ms);

            string NPOIDownloadFileName = this.Properties.Title;

            if (completePath.EndsWith(".xlsx"))
            {
                NPOIDownloadFileName += ".xlsx";
            }
            else
            {
                NPOIDownloadFileName += ".xls";
            }

            response.ClearHeaders();
            response.Clear();
            response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
            response.Cache.SetMaxAge(new TimeSpan(0));
            response.Cache.SetExpires(new DateTime(0));
            response.Cache.SetNoServerCaching();
            response.AppendHeader("Content-Disposition", ("attachment; filename=\"" + (NPOIDownloadFileName + "\"")));
            response.ContentType = "application/vnd.ms-excel";

            OutputStream.Write(ms.ToArray(), 0, ms.ToArray().Length);

            return;
        }