C # npoi export excel horizontal and vertical display

Time:2021-5-10
/// 
        ///Datatable export to excel (vertical export)
        /// 
        ///Data sources
        ///Saved path
        ///Table name
        public static void Excel2(this DataTable dataTable, string filePath, string documentname)
        {
            string sheetName = "Sheet1";
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new Exception("No data to export");

            ISheet sheet = null;
            IWorkbook workbook = null;

            try
            {
                if (Directory.Exists(filePath) == false)
                {
                    Directory.CreateDirectory(filePath);
                }
                string filedocmentname = "\File Card.xls";// file name
                using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
                {
                    workbook = new HSSFWorkbook();
                    if (string.IsNullOrEmpty(sheetName))
                        sheetName = "Sheet1";// workbook 
                    sheet = workbook.CreateSheet(sheetName);
                    IRow row = sheet.CreateRow(0);
                    IFont font = workbook.CreateFont();
                    font.FontName = "Arial";// Font style
                    font.Boldweight = (short)FontBoldWeight.Bold;
                    ICellStyle headerStyle2 = workbook.CreateCellStyle();
                    headerStyle2.VerticalAlignment = VerticalAlignment.Center; // Vertical center
                    headerStyle2.WrapText = true;// Wrap automatically
                    font.FontHeightInPoints = 9;// typeface

                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        ICellStyle header= workbook.CreateCellStyle();
                        header.VerticalAlignment = VerticalAlignment.Center; // Vertical center
                        header.WrapText = true;// Wrap automatically
                        var row1_ =  sheet.CreateRow(i);// Create row
                        var cellName = row1_. CreateCell(0);// Listing
                        cellName.SetCellValue(dataTable.Columns[i].ColumnName.ToString().Replace("", "\n"));
                        IFont font2 = workbook.CreateFont();
                        font2.Boldweight = (short)FontBoldWeight.Bold;
                        header.SetFont(font);
                        cellName.CellStyle = header; // fill in content
                        for (int j = 0; j < dataTable.Rows.Count; j++)
                        {
                            var cell1_ = row1_.CreateCell(j + 1);
                            cell1_.SetCellValue(dataTable.Rows[j][i].ToString().Replace("", "\n"));
                            cell1_. CellStyle = headerStyle2; // Assign styles to cells
                        }
                    }
                    //Set column width
                    sheet.SetColumnWidth(0, 10 * 256+200);// Column width is 10 // set row height // row. Height = 30 * 20// The row height is 30
                    workbook.Write(fs); // Write to excel
                }
            }
            finally
            {
                if (workbook != null)
                    workbook.Clear();
            }
        }
/// 
        ///Datatable export to excel (horizontal export)
        /// 
        ///Data sources
        ///Saved path
        ///Table name
        public static void Excel(this DataTable dataTable, string filePath, string documentname)
        The first column of {// datatable is added with sequence number
            dataTable= AddSeriNumToDataTable(dataTable);
            string sheetName = "Sheet1";
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new Exception("No data to export");

            ISheet sheet = null;
            IWorkbook workbook = null;

            try
            {
                if (Directory.Exists(filePath) == false)
                {
                    Directory.CreateDirectory(filePath);
                }
                string filedocmentname = "\List.xls";// File name using (FileStream FS = new FileStream (filepath + filedocumentname, FileMode. Create, fileaccess. Write))
                {
                    workbook = new HSSFWorkbook();
                    if (string.IsNullOrEmpty(sheetName))
                        sheetName = "Sheet1";
                    sheet = workbook.CreateSheet(sheetName);
                    IRow row = sheet.CreateRow(0);
                    IFont font = workbook.CreateFont();
                    font.FontName = "Arial";// Font style
                    ICellStyle headerStyle2 = workbook.CreateCellStyle();
                    headerStyle2.VerticalAlignment = VerticalAlignment.Center; // Vertical center
                    headerStyle2.WrapText = true;// Wrap automatically
                    font.FontHeightInPoints = 9;// typeface
                    //Fill header
                    for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        ICellStyle headerStyle = workbook.CreateCellStyle(); 
                        headerStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;// First line fill color
                        headerStyle.FillPattern = FillPattern.SolidForeground;
                        headerStyle.VerticalAlignment = VerticalAlignment.Center; // Vertical center 
                        headerStyle.WrapText = true;// Wrap automatically
                        headerStyle.BorderTop = BorderStyle.Thin;// border-top 
                        headerStyle.BorderBottom = BorderStyle.Thin;// bottom
                        headerStyle.BorderLeft = BorderStyle.Thin;// border-left 
                        headerStyle.BorderRight = BorderStyle.Thin;// border-right 
                        var cell = row.CreateCell(columnIndex);
                        cell.CellStyle = headerStyle;
                        cell.SetCellValue(dataTable.Columns[columnIndex].ColumnName.Replace("", "\n")); // Set column name
                        row.HeightInPoints= 35;
                    }
                    //Fill in content
                    for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                    {
                        row = sheet.CreateRow(rowIndex + 1);
                        for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                        {
                            row.CreateCell(columnIndex).SetCellValue(Convert.ToString(dataTable.Rows[rowIndex][columnIndex]));
                            ICell cellData = row.CreateCell(columnIndex);
                            cellData.SetCellValue(dataTable.Rows[rowIndex][columnIndex].ToString().Replace("", "\n"));
                            cellData.CellStyle = headerStyle2; // Assign styles to cells
                        }
                    }
                    sheet.CreateFreezePane(0, 1, 0, 1); // First line freeze
                    workbook.Write(fs); // Write to excel
                }
            }
            finally
            {
                if (workbook != null)
                    workbook.Clear();
            }
        }
/// 
        ///Add an ordinal column in datatable, and the number increases from 1 to 1
        /// 
        /// DataTable
        /// 
        public static DataTable AddSeriNumToDataTable(DataTable dt)
        {
            //The value to be returned
            DataTable dtNew;
            If (DT. Columns. Indexof ("serial number") > = 0)
            {
                dtNew = dt;
            }
            Else // add an ordinal column and add it in the first column
            {
                int rowLength = dt.Rows.Count;
                int colLength = dt.Columns.Count;
                DataRow[] newRows = new DataRow[rowLength];
                dtNew = new DataTable();
                //Add the ordinal column to the first column
                Dtnew. Columns. Add ("serial number");
                for (int i = 0; i < colLength; i++)
                {
                    dtNew.Columns.Add(dt.Columns[i].ColumnName);
                    //Copy data in DT
                    for (int j = 0; j < rowLength; j++)
                    {
                        if (newRows[j] == null)
                            newRows[j] = dtNew.NewRow();
                        //Fill other data into the second column, because the first column is the new ordinal column
                        newRows[j][i + 1] = dt.Rows[j][i];
                    }
                }
                foreach (DataRow row in newRows)
                {
                    dtNew.Rows.Add(row);
                }
            }
            //Fill in the ordinal column, increasing from 1
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Dtnew. Rows [i] [serial number] = I + 1;
            }
            return dtNew;
        }
/// 
        ///Datatable export to excel (without any style)
        /// 
        /// DataTable
        /// 
        public static void ExportExcel(DataTable dataTable, string filePath, string documentname)
        {
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                HSSFWorkbook excelBook = new HSSFWorkbook();
                ISheet sheet1 = excelBook.CreateSheet("Export Data");
                IRow row1 = sheet1.CreateRow(0);

                //Fill header
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    row1.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
                }

                //Fill in content
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    IRow rowTemp = sheet1.CreateRow(i + 1);
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        rowTemp.CreateCell(j).SetCellValue(string.Format("{0}", dataTable.Rows[i][j]));
                    }
                }
                //Save
                MemoryStream ms = new MemoryStream();
                excelBook.Write(ms);
                if (Directory.Exists(filePath) == false)
                {
                    Directory.CreateDirectory(filePath);
                }
                string filedocmentname = "\List.xls";                using (FileStream fs = new FileStream(filePath + filedocmentname, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
                ms.Close();
                ms.Dispose();
            }
        }

Vertical export of Excel renderings

Horizontal export of Excel renderings

Copyright notice: This article is the original article of the blogger, please attach the blog link if you reprint it!

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]