How to lock some excel columns as readonly for npoi excel sheet

Question

unable to set npoi excel sheet as read only for some columns I have tried create freeze (sheet1.CreateFreezePane(0, 1, 0, 1);) but not working

    public void WriteExcelWithNPOI(DataTable dt, String extension,string MSANAme,string CheckValue)
    {

        IWorkbook workbook;

        if (extension == "xlsx")
        {
            workbook = new XSSFWorkbook();
        }
        else if (extension == "xls")
        {
            workbook = new HSSFWorkbook();
        }
        else
        {
            throw new Exception("This format is not supported");
        }

        ISheet sheet1 = workbook.CreateSheet("Sheet 1");
        //make a header row
        IRow row1 = sheet1.CreateRow(0);



        for (int j = 0; j < dt.Columns.Count; j++)
        {

            ICell cell = row1.CreateCell(j);
            String columnName = dt.Columns[j].ToString();
            cell.SetCellValue(columnName);
        }

        //loops through data
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow row = sheet1.CreateRow(i + 1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {

                ICell cell = row.CreateCell(j);
                String columnName = dt.Columns[j].ToString();
                cell.SetCellValue(dt.Rows[i][columnName].ToString());
            }
        }

        using (var exportData = new MemoryStream())
        {
            Response.Clear();
            workbook.Write(exportData);
            if (extension == "xlsx") //xlsx file format
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}" + MSANAme + CheckValue+ ".xlsx"));

                Response.BinaryWrite(exportData.ToArray());
            }
            else if (extension == "xls")  //xls file format
            {
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename="+ MSANAme + CheckValue + ".xls"));
                Response.BinaryWrite(exportData.GetBuffer());
            }
            Response.End();
        }
    }

all the excel columns are editable, need to set some of the columns as readonly. please help. all the excel columns are editable, need to set some of the columns as readonly. please help. all the excel columns are editable, need to set some of the columns as readonly. please help.

freezepane is to stop scrolling, you need to protect the areas. — BugFinder, Jun 05 '19 at 09:53
yes, I found that out later. npoi version is v2.30. please let me know how to lock some columns in excel and allow others as editable. thanks — Yuvaraj77, Jun 06 '19 at 09:45
I dont use npoi, you would need to look at the documentation — BugFinder, Jun 06 '19 at 11:00

Krishna Chaithanya Muthyala · Answer 1 · 2019-06-19T13:27:05.200

Am going to assume your code is working, so am taking it as reference and editing it.

    public void WriteExcelWithNPOI(DataTable dt, String extension,string MSANAme,string CheckValue)
    {

        IWorkbook workbook;

        if (extension == "xlsx")
        {
            workbook = new XSSFWorkbook();
        }
        else if (extension == "xls")
        {
            workbook = new HSSFWorkbook();
        }
        else
        {
            throw new Exception("This format is not supported");
        }

        ISheet sheet1 = workbook.CreateSheet("Sheet 1");
        //make a header row
        IRow row1 = sheet1.CreateRow(0);

        var cellStyleForLocked = workbook.CreateCellStyle(); //create the style in the beginning itself
        cellStyleForLocked.IsLocked = true;

        for (int j = 0; j < dt.Columns.Count; j++)
        {

            ICell cell = row1.CreateCell(j);
            String columnName = dt.Columns[j].ToString();
            cell.SetCellValue(columnName);
        }

        //loops through data
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow row = sheet1.CreateRow(i + 1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {

                ICell cell = row.CreateCell(j);
                String columnName = dt.Columns[j].ToString();

                if(j < 6) //set the locked style only for first 6 columns.
                    cell.CellStyle = cellStyleForLocked;

                cell.SetCellValue(dt.Rows[i][columnName].ToString());
            }
        }

        using (var exportData = new MemoryStream())
        {
            Response.Clear();
            workbook.Write(exportData);
            if (extension == "xlsx") //xlsx file format
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}" + MSANAme + CheckValue+ ".xlsx"));

                Response.BinaryWrite(exportData.ToArray());
            }
            else if (extension == "xls")  //xls file format
            {
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename="+ MSANAme + CheckValue + ".xls"));
                Response.BinaryWrite(exportData.GetBuffer());
            }
            Response.End();
        }
    }

Hopefully it works for you!

Tried this while iterating "dt.Columns.Count", still not working, please help. And I need to lock only first 6 columns in excel and leave the 7th column editable. please help. Thanks — Yuvaraj77, Jun 12 '19 at 07:00
you have to create different cell styles, in the beginning of the function, not while iterating. Then, while iterating, assign the cell style you want for the particular row and cell. — Krishna Chaithanya Muthyala, Jun 12 '19 at 14:03
@Yuvaraj77, do you still need code sample for different cell styles? — Krishna Chaithanya Muthyala, Jun 14 '19 at 11:33
Tried it but still the issue persists please send code samples. Thanks. — Yuvaraj77, Jun 19 '19 at 05:41

How to lock some excel columns as readonly for npoi excel sheet

1 Answers1