Ejemplo n.º 1
0
        public void Test49381()
        {
            IWorkbook wb             = _testDataProvider.CreateWorkbook();
            int       colSplit       = 1;
            int       rowSplit       = 2;
            int       leftmostColumn = 3;
            int       topRow         = 4;

            ISheet s = wb.CreateSheet();

            // Populate
            for (int rn = 0; rn <= topRow; rn++)
            {
                IRow r = s.CreateRow(rn);
                for (int cn = 0; cn < leftmostColumn; cn++)
                {
                    ICell c = r.CreateCell(cn, CellType.Numeric);
                    c.SetCellValue(100 * rn + cn);
                }
            }

            // Create the Freeze Pane
            s.CreateFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
            PaneInformation paneInfo = s.PaneInformation;

            // Check it
            Assert.AreEqual(colSplit, paneInfo.VerticalSplitPosition);
            Assert.AreEqual(rowSplit, paneInfo.HorizontalSplitPosition);
            Assert.AreEqual(leftmostColumn, paneInfo.VerticalSplitLeftColumn);
            Assert.AreEqual(topRow, paneInfo.HorizontalSplitTopRow);


            // Now a row only freezepane
            s.CreateFreezePane(0, 3);
            paneInfo = s.PaneInformation;

            Assert.AreEqual(0, paneInfo.VerticalSplitPosition);
            Assert.AreEqual(3, paneInfo.HorizontalSplitPosition);
            Assert.AreEqual(0, paneInfo.VerticalSplitLeftColumn);
            Assert.AreEqual(3, paneInfo.HorizontalSplitTopRow);

            // Now a column only freezepane
            s.CreateFreezePane(4, 0);
            paneInfo = s.PaneInformation;

            Assert.AreEqual(4, paneInfo.VerticalSplitPosition);
            Assert.AreEqual(0, paneInfo.HorizontalSplitPosition);
            Assert.AreEqual(4, paneInfo.VerticalSplitLeftColumn);
            Assert.AreEqual(0, paneInfo.HorizontalSplitTopRow);
        }
Ejemplo n.º 2
0
        private void addTablaArchivosFirmados(ref int rowNumber, List <FileProcessing> data, ISheet sheet)
        {
            var row = sheet.CreateRow(rowNumber);

            sheet.CreateFreezePane(0, rowNumber + 1);

            var cell = row.CreateCell(0);

            AddHeaderCell(row, 0, "Lote", widthInChars(25));
            AddHeaderCell(row, 1, "Archivo", widthInChars(55));
            AddHeaderCell(row, 2, "Estado", widthInChars(12));
            AddHeaderCell(row, 3, "Descripcion", widthInChars(32));
            AddHeaderCell(row, 4, "Path completo", widthInChars(92));
            rowNumber++;
            var  firstRow  = rowNumber;
            bool alternate = false;

            foreach (var pp in data)
            {
                row = sheet.CreateRow(rowNumber);
                addRow(row, alternate, pp);
                rowNumber++;
                alternate = !alternate;
            }
        }
Ejemplo n.º 3
0
        void ExportExecute()
        {
            rows = DataGrid.Rows.Where(x => x.Type == DataGridRowType.Group || x.Type == DataGridRowType.Item).ToList();
            if (rows.Count == 0)
            {
                return;
            }

            IWorkbook workbook = new XSSFWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            sheet.CreateFreezePane(0, 2);
            if (DataGrid.FlowDirection == FlowDirection.RightToLeft)
            {
                sheet.IsRightToLeft = true;
            }

            for (int i = 0; i <= DataGrid.GroupedColumns.Count(); i++)
            {
                sheet.SetColumnWidth(i, 1024);
            }

            PrepareStyle(workbook);

            CreateExcelHeader(sheet);

            worker         = new BackgroundWorker();
            worker.DoWork += CreateExcelItem;
            worker.WorkerReportsProgress      = true;
            worker.WorkerSupportsCancellation = true;
            worker.ProgressChanged           += worker_ProgressChanged;
            worker.RunWorkerAsync(sheet);
        }
Ejemplo n.º 4
0
        //Action action;

        public void DatatableToExcel(DataTable table, string path)
        {
            if (table == null || string.IsNullOrEmpty(path))
            {
                MessageBox.Show(ReadConfigXml("alarm08"));
                return;
            }
            IWorkbook workbook = null;

            if (Path.GetExtension(path).ToLower() == ".xls")
            {
                workbook = new HSSFWorkbook();
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                workbook = new XSSFWorkbook();
            }
            else
            {
                MessageBox.Show(path);
            }
            ISheet sheet = workbook.CreateSheet("sheet1");

            sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            sheet.CreateFreezePane(table.Columns.Count, 1);
            IRow cells = sheet.CreateRow(0);

            for (int i = 0; i < table.Columns.Count; i++)
            {
                sheet.SetColumnWidth(i, 20 * 256);
                cells.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
            }
            for (int i = 0; i < table.Rows.Count; i++)
            {
                cells = sheet.CreateRow(i + 1);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    cells.CreateCell(j).SetCellValue(table.Rows[i][j].ToString().Trim());
                }
            }
            FileStream fs;

            try
            {
                // File.Delete(path);
                fs = File.Create(path);
                fs.Close();
                fs = new FileStream(path, FileMode.Open, FileAccess.Write);
                workbook.Write(fs);
                //fs.Flush();
                fs.Close();
                workbook.Close();
                //action = new Action(SimpleIoc.Default.GetInstance<EncodingViewModel>().SaveSuccess);
                //action.Invoke();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
Ejemplo n.º 5
0
        public void DownLoadExcelTemplate()
        {
            string filename = "test.xlsx";

            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));

            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet       sheet1   = workbook.CreateSheet("Sheet1");

            //冻结第一行
            sheet1.CreateFreezePane(0, 1, 0, 1);
            //创建表头
            //sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
            int x = 1;

            for (int i = 0; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                for (int j = 0; j < 15; j++)
                {
                    row.CreateCell(j).SetCellValue(x++);
                }
            }
            using (var f = System.IO.File.Create(@"d:\test.xlsx"))
            {
                workbook.Write(f);
            }
            Response.WriteFile(@"d:\test.xlsx");
            Response.Flush();
            Response.End();
        }
Ejemplo n.º 6
0
        private Collection <TotalsColumn> CreateHeader(ISheet worksheet, Type type, IList <PropertyInfo> properties, StylingOptions stylingOptions,
                                                       Dictionary <int, short> columnFormatters, Dictionary <string, ICellStyle> styleCache)
        {
            var headerRowColorId  = GetXlColor(stylingOptions.HeaderRowColor);
            var headerFontColorId = GetXlColor(stylingOptions.HeaderFontColor);
            var headerCellStyle   = GetCellStyle(0, headerFontColorId, stylingOptions.HeaderFontSize, styleCache, true, headerRowColorId, HorizontalAlignment.Center);
            var headerRow         = worksheet.CreateRow(0);
            var totalsColumns     = new Collection <TotalsColumn>();

            for (var columnIndex = 0; columnIndex < properties.Count; columnIndex++)
            {
                var displayNameAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(DisplayNameAttribute)) as DisplayNameAttribute;
                headerRow.CreateCell(columnIndex).SetCellValue(displayNameAttribute == null ? properties[columnIndex].Name : displayNameAttribute.DisplayName);
                headerRow.Cells[columnIndex].CellStyle = headerCellStyle;

                var formatAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(FormatAttribute)) as FormatAttribute;
                var format          = GetFormatId(formatAttribute);
                columnFormatters.Add(columnIndex, format);

                var totalsAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(FormulaAttribute)) as FormulaAttribute;
                if (totalsAttribute != null)
                {
                    totalsColumns.Add(new TotalsColumn
                    {
                        ColumnIndex = columnIndex,
                        Format      = format,
                        TotalType   = totalsAttribute.FormulaType
                    });
                }
            }

            worksheet.CreateFreezePane(0, 1);

            return(totalsColumns);
        }
Ejemplo n.º 7
0
        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet    sheet1   = workbook.CreateSheet("new sheet");
            ISheet    sheet2   = workbook.CreateSheet("second sheet");
            ISheet    sheet3   = workbook.CreateSheet("third sheet");
            ISheet    sheet4   = workbook.CreateSheet("fourth sheet");

            // Freeze just one row
            sheet1.CreateFreezePane(0, 1, 0, 1);

            // Freeze just one column
            sheet2.CreateFreezePane(1, 0, 1, 0);

            // Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
            sheet3.CreateFreezePane(2, 2);

            // Create a split with the lower left side being the active quadrant
            sheet4.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);

            FileStream sw = File.Create("test.xlsx");

            workbook.Write(sw);
            sw.Close();
        }
Ejemplo n.º 8
0
        /// <summary>
        /// Export2s the excel.
        /// </summary>
        /// <param name="contentDics">The content dics.</param>
        /// <param name="sheetColumnDics">The sheet column dics.</param>
        /// <param name="filename">The filename.</param>
        /// <returns></returns>
        public static bool ExportToExcel(Dictionary <string, Dictionary <string, List <string> > > contentDics, Dictionary <string, List <string> > sheetColumnDics, string filename)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                foreach (KeyValuePair <string, Dictionary <string, List <string> > > keyValuePair in contentDics)
                {
                    ISheet     sheet = workbook.CreateSheet(keyValuePair.Key);
                    ICellStyle style = workbook.CreateCellStyle();

                    style.Alignment         = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    sheet.CreateFreezePane(0, 1, 0, 1);

                    IRow rowHead = sheet.CreateRow(0);

                    ICell cell = rowHead.CreateCell(0, CellType.String);
                    cell.SetCellValue("时间");
                    cell.CellStyle = style;

                    int y = 1;
                    for (int x = 0; x < sheetColumnDics[keyValuePair.Key].Count; x++)
                    {
                        cell = rowHead.CreateCell(x + 1, CellType.String);
                        cell.SetCellValue(sheetColumnDics[keyValuePair.Key][x]);
                        cell.CellStyle = style;
                    }
                    Dictionary <string, List <string> > data = keyValuePair.Value;

                    foreach (KeyValuePair <string, List <string> > subKeyValuePair in data)
                    {
                        IRow row = sheet.CreateRow(y++);
                        cell = row.CreateCell(0, CellType.String);
                        cell.SetCellValue(subKeyValuePair.Key);
                        cell.CellStyle = style;

                        for (int index = 0; index < subKeyValuePair.Value.Count; index++)
                        {
                            string p = subKeyValuePair.Value[index];
                            cell = row.CreateCell(index + 1, CellType.String);
                            cell.SetCellValue(p);
                            cell.CellStyle = style;
                        }
                    }
                }

                using (FileStream stream = File.Create(filename))
                {
                    workbook.Write(stream);
                    stream.Close();
                }
                return(true);
            }
            catch
            {
                throw;
            }
        }
        public byte[] WriteExcel(IEnumerable data, string[] columns)
        {
            MemoryStream output     = new MemoryStream();
            HSSFWorkbook workbook   = new HSSFWorkbook();
            ISheet       sheet      = workbook.CreateSheet();
            IFont        headerFont = workbook.CreateFont();

            headerFont.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle headerStyle = workbook.CreateCellStyle();

            headerStyle.SetFont(headerFont);
            headerStyle.Alignment = HorizontalAlignment.Center;

            //(Optional) freeze the header row so it is not scrolled
            sheet.CreateFreezePane(0, 1, 0, 1);

            IEnumerator foo = data.GetEnumerator();

            foo.MoveNext();
            Type t = foo.Current.GetType();

            IRow header = sheet.CreateRow(0);

            PropertyInfo[] properties = t.GetProperties();
            int            colIndex   = 0;

            for (int i = 0; i < properties.Length; i++)
            {
                if (columns.Contains(properties[i].Name))
                {
                    ICell cell = header.CreateCell(colIndex);
                    cell.CellStyle = headerStyle;
                    cell.SetCellValue(properties[i].Name);
                    colIndex++;
                }
            }

            int rowIndex = 0;

            foreach (object o in data)
            {
                colIndex = 0;
                IRow row = sheet.CreateRow(rowIndex + 1);
                for (int i = 0; i < properties.Length; i++)
                {
                    if (columns.Contains(properties[i].Name))
                    {
                        row.CreateCell(colIndex).SetCellValue(properties[i].GetValue(o, null).ToString());
                        colIndex++;
                    }
                }
                rowIndex++;
            }

            workbook.Write(output);
            return(output.ToArray());
        }
Ejemplo n.º 10
0
        private void SetUpHeader(ISheet sheet, ClassMapping classMapping)
        {
            var workbook = sheet.Workbook;
            var row      = sheet.GetRow(0) ?? sheet.CreateRow(0);

            InitMappingColumns(classMapping);
            CacheColumnIndices(classMapping);

            //freeze header row
            sheet.CreateFreezePane(0, 1);

            //create header and column style for every mapped column
            foreach (var mapping in classMapping.PropertyMappings)
            {
                var cellIndex = CellReference.ConvertColStringToIndex(mapping.Column);
                var cell      = row.GetCell(cellIndex) ?? row.CreateCell(cellIndex);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(mapping.Header);
                cell.CellStyle = GetStyle(DataStatus.Header, workbook);

                //set default column style if not defined but available
                var style = GetStyle(mapping.Status, workbook);
                if (mapping.Status == DataStatus.None)
                {
                    continue;
                }
                var existStyle = sheet.GetColumnStyle(cellIndex);
                if (
                    existStyle != null &&
                    existStyle.FillForegroundColor == style.FillForegroundColor &&
                    existStyle.BorderTop == style.BorderTop &&
                    existStyle.TopBorderColor == style.TopBorderColor
                    )
                {
                    continue;
                }

                //create new style
                sheet.SetDefaultColumnStyle(cellIndex, style);
                sheet.SetColumnHidden(cellIndex, false);
                //set default width
                sheet.SetColumnWidth(cellIndex, 256 * 15);
                //hide if defined
                if (mapping.Hidden)
                {
                    sheet.SetColumnHidden(cellIndex, true);
                }
            }

            //set up filter
            var lastPropMap = classMapping.PropertyMappings.OrderBy(p => p.ColumnIndex).LastOrDefault();

            if (lastPropMap != null)
            {
                sheet.SetAutoFilter(new CellRangeAddress(0, 1, 0, lastPropMap.ColumnIndex));
            }
        }
Ejemplo n.º 11
0
        /// <summary>
        /// 将List转换成excel
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="tableHeaders">表头 k--属性 v--属性显示名称</param>
        /// <param name="sheet">sheet名称</param>
        /// <returns>文件流</returns>
        public static byte[] ExportDataListToExcel <T>(List <T> list, Dictionary <string, string> tableHeaders, string sheetPrefix) where T : class, new()
        {
            HSSFWorkbook hssWorkBook = new HSSFWorkbook();  // 创建工作薄
            int          sheetNums   = (int)Math.Ceiling(list.Count / 50000.00);

            for (int i = 0; i < sheetNums; i++)
            {
                ISheet sheet = hssWorkBook.CreateSheet(sheetPrefix + (i + 1));    // 创建sheet表
                // 创建表头
                IRow rowHead = sheet.CreateRow(0);
                int  rowId   = 0;
                foreach (KeyValuePair <string, string> kv in tableHeaders)
                {
                    ICell cell = rowHead.CreateCell(rowId);
                    cell.SetCellValue(kv.Value);
                    // 设置样式 加粗居中
                    HSSFCellStyle cellStyle = (HSSFCellStyle)hssWorkBook.CreateCellStyle();
                    HSSFFont      font      = (HSSFFont)hssWorkBook.CreateFont();
                    font.Boldweight = 600;
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cell.CellStyle      = cellStyle;
                    rowId++;
                }
                rowId = 1;
                // 创建数据行
                PropertyInfo[] properties = typeof(T).GetProperties();
                // 计算每个sheet中记录数
                var pagedList = list.Skip(i * 50000).Take(50000).ToList();
                foreach (T item in pagedList)
                {
                    IRow rowBody   = sheet.CreateRow(rowId);
                    int  cellIndex = 0;
                    foreach (KeyValuePair <string, string> kv in tableHeaders)
                    {
                        foreach (PropertyInfo property in properties)
                        {
                            if (string.Equals(property.Name, kv.Key, StringComparison.CurrentCultureIgnoreCase))
                            {
                                ICell cell = rowBody.CreateCell(cellIndex);
                                FillCellValue(cell, property.GetValue(item, null));
                                break;
                            }
                        }
                        cellIndex++;
                    }
                    rowId++;
                }
                // 冻结首行
                sheet.CreateFreezePane(0, 1);
            }
            MemoryStream file = new MemoryStream();

            hssWorkBook.Write(file);
            return(file.GetBuffer());
        }
Ejemplo n.º 12
0
        /// <summary>
        /// 冻结窗口
        /// </summary>
        /// <param name="sheet">Sheet对象</param>
        /// <param name="rowIndex">行,默认值0表示不冻结行</param>
        /// <param name="colIndex">列,默认值0表示不冻结列</param>
        public static void Freeze(ISheet sheet, int rowIndex = 0, int colIndex = 0)
        {
            rowIndex = rowIndex < 0 ? 0 : rowIndex;
            colIndex = colIndex < 0 ? 0 : colIndex;

            if (rowIndex > 0 || colIndex > 0)
            {
                sheet.CreateFreezePane(colIndex, rowIndex);
            }
        }
Ejemplo n.º 13
0
        /// <summary>
        /// 冻结行,FreezeRow为要冻结的行
        /// </summary>
        /// <param name="Sheetindex"></param>
        public void SplitAndFreezePanes(int Sheetindex, int FreezeRow)
        {
            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex);

            // Freeze just one row
            sheet1.CreateFreezePane(0, FreezeRow);

            WriteToFile();
        }
Ejemplo n.º 14
0
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table)
        {
            FileStream fs       = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook  workBook = new HSSFWorkbook();

            this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
            ISheet sheet = workBook.CreateSheet(this._sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);

            row.CreateCell(0).SetCellValue(this._title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;
            sheet.CreateFreezePane(2, 0, 5, 0);
            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont      font      = workBook.CreateFont();

            font.FontName           = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment         = HorizontalAlignment.Center;
            row.Cells[0].CellStyle      = cellStyle;

            //处理表格列头
            row = sheet.CreateRow(1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row        = sheet.CreateRow(2 + i);
                row.Height = 250 * 5;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).CellStyle.WrapText = true;
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 8);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return(true);
        }
Ejemplo n.º 15
0
        public void MakeSheet(IEnumerable <DataRow> rows)
        {
            ISheet sh1  = WBC.WB.CreateSheet(SheetName);
            var    row0 = sh1.CreateRow(0);

            for (int i = 0; i < FieldsConfig.Count; i++)
            {
                var fc   = FieldsConfig[i];
                var cell = row0.CreateCell(i);
                cell.CellStyle = WBC.CellStyleCations;
                cell.SetCellValue(FieldsConfig[i].Caption);
                sh1.SetColumnWidth(i, fc.WidthInChars * 256);

                if (fc.CellStyle != null)
                {
                    sh1.SetDefaultColumnStyle(i, fc.CellStyle);
                }
            }


            int ct = 1;

            foreach (var dr in rows)
            {
                var row = sh1.CreateRow(ct);
                ct++;
                for (int i = 0; i < FieldsConfig.Count; i++)
                {
                    var    fc    = FieldsConfig[i];
                    object value = null;
                    if (fc.FieldHelper == null)
                    {
                        value = dr[fc.FieldName];
                    }
                    else
                    {
                        value = fc.FieldHelper.GetFieldValue(fc.FieldName, dr);
                    }

                    var cell = row.CreateCell(i);

                    SetCellValue(cell, fc, value);
                }
                WBC.RowsDone++;
                WBC.OnProgress();
                if (WBC.Cancel)
                {
                    return;
                }
            }

            sh1.CreateFreezePane(0, 1);
        }
Ejemplo n.º 16
0
        /// <summary>
        /// 导出一个面签的数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="data"></param>
        public void ExportSheet(ISheet sheet, Sheet data)
        {
            // 输出标题列
            var head     = sheet.CreateRow(0);
            var headFont = CreateFont();

            headFont.FontName           = "宋体";
            headFont.FontHeightInPoints = 11;
            headFont.Boldweight         = 600;
            var headCellStyle = CreateCellStyle();

            headCellStyle.CloneStyleFrom(defaultCellStyle);
            headCellStyle.Alignment         = HorizontalAlignment.Center;
            headCellStyle.VerticalAlignment = VerticalAlignment.Center;
            headCellStyle.SetFont(headFont);
            var columns = data.Columns;

            for (int i = 0; i < columns.Length; i++)
            {
                var column = columns[i];
                SetColumnDefaultStyle(sheet, i, column);
                var cell = head.CreateCell(i);
                cell.SetCellValue(column.ColumnName);
                cell.CellStyle = headCellStyle;
            }

            // 输出行
            var rowNumber = 1;
            var rowWriter = new ExcelRowWriter(columns);

            foreach (var rowValues in data.Rows)
            {
                var row = sheet.CreateRow(rowNumber++);
                rowWriter.Write(row, rowValues);
                if (rowNumber == 100) // 采样到100时自适应大小
                {
                    for (int i = 0; i < columns.Length; i++)
                    {
                        sheet.AutoSizeColumn(i);
                        sheet.SetColumnWidth(i, Math.Min(sheet.GetColumnWidth(i) + 1000, 65280));
                    }
                }
            }

            // 自适应列宽,弥补小于100行的情况
            for (int i = 0; rowNumber < 100 && i < columns.Length; i++)
            {
                sheet.AutoSizeColumn(i);
                sheet.SetColumnWidth(i, Math.Min(sheet.GetColumnWidth(i) + 1000, 65280));
            }
            sheet.CreateFreezePane(0, 1);
        }
Ejemplo n.º 17
0
        private static IWorkbook CrearLibro <T>(IEnumerable <T> datos, TipoExportacion tipoExportacion, string grupo)
        {
            //Inicializamos el archivo excel
            IWorkbook workbook = new HSSFWorkbook();
            ISheet    sheet    = workbook.CreateSheet("DatosExportados");

            //Inmoviliza la cabecera del reporte
            sheet.CreateFreezePane(0, 1, 0, 1);

            workbook = AgregarDatos <T>(workbook, datos, tipoExportacion, grupo);

            return(workbook);
        }
Ejemplo n.º 18
0
        protected override void Resize(int headerRows, int headerColumns, int rows, int columns)
        {
            _headerRows    = headerRows;
            _headerColumns = headerColumns;
            _sheet.CreateFreezePane(headerColumns, headerRows);

            for (int row = 0; row < headerRows; row++)
            {
                for (int column = 0; column < headerColumns; column++)
                {
                    SetHeader(row, column, "", 1, 1);
                }
            }
        }
Ejemplo n.º 19
0
        public ISheet DataTableToSheet([NotNull] ISheet sheet, DataTable dataTable, int sheetIndex)
        {
            if (null == dataTable || dataTable.Rows.Count == 0 || dataTable.Columns.Count == 0 || _propertyColumnDictionary.Keys.Count == 0)
            {
                return(sheet);
            }
            var sheetSetting = sheetIndex >= 0 && sheetIndex < _sheetSettings.Count ? _sheetSettings[sheetIndex] : _sheetSettings[0];

            if (sheetSetting.HeaderRowIndex >= 0)
            {
                var headerRow = sheet.CreateRow(sheetSetting.HeaderRowIndex);
                for (var i = 0; i < dataTable.Columns.Count; i++)
                {
                    var col = _propertyColumnDictionary.GetPropertySettingByPropertyName(dataTable.Columns[i].ColumnName);
                    if (null != col)
                    {
                        headerRow.CreateCell(col.ColumnIndex).SetCellValue(col.ColumnTitle);
                    }
                }
            }

            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var row = sheet.CreateRow(sheetSetting.StartRowIndex + i);
                for (var j = 0; j < dataTable.Columns.Count; j++)
                {
                    var col = _propertyColumnDictionary.GetPropertySettingByPropertyName(dataTable.Columns[j].ColumnName);
                    row.CreateCell(col.ColumnIndex).SetCellValue(dataTable.Rows[i][j], col.ColumnFormatter);
                }
            }

            // autosizecolumn
            foreach (var setting in _propertyColumnDictionary.Values)
            {
                sheet.AutoSizeColumn(setting.ColumnIndex);
            }

            foreach (var freezeSetting in _excelConfiguration.FreezeSettings)
            {
                sheet.CreateFreezePane(freezeSetting.ColSplit, freezeSetting.RowSplit, freezeSetting.LeftMostColumn, freezeSetting.TopRow);
            }

            if (_excelConfiguration.FilterSetting != null)
            {
                var headerIndex = sheetSetting.HeaderRowIndex >= 0 ? sheetSetting.HeaderRowIndex : 0;
                sheet.SetAutoFilter(new CellRangeAddress(headerIndex, dataTable.Rows.Count + headerIndex, _excelConfiguration.FilterSetting.FirstColumn, _excelConfiguration.FilterSetting.LastColumn ?? _propertyColumnDictionary.Values.Max(_ => _.ColumnIndex)));
            }

            return(sheet);
        }
Ejemplo n.º 20
0
    /// <summary>
    ///  DataTable转换成Excel文档流
    /// </summary>
    /// <param name="table"></param>
    /// <param name="clomunDic">列头字典,Key是列的索引,value是列名称</param>
    /// <returns></returns>
    public static MemoryStream RenderToExcel(DataTable table, Dictionary <string, string> headerDic)
    {
        MemoryStream ms = new MemoryStream();

        using (table)
        {
            using (IWorkbook workbook = new HSSFWorkbook())
            {
                using (ISheet sheet = workbook.CreateSheet())
                {
                    IRow headerRow = sheet.CreateRow(0);
                    int  index     = 0;
                    foreach (var item in headerDic)
                    {
                        headerRow.CreateCell(index).SetCellValue(item.Value);//If Caption not set, returns the ColumnName value
                        index++;
                    }
                    //第一个参数表示要冻结的列数;
                    //第二个参数表示要冻结的行数,这里只冻结列所以为0;
                    //第三个参数表示右边区域可见的首列序号,从1开始计算;
                    //第四个参数表示下边区域可见的首行序号,也是从1开始计算,这里是冻结列,所以为0;
                    //冻结首行
                    sheet.CreateFreezePane(0, 1, 0, 1);

                    // handling value.
                    int rowIndex = 1;
                    List <KeyValuePair <string, string> > list = headerDic.ToList();
                    foreach (DataRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);

                        foreach (DataColumn column in table.Columns)
                        {
                            if (headerDic.Keys.Contains(column.ColumnName))
                            {
                                int cloumnIndex = list.FindIndex(t => t.Key == column.ColumnName);
                                dataRow.CreateCell(cloumnIndex).SetCellValue(row[column.ColumnName].ToString());
                            }
                        }
                        rowIndex++;
                    }
                    AutoSizeColumns(sheet);
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                }
            }
        }
        return(ms);
    }
Ejemplo n.º 21
0
        /// <summary>
        /// Export2s the excel.
        /// </summary>
        /// <param name="contentDic">The content dictionary.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <param name="filename">The filename.</param>
        /// <returns></returns>
        public static bool ExportToExcel(Dictionary <string, string> contentDic, string columnName, string sheetName, string filename)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet       sheet    = workbook.CreateSheet(sheetName);
                ICellStyle   style    = workbook.CreateCellStyle();

                style.Alignment         = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                sheet.CreateFreezePane(0, 1, 0, 1);

                //填写表头
                IRow  rowHead = sheet.CreateRow(0);
                ICell cell    = rowHead.CreateCell(0, CellType.String);
                cell.SetCellValue("时间");
                cell.CellStyle = style;

                cell = rowHead.CreateCell(1, CellType.String);
                cell.SetCellValue(columnName);
                cell.CellStyle = style;

                //填写内容
                int y = 1;
                foreach (KeyValuePair <string, string> keyValuePair in contentDic)
                {
                    IRow row = sheet.CreateRow(y++);
                    cell = row.CreateCell(0, CellType.String);
                    cell.SetCellValue(keyValuePair.Key);
                    cell.CellStyle = style;

                    cell = row.CreateCell(1, CellType.String);
                    cell.SetCellValue(keyValuePair.Value);
                    cell.CellStyle = style;
                }

                using (FileStream stream = File.Create(filename))
                {
                    workbook.Write(stream);
                    stream.Close();
                }
                return(true);
            }
            catch
            {
                throw;
            }
        }
Ejemplo n.º 22
0
        /// <summary>
        /// datatable导出到excel,指定表头信息
        /// </summary>
        /// <param name="HeaderText">excel表头信息,如:{"编号","名称"}</param>
        /// <param name="HeaderField">excel对应的DataTable中字段名称,如{"ID","Name"}</param>
        /// <param name="SourceTable">DataTable数据源</param>
        /// <returns></returns>
        private static MemoryStream Export(string[] HeaderText, string[] HeaderField, DataTable SourceTable)
        {
            if (SourceTable == null)
            {
                return(null);
            }

            MemoryStream stream    = new MemoryStream();
            HSSFWorkbook workbook  = new HSSFWorkbook();
            string       sheetName = String.IsNullOrEmpty(SourceTable.TableName) ? "Sheet1" : SourceTable.TableName;
            ISheet       sheet     = workbook.CreateSheet(sheetName);

            //固定表头
            sheet.CreateFreezePane(0, 1);
            //导出excel表头;
            IRow hRow = sheet.CreateRow(0);

            hRow.HeightInPoints = 25;
            ICellStyle headStyle = workbook.CreateCellStyle();

            headStyle.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();

            font.FontHeightInPoints = 10;
            font.Boldweight         = 700;
            headStyle.SetFont(font);
            //headStyle.FillBackgroundColor =See getFillForegroundColor();
            for (int m = 0; m < HeaderText.Length; m++)
            {
                hRow.CreateCell(m).SetCellValue(HeaderText[m]);
                hRow.GetCell(m).CellStyle = headStyle;
            }

            for (int i = 0; i < SourceTable.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < HeaderField.Length; j++)
                {
                    row.CreateCell(j).SetCellValue(SourceTable.Rows[i][HeaderField[j]].ToString());
                }
            }
            workbook.Write(stream);
            sheet    = null;
            workbook = null;
            stream.Flush();
            stream.Position = 0L;
            return(stream);
        }
Ejemplo n.º 23
0
        public static void DataSetToExcel(DataSet ds, string filename)
        {
            #region 将DATASET中文件,生成Excel代码

            //导出Excel代码
            //创建Workbook
            IWorkbook wk = new HSSFWorkbook();
            //创建Sheet
            foreach (DataTable dtl in ds.Tables)
            {
                //设置Excel第一行名称
                ISheet sheet = wk.CreateSheet(dtl.TableName);
                IRow   row0  = sheet.CreateRow(0);
                for (int i = 0; i < dtl.Columns.Count; i++)
                {
                    row0.CreateCell(i).SetCellValue(dtl.Columns[i].ColumnName);
                }
                int rowIndex = 1;
                foreach (DataRow dtdr in dtl.Rows)
                {
                    IRow row = sheet.CreateRow(rowIndex);
                    foreach (DataColumn dtdc in dtl.Columns)
                    {
                        for (int i = 0; i < dtl.Columns.Count; i++)
                        {
                            row.CreateCell(i).SetCellValue(dtdr[i].ToString());
                        }
                    }
                    rowIndex++;
                }

                ////第一行自动筛选
                //CellRangeAddress c = new CellRangeAddress(1, 65535, 0,9);
                //sheet.SetAutoFilter(c);

                //冻结窗口
                sheet.CreateFreezePane(0, 1);
                // 把数据写入到磁盘上
                using (FileStream fs = File.OpenWrite(filename + ".xls"))
                {
                    wk.Write(fs);
                }
            }

            //MessageBox.Show("PTN配置数据已成功导出至程序目录下:" + filename + "中,请查看。");
            #endregion
        }
Ejemplo n.º 24
0
        private int MSWriteToSheet2(Dictionary <int, SelfNPOICell[]> data, ISheet sheet)
        {
            if (data == null || data.Count == 0)
            {
                return(-1);
            }

            ICellStyle backcolor = this.setCellBackColor();
            ICellStyle fontBold  = this.setFontBold();
            ICellStyle wrapText  = this.setWrapText();

            foreach (var r in data)
            {
                IRow row            = sheet.CreateRow(r.Key);
                bool isSetBackColor = false;
                if (r.Key > 0 && r.Value[0].HasBackColor)
                {
                    isSetBackColor = true;
                }
                for (int col = 0; col < r.Value.Count(); col++)
                {
                    ICell cell = row.CreateCell(col);
                    cell.SetCellValue(r.Value[col].CellValue);
                    if (r.Value[col].IsFontBold)
                    {
                        cell.CellStyle = fontBold;
                    }
                    if (isSetBackColor)
                    {
                        cell.CellStyle = backcolor;
                    }
                    else if (col == 4)
                    {
                        cell.CellStyle = setWrapText();
                    }
                }
            }
            //设置合并单元格的长度,需全部显示文字
            sheet.SetColumnWidth(0, 24 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(4, 60 * 256);
            sheet.SetColumnWidth(8, 15 * 256);
            sheet.CreateFreezePane(0, 1, 0, 1);

            return(0);
        }
Ejemplo n.º 25
0
        private static void CreateHeader(HSSFWorkbook workbook, ISheet sheet, int rowId)
        {
            IRow rowHeader = sheet.CreateRow(rowId);

            rowHeader.Height = 40 * 20;
            ICellStyle style = CreateTableStyle(workbook, true, false);

            CreateApplyStyleToCell(rowHeader, 0, style, "Название");
            CreateApplyStyleToCell(rowHeader, 1, style, "Артикул");
            CreateApplyStyleToCell(rowHeader, 2, style, "Кол-во");
            CreateApplyStyleToCell(rowHeader, 3, style, "Зак. цена");
            CreateApplyStyleToCell(rowHeader, 4, style, "Цена");
            CreateApplyStyleToCell(rowHeader, 5, style, "Зак. сумма");
            CreateApplyStyleToCell(rowHeader, 6, style, "Сумма");

            sheet.CreateFreezePane(0, rowId + 1);
        }
Ejemplo n.º 26
0
        public void Export(Stream outStram, int year)
        {
            wb = new XSSFWorkbook();
            exportStyleManager = new ExportStyleManager(wb);

            this.employes = employes.OrderBy(e => e.Surname).ThenBy(e => e.Name).ToList();

            ISheet sheet = CreateSheet(year);

            int  i         = 1;
            int  mergeSize = 1;
            IRow headerRow = sheet.CreateRow(0);

            headerRow.HeightInPoints = 30;

            IRow dayNameRow = sheet.CreateRow(1);

            IRow dayNumberRow = sheet.CreateRow(2);

            List <IRow> employesLeavesRows = CreateEmployeLeavesRows(sheet);

            for (int month = 1; month <= 12; month++)
            {
                DateTime calendar    = new DateTime(year, month, 1);
                int      daysInMonth = DateTime.DaysInMonth(year, month);


                CreateMonthHeader(year, sheet, headerRow, mergeSize, month, daysInMonth);
                mergeSize += daysInMonth;
                for (; i < mergeSize; i++)
                {
                    int weekDay = (int)calendar.DayOfWeek;
                    sheet.SetColumnWidth(i, 4 * 256);
                    CreateDayColumn(calendar, dayNameRow, dayNumberRow, employesLeavesRows, weekDay, i, mergeSize);
                    calendar = calendar.AddDays(1);
                }
                i = mergeSize;
            }

            CreateTotalColumn(sheet, employesLeavesRows, dayNameRow, dayNumberRow, i);
            sheet.CreateFreezePane(1, 3);
            sheet.SetActiveCell(3, 0);

            wb.Write(outStram);
            wb.Close();
        }
Ejemplo n.º 27
0
        protected void FillComponentSheet(ISheet sheet, CODModel model, Database db)
        {
            BuildRow(sheet, HeadStyle, GetHeadValues());

            sheet.CreateFreezePane(0, 1, 0, 1);

            long line       = 0;
            var  totalLines = model.Renderings.Count;

            var d = new Dictionary <Guid, Item>();

            foreach (var a in model.Renderings)
            {
                d.Add(a.Key, a.Value);
            }
            foreach (var b in model.Sublayouts)
            {
                d.Add(b.Key, b.Value);
            }
            foreach (KeyValuePair <Guid, Item> itm in d)
            {
                line++;

                BuildRow(sheet, GreenStyle, GetTemplateValues($"{itm.Value.Name} ({itm.Value.TemplateName})", "Component"));

                var rpItem = GetItemByFieldId(db, itm.Value, RenderingParameterField);
                var dsItem = GetItemByFieldPath(db, itm.Value, DatasourceTemplateField);
                if (dsItem == null && rpItem == null)
                {
                    BuildRow(sheet, DefaultStyle, GetEmptyValues());
                    continue;
                }

                if (dsItem != null)
                {
                    EnumerateTemplateFields(sheet, db, dsItem, "Datasource");
                }
                if (rpItem != null)
                {
                    EnumerateTemplateFields(sheet, db, rpItem, "Rendering Parameters");
                }

                JobService.SetJobMessage($"Processed component item {line} of {totalLines}");
            }
        }
Ejemplo n.º 28
0
        public void WriteExcel2003(string path, List <Dictionary <string, string> > data)
        {
            using (FileStream fs = new FileStream(path, FileMode.Create))
            {
                HSSFWorkbook workbook = new HSSFWorkbook();

                ISheet sheet = workbook.CreateSheet("sheet1");
                sheet.CreateFreezePane(0, 1);

                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 15f;

                ICellStyle headerStyle = workbook.CreateCellStyle();
                headerStyle.Alignment         = HorizontalAlignment.Center;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;

                IFont headerFont = workbook.CreateFont();
                headerFont.IsBold             = true;
                headerFont.FontHeightInPoints = 12;
                headerFont.FontName           = "宋体";

                headerStyle.SetFont(headerFont);

                var dic = data.ElementAt(0);
                for (int i = 0; i < dic.Count; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.CellStyle = headerStyle;
                    cell.SetCellValue(dic.ElementAt(i).Key);
                }

                for (int i = 0; i < data.Count; i++)
                {
                    var  item = data.ElementAt(i);
                    IRow row  = sheet.CreateRow(i + 1);
                    for (int j = 0; j < item.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue(item.ElementAt(j).Value);
                    }
                }

                workbook.Write(fs);
            }
        }
Ejemplo n.º 29
0
        /// <summary>
        /// 创建Sheet及列头
        /// </summary>
        private ISheet CreateSheetWithHeader(IWorkbook workbook, ColumnInfo[] cols, int sheetIndex)
        {
            ISheet sheet = workbook.CreateSheet("第 " + sheetIndex + " 页");

            //冻结首行首列
            sheet.CreateFreezePane(0, 1);

            IRow header = sheet.CreateRow(0);

            for (int i = 0; i < cols.Length; i++)
            {
                ICell cell = header.CreateCell(i);
                cell.SetCellValue(cols[i].DisplayName);
                //自适应宽度
                sheet.AutoSizeColumn(i);
            }
            return(sheet);
        }
Ejemplo n.º 30
0
        private void BuildTitle()
        {
            var   row = _sheet.CreateRow(0);
            ICell cell;

            row.HeightInPoints = TitleRowHight;

            for (int i = 0; i < Columns.Count; i++)
            {
                cell = row.CreateCell(i, CellType.String);
                cell.SetCellValue(Columns[i].Name);
                cell.SetCellType(CellType.String);
            }

            if (FreezeTitle)
            {
                _sheet.CreateFreezePane(0, 1);
            }
        }
Ejemplo n.º 31
0
        private static void CreateHeader(HSSFWorkbook workbook, ISheet sheet, int rowId)
        {
            IRow rowHeader = sheet.CreateRow(rowId);
            rowHeader.Height = 40 * 20;
            ICellStyle style = CreateTableStyle(workbook, true, false);

            CreateApplyStyleToCell(rowHeader, 0, style, "Название");
            CreateApplyStyleToCell(rowHeader, 1, style, "Артикул");
            CreateApplyStyleToCell(rowHeader, 2, style, "Кол-во");
            CreateApplyStyleToCell(rowHeader, 3, style, "Зак. цена");
            CreateApplyStyleToCell(rowHeader, 4, style, "Цена");
            CreateApplyStyleToCell(rowHeader, 5, style, "Зак. сумма");
            CreateApplyStyleToCell(rowHeader, 6, style, "Сумма");

            sheet.CreateFreezePane(0, rowId + 1);
        }
Ejemplo n.º 32
0
        private int MSWriteToSheet2(Dictionary<int, SelfNPOICell[]> data, ISheet sheet)
        {
            if (data == null || data.Count == 0)
                return -1;

            ICellStyle backcolor = this.setCellBackColor();
            ICellStyle fontBold = this.setFontBold();
            ICellStyle wrapText = this.setWrapText();

            foreach (var r in data)
            {
                IRow row = sheet.CreateRow(r.Key);
                bool isSetBackColor = false;
                if (r.Key > 0 && r.Value[0].HasBackColor)
                {
                    isSetBackColor = true;
                }
                for (int col = 0; col < r.Value.Count(); col++)
                {
                    ICell cell = row.CreateCell(col);
                    cell.SetCellValue(r.Value[col].CellValue);
                    if (r.Value[col].IsFontBold)
                    {
                        cell.CellStyle = fontBold;

                    }
                    if (isSetBackColor)
                    {
                        cell.CellStyle = backcolor;
                    }
                    else if (col == 4)
                    {
                        cell.CellStyle = setWrapText();
                    }
                }
            }
            //设置合并单元格的长度,需全部显示文字
            sheet.SetColumnWidth(0, 24 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(4, 60 * 256);
            sheet.SetColumnWidth(8, 15 * 256);
            sheet.CreateFreezePane(0, 1, 0, 1);

            return 0;
        }