If you have to type the same data into cells all the time, then adding a drop down list to your spreadsheet could be the answer. In Excel, this comes under the heading of Data Validation.
In the example below, we have a class of students on a drop down list. We only have to click a cell in the A column to see this same list of students. You'll see how to do that now. Here's a picture of your finished spreadsheet:
In the image above, we can simply select a student from the drop down list — no more typing! We can also do the same for the Subject and Grade.
So, create the following headings in a new spreadsheet:
Cell A1 Student Cell B1 Subject Cell C1 Grade Cell E1 Comments
We now need some data to go in our lists. So, type the same data as in the image below. It doesn't need to go in the same columns as ours. But don't type anything in columns A, B, C or E:
The data in Columns F, G and H above will be going in to our list.
Now click on Column A to highlight that entire column:
With Column A highlighted, click on Data from the Excel Ribbon at the top. From the Data tab, locate the Data Tools panel. On the Data Tools panel, click on the Data Validation item. Select Data Validation from the menu:
When you click Data Validation, you'll see the following dialogue box appear:
To create a drop down list, click the down arrow just to the right of «Allow: Any Value» on the Settings tab:
Select List from the drop down menu, and you'll see a new area appear:
Source means which data you want to go in your list. You can either just type in your cell references here, or let Excel do it for you.
To let Excel handle the job, click the icon to the right of the Source textbox:
When you click this icon, the Data Validation dialogue box will shrink:
- Now select the cells on your spreadsheet that you want in your list. For us, this is the Students:
- Once you have selected your data, click the same icon on the Data Validation dialogue box. You'll then be returned to the full size one, with your cell references filled in for you:
- Click OK, and you'll see the A column with a drop down list in cell A1:
However, you don't want a drop down list for your A1 column heading. To get rid of it, click inside of cell A1. Click the Data Validation item on the Data Tools panel again to bring up the dialogue box. From the Allow list, select Any Value:
Click OK on the Data Validation dialogue box, and your drop down list in cell A1 will be gone.
The rest of the column will still have drop down lists, though. Try it out. Click inside cell A2, and you'll see a down-pointing arrow:
- Click the arrow to see your list:
Select an item on your list to enter that name in the cell. Click any other cell in the A column and you'll see the same list.
Adding a drop down list to your cell can save you a lot of time. And it means that typing errors won't creep in to your work.
Exercise
Источник: https://www.homeandlearn.co.uk/excel2007/excel2007s8p2.html
Excel Dropdown Lists Tutorial
In Excel, a user has the ability to customize columns and rows of cells through unique dropdown menus. In dropdown lists, you can access many data choices for output within a cell. The Excel dropdown lists tutorial will show you how achieve this.
Download the tutorial and follow along.
In the example, you will learn how to easily assign an employee to a department using the creation of dropdown lists.
Creating a dropdown list in Excel
Choose the “List of Departments” in the example you downloaded and drag your selection to have A9 through A13 highlighted together. Right click and select “Define Name” from the list that appears.
A new box titled “New Name” will now show. Give the list title a descriptive name to identify it. We chose “Departments”.
Now you have made a list of the departments for the spreadsheet. All that is needed now is to tell Excel where to insert the list.
To do this, highlight cells C3 through C6.
Choose the “Data” tab in the top Ribbon bar and click “Data Validation”. From the dropdown list that appears, pick the “Data Validation” option.
In the Data Validation dialog box that comes up, make sure you’re on the “Settings” tab. In the dropdown under “Allow”, choose “List”. More options will then appear. Choose the text box located under “Source”:
- Enter in “=Departments” (without quotations) or the name you made earlier for the list.
- If you want to customize messages for the dropdown list, there are two tabs in the dialog called “Input Message” and “Error Alert” for you to take advantage of.
- When you’re done, press enter.
Under “Employee Information”, go to the empty “Department” cell next to an employee name and choose that empty cell. A dropdown arrow will show the options for each department that you can assign to that employee. This concludes the dropdown lists tutorial.
Related Templates:
- Macros
- Preventing Duplicates in Excel
- Excel Autocorrect Guide
- How to Use the COUNTIF Function in Excel
- Highlight Rows in Excel
Источник: http://exceltemplates.net/excel-guides/excel-dropdown-lists-tutorial/
Create a drop-down list
Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac More… Less
You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create.
Want to be walked through this process? Try our new online tutorial for drop-down lists (beta).
-
In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.
-
Select the cell in the worksheet where you want the drop-down list.
-
Go to the Data tab on the Ribbon, then Data Validation.
-
On the Settings tab, in the Allow box, click List.
-
Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:
-
If it’s OK for people to leave the cell empty, check the Ignore blank box.
-
Check the In-cell dropdown box.
-
Click the Input Message tab.
- If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
- If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.
-
Click the Error Alert tab.
- If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.
- If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.
-
Not sure which option to pick in the Style box?
- To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Information will show a message with this icon and Warning will show a message with this icon .
- To stop people from entering data that isn’t in the drop-down list, click Stop. Note: If you don't add a title or text, the title defaults to «Microsoft Excel» and the message to: «The value you entered is not valid. A user has restricted values that can be entered into this cell.»
Источник: https://support.office.com/en-ie/article/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b
Создать все строки столбца как DropDownList в Excel с помощью C #
Мое требование экспортировать пустой первенствовать лист с 5 столбцами, в которых 3-е столбцы всех строк как DropDownList, так что пользователь может использовать этот рабочий лист для изменения данных в соответствии с их потребностями. Я использую C # для экспорта файла.
Я уже работал на нем, но что на данный момент, это только создает выпадающий список в определенной ячейке, но я хочу, чтобы все строки первого столбца в виде списка.
Am используя Gembox таблицу, чтобы создать файл Excel.
Ниже приведен код, я использую:
SpreadsheetInfo.SetLicense(«FREE-LIMITED-KEY»);
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add(«Journal Entry Format»);
ws.Columns[0].Width = 30 * 256;
ws.Columns[1].Width = 30 * 256;
ws.Columns[2].Width = 30 * 256;
ws.Columns[3].Width = 30 * 256;
ws.Columns[4].Width = 30 * 256;
ws.Columns[5].Width = 30 * 256;
ws.Columns[6].Width = 30 * 256;
CellStyle tmpStyle2 = new CellStyle();
tmpStyle2.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle2.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle2.FillPattern.SetSolid(System.Drawing.Color.Yellow);
ws.Cells.GetSubrangeAbsolute(0, 0, 1, 5).Style = tmpStyle2;
ws.Cells[1, 0].Value = «Last»;
ws.Cells.GetSubrangeAbsolute(1, 0, 1, 5).Merged = true;
CellStyle tmpStyle1 = new CellStyle();
tmpStyle1.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle1.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle1.FillPattern.SetSolid(System.Drawing.Color.Blue);
tmpStyle1.Font.Weight = ExcelFont.BoldWeight;
tmpStyle1.Font.Color = System.Drawing.Color.White;
tmpStyle1.WrapText = true;
ws.Cells[3, 0].Value = «Voucher Date»;
ws.Cells[3, 1].Value = «Voucher No.»;
ws.Cells[3, 2].Value = «Select Ledger»;
ws.Cells[3, 3].Value = «Debit/Credit»;
ws.Cells[3, 4].Value = «Amount»;
ws.Cells[3, 5].Value = «Narration»;
ws.Cells.GetSubrangeAbsolute(3, 0, 3, 5).Style = tmpStyle1;
var list = new List();
foreach (var led in ledgers)
{
list.Add(led.AccountHead);
}
var flatList = string.Join(«,», list.ToArray());
for (int i = 3; i < 100; ++i) { DataValidation dv = new DataValidation(); dv.InCellDropdown = true; dv.InputMessage = "Select Ledger"; dv.Formula1 = flatList; ws.DataValidations.Add(dv); ws.Columns[2].Cells[i + 1].Value = dv.Formula1; } ws.PrintOptions.FitWorksheetWidthToPages = 1; ef.Save(this.Response, "JournalEntry Format" + ".xlsx");
Источник: https://askvoprosy.com/voprosy/create-all-the-rows-of-a-column-as-a-dropdownlist-in-excel-using-c
Как создать Dropdownlist в Excel с помощью Openxml в C#
Есть некоторые листы, созданные из файла XML.
На втором листе есть некоторые имена .excel создается путем чтения файла XML и изменения его на набор данных, а затем созданный рабочий лист и все остальные строки и столбцы с помощью OPEN-XML.
Так что я хочу создать список, используя имена из 2-го листа, и показать список в листе 1 как выпадающее меню. Использование OPEN-XML Я хочу создать раскрывающийся список с данными, взятыми со 2-й страницы. Я просматриваю много раз, но я не нашел никакого решения. Можно ли создать раскрывающийся список с помощью openxml.
Это мой весь код для создания excel из xml-файла, поэтому, если у него есть решение, пожалуйста, помогите мне.
public void ExportDSToExcel(DataSet ds, string dest)
{
try
{
using (var workbook = SpreadsheetDocument.Create(dest, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
foreach (DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements().Count() > 0)
{
sheetId =
sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
if(sheet.Name==»Customer»)
{
PageMargins pageM = sheetPart.Worksheet.GetFirstChild();
SheetProtection sheetProtection = new SheetProtection();
sheetProtection.Password = «admin»;
sheetProtection.Sheet = true;
sheetProtection.Objects = true;
sheetProtection.Scenarios = true;
ProtectedRanges pRanges = new ProtectedRanges();
ProtectedRange pRange = new ProtectedRange();
ListValue lValue = new ListValue();
lValue.InnerText = «»; //set cell which you want to make it editable
pRange.SequenceOfReferences = lValue;
pRange.Name = «not allow editing»;
pRanges.Append(pRange);
sheetPart.Worksheet.InsertBefore(sheetProtection, pageM);
sheetPart.Worksheet.InsertBefore(pRanges, pageM);
if (cell.CellReference == «B4»)
{
CellFormula cellformula = new CellFormula();
cellformula.Text = «=INDEX(Sheet5!B:B,MATCH(A4,Sheet5!B:B,0))»;
CellValue cellValue = new CellValue();
cellValue.Text = «0»;
cell.Append(cellformula);
cell.Append(cellValue);
}
}
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
if (RadioButtonList1.SelectedItem.Text == «Yes»)
{
PageMargins pageM = sheetPart.Worksheet.GetFirstChild();
SheetProtection sheetProtection = new SheetProtection();
sheetProtection.Password = «admin»;
sheetProtection.Sheet = true;
sheetProtection.Objects = true;
sheetProtection.Scenarios = true;
ProtectedRanges pRanges = new ProtectedRanges();
ProtectedRange pRange = new ProtectedRange();
ListValue lValue = new ListValue();
lValue.InnerText = «»; //set cell which you want to make it editable
pRange.SequenceOfReferences = lValue;
pRange.Name = «not allow editing»;
pRanges.Append(pRange);
sheetPart.Worksheet.InsertBefore(sheetProtection, pageM);
sheetPart.Worksheet.InsertBefore(pRanges, pageM);
}
else
{
}
List columns = new List();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}
catch
{
lblstatus.Text = «File Upload Not Succesfull «;
}
lblstatus.Text = «File Upload Succesfull «;
}
protected void Button1_Click(object sender, EventArgs e)
{
if(txtname.Text != null)
{
if (FileUpload1.HasFile == true)
{
string myXMLfile = «/uploads/» + FileUpload1.FileName;
FileUpload1.SaveAs(Server.MapPath(myXMLfile));
string dest = «D:/uploads/» + txtname.Text+».xlsx»;
DataSet ds = new DataSet();
try
{
ds.ReadXml(Server.MapPath(myXMLfile));
}
catch (Exception ex)
{
lblstatus.Text=(ex.ToString());
}
ExportDSToExcel(ds, dest);
}
else
{
lblstatus.Text = «Please Upload the file «;
}
}
else {
lblstatus.Text = «Please enter the name «;
}
}
}
c# excel openxml dropdownlistfor468
Источник: https://stackoverrun.com/ru/q/11010771
как создать dropdownlist в excel с помощью инструкции openxml в c#
Есть несколько листов, который создается из XML-файла.
Есть некоторые имена в 2-й лист .в Excel создается читает XML-файл и изменить его данные и в дальнейшем создал таблицу и все остальные строки и столбцы с помощью открытого формата XML .
Поэтому я хочу создать список, используя имена из 2-го листа и показать список в лист 1 в выпадающем.С помощью Open XML и я хочу создать капля downlist с данными, взятыми из 2-й странице. Я могу просматривать много раз, но я не найти любое решение можно создать выпадающий список с помощью OpenXML.
Это весь мой код для создания Excel из XML-файла, так что если есть решение, Пожалуйста, помогите мне.
public void ExportDSToExcel(DataSet ds, string dest){ try { using (var workbook = SpreadsheetDocument.Create(dest, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; foreach (DataTable table in ds.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements().Count() > 0) { sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); if(sheet.Name==»Customer») { PageMargins pageM = sheetPart.Worksheet.GetFirstChild(); SheetProtection sheetProtection = new SheetProtection(); sheetProtection.Password = «admin»; sheetProtection.Sheet = true; sheetProtection.Objects = true; sheetProtection.Scenarios = true; ProtectedRanges pRanges = new ProtectedRanges(); ProtectedRange pRange = new ProtectedRange(); ListValue lValue = new ListValue(); lValue.InnerText = «»; //set cell which you want to make it editable pRange.SequenceOfReferences = lValue; pRange.Name = «not allow editing»; pRanges.Append(pRange); sheetPart.Worksheet.InsertBefore(sheetProtection, pageM); sheetPart.Worksheet.InsertBefore(pRanges, pageM); if (cell.CellReference == «B4») { CellFormula cellformula = new CellFormula(); cellformula.Text = «=INDEX(Sheet5!B:B,MATCH(A4,Sheet5!B:B,0))»; CellValue cellValue = new CellValue(); cellValue.Text = «0»; cell.Append(cellformula); cell.Append(cellValue); } } DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); if (RadioButtonList1.SelectedItem.Text == «Yes») { PageMargins pageM = sheetPart.Worksheet.GetFirstChild(); SheetProtection sheetProtection = new SheetProtection(); sheetProtection.Password = «admin»; sheetProtection.Sheet = true; sheetProtection.Objects = true; sheetProtection.Scenarios = true; ProtectedRanges pRanges = new ProtectedRanges(); ProtectedRange pRange = new ProtectedRange(); ListValue lValue = new ListValue(); lValue.InnerText = «»; //set cell which you want to make it editable pRange.SequenceOfReferences = lValue; pRange.Name = «not allow editing»; pRanges.Append(pRange); sheetPart.Worksheet.InsertBefore(sheetProtection, pageM); sheetPart.Worksheet.InsertBefore(pRanges, pageM); } else { } List columns = new List(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } } } catch { lblstatus.Text = «File Upload Not Succesfull «; } lblstatus.Text = «File Upload Succesfull «;} protected void Button1_Click(object sender, EventArgs e) { if(txtname.Text != null) { if (FileUpload1.HasFile == true) { string myXMLfile = «/uploads/» + FileUpload1.FileName; FileUpload1.SaveAs(Server.MapPath(myXMLfile)); string dest = «D:/uploads/» + txtname.Text+».xlsx»; DataSet ds = new DataSet(); try { ds.ReadXml(Server.MapPath(myXMLfile)); } catch (Exception ex) { lblstatus.Text=(ex.ToString()); } ExportDSToExcel(ds, dest); } else { lblstatus.Text = «Please Upload the file «; } } else { lblstatus.Text = «Please enter the name «; } }}
Похожие вопросы
© 2019 — Вопросы и ответы по программированию
Источник: http://otzovio.ru/Question/Index/1108473/how-to-create-dropdownlist-in-excel-using-openxml-in-c
Как создать Dropdownlist в Excel с помощью Openxml в C #
Существуют некоторые листы, созданные из файла XML.
На втором листе есть некоторые имена .excel создается путем чтения файла XML и изменения его на набор данных, а затем созданный рабочий лист и все остальные строки и столбцы с помощью OPEN-XML.
Поэтому я хочу создать список, используя имена из второго листа, и показать список в листе 1 как выпадающее меню. Используя OPEN-XML, я хочу создать раскрывающийся список с данными, взятыми со 2-й страницы. Я просматриваю много раз, но я не нашел никакого решения. Можно ли создать раскрывающийся список с помощью openxml.
Это мой весь код для создания excel из xml-файла, поэтому, если у него есть решение, пожалуйста, помогите мне.
public void ExportDSToExcel(DataSet ds, string dest) { try { using (var workbook = SpreadsheetDocument.Create(dest, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; foreach (DataTable table in ds.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements().Count() > 0) { sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); if(sheet.Name==»Customer») { PageMargins pageM = sheetPart.Worksheet.GetFirstChild(); SheetProtection sheetProtection = new SheetProtection(); sheetProtection.Password = «admin»; sheetProtection.Sheet = true; sheetProtection.Objects = true; sheetProtection.Scenarios = true; ProtectedRanges pRanges = new ProtectedRanges(); ProtectedRange pRange = new ProtectedRange(); ListValue lValue = new ListValue(); lValue.InnerText = «»; //set cell which you want to make it editable pRange.SequenceOfReferences = lValue; pRange.Name = «not allow editing»; pRanges.Append(pRange); sheetPart.Worksheet.InsertBefore(sheetProtection, pageM); sheetPart.Worksheet.InsertBefore(pRanges, pageM); if (cell.CellReference == «B4») { CellFormula cellformula = new CellFormula(); cellformula.Text = «=INDEX(Sheet5!B:B,MATCH(A4,Sheet5!B:B,0))»; CellValue cellValue = new CellValue(); cellValue.Text = «0»; cell.Append(cellformula); cell.Append(cellValue); } } DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); if (RadioButtonList1.SelectedItem.Text == «Yes») { PageMargins pageM = sheetPart.Worksheet.GetFirstChild(); SheetProtection sheetProtection = new SheetProtection(); sheetProtection.Password = «admin»; sheetProtection.Sheet = true; sheetProtection.Objects = true; sheetProtection.Scenarios = true; ProtectedRanges pRanges = new ProtectedRanges(); ProtectedRange pRange = new ProtectedRange(); ListValue lValue = new ListValue(); lValue.InnerText = «»; //set cell which you want to make it editable pRange.SequenceOfReferences = lValue; pRange.Name = «not allow editing»; pRanges.Append(pRange); sheetPart.Worksheet.InsertBefore(sheetProtection, pageM); sheetPart.Worksheet.InsertBefore(pRanges, pageM); } else { } List columns = new List(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } } } catch { lblstatus.Text = «File Upload Not Succesfull «; } lblstatus.Text = «File Upload Succesfull «; } protected void Button1_Click(object sender, EventArgs e) { if(txtname.Text != null) { if (FileUpload1.HasFile == true) { string myXMLfile = «/uploads/» + FileUpload1.FileName; FileUpload1.SaveAs(Server.MapPath(myXMLfile)); string dest = «D:/uploads/» + txtname.Text+».xlsx»; DataSet ds = new DataSet(); try { ds.ReadXml(Server.MapPath(myXMLfile)); } catch (Exception ex) { lblstatus.Text=(ex.ToString()); } ExportDSToExcel(ds, dest); } else { lblstatus.Text = «Please Upload the file «; } } else { lblstatus.Text = «Please enter the name «; } } }
Вам нужно создать валидатор
- Первый параметр – это рабочий лист, на котором создается раскрывающийся список
- Второй параметр – это рабочий лист для получения данных из
A1: A1048576 – это ячейки для применения этого валидатора
public void CreateValidator(Worksheet ws, string dataContainingSheet) { /*** DATA VALIDATION CODE ***/ DataValidations dataValidations = new DataValidations(); DataValidation dataValidation = new DataValidation { Type = DataValidationValues.List, AllowBlank = true, SequenceOfReferences = new ListValue { InnerText = «A1:A1048576» } }; dataValidation.Append( //new Formula1 { Text = «»FirstChoice,SecondChoice,ThirdChoice»» } new Formula1(string.Format(«'{0}'!$A:$A», dataContainingSheet)) ); dataValidations.Append(dataValidation); var wsp = ws.WorksheetPart; wsp.Worksheet.AppendChild(dataValidations); }
Источник: https://excel.bilee.com/dropdownlist-excel-openxml-c.html
Загрузка...
