将Silverlight网格导出到Excel工作表只会写入1行
本文关键字：1行 工作 Excel 网格 Silverlight | 更新日期: 2023-09-27 18:27:34
我有一个web silverlight应用程序，用户可以从桌面上选择一个excel文件，并添加3个excel列。这部分在桌面应用程序中运行良好。但我们需要它作为一个网络应用程序。现在网络应用程序只写第一行。我有一个"导出"功能，可以获取网格上的内容并制作一个excel文件。
public void Export()
{
Presettings();
string ss = "urn:schemas-microsoft-com:office:spreadsheet";
SaveFileDialog dialog = new SaveFileDialog();
//////dialog.DefaultExt = "*.xml";
dialog.DefaultExt = "*.xls";
//Execl files (*.xls)|*.xls
dialog.Filter = "Execl files (*.xls)|*.xls";
//////dialog.Filter = "Excel Xml (*.xml)|*.xml|All files (*.*)|*.*";
if (dialog.ShowDialog() == false)
return;
XmlWriterSettings myXmlSettings = new XmlWriterSettings();
myXmlSettings.Indent = true;
myXmlSettings.NewLineOnAttributes = false;
using (XmlWriter myXML = XmlWriter.Create(dialog.OpenFile(), myXmlSettings))
{
var _with1 = myXML;
_with1.WriteStartDocument();
_with1.WriteProcessingInstruction("mso-application", "progid=" + '"' + "Excel.Sheet" + '"');
_with1.WriteStartElement("Workbook", ss);
_with1.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");
_with1.WriteElementString("Author", m_DocumentProperties.Author);
_with1.WriteElementString("LastAuthor", m_DocumentProperties.LastAuthor);
_with1.WriteElementString("Created", m_DocumentProperties.Created.ToString());
_with1.WriteElementString("LastSaved", m_DocumentProperties.LastSaved.ToString());
_with1.WriteElementString("Company", m_DocumentProperties.Company);
_with1.WriteElementString("Version", m_DocumentProperties.Version);
_with1.WriteEndElement();
//Document Properties
_with1.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");
_with1.WriteElementString("WindowHeight", m_WorkbookProperties.WindowHeight.ToString());
_with1.WriteElementString("WindowWidth", m_WorkbookProperties.WindowWidth.ToString());
_with1.WriteElementString("WindowTopX", m_WorkbookProperties.WindowTopX.ToString());
_with1.WriteElementString("WindowTopY", m_WorkbookProperties.WindowTopY.ToString());
_with1.WriteElementString("ProtectStructure", m_WorkbookProperties.ProtectStructure.ToString());
_with1.WriteElementString("ProtectWindows", m_WorkbookProperties.ProtectWindows.ToString());
_with1.WriteEndElement();
//Excel Workbook
_with1.WriteStartElement("Styles");
for (int I = 0; I <= m_Styles.Count - 1; I++)
{
_with1.WriteStartElement("Style");
_with1.WriteAttributeString("ss", "ID", ss, m_Styles[I].ID);
if (!string.IsNullOrEmpty(m_Styles[I].Name))
{
_with1.WriteAttributeString("ss", "Name", ss, m_Styles[I].Name);
}
//ALIGNMENT LOGIC:
_with1.WriteStartElement("Alignment");
if ((m_Styles[I].Alignment != null))
{
if (m_Styles[I].Alignment.Horizontal != 0)
{
_with1.WriteAttributeString("ss", "Horizontal", ss, m_Styles[I].Alignment.Horizontal.ToString());
}
if (m_Styles[I].Alignment.Vertical != 0)
{
_with1.WriteAttributeString("ss", "Vertical", ss, m_Styles[I].Alignment.Vertical.ToString());
}
if (m_Styles[I].Alignment.WrapText == true)
{
_with1.WriteAttributeString("ss", "WrapText", ss, "1");
}
}
_with1.WriteEndElement();
//BORDER LOGIC:
_with1.WriteStartElement("Borders");
if (!(m_Styles[I].Borders.Count == 0))
{
for (int J = 0; J <= m_Styles[I].Borders.Count - 1; J++)
{
ExcelBorder myBorder = m_Styles[I].Borders[J];
if (myBorder.Position == Excel.Styles.Position.All)
{
_with1.WriteStartElement("Border");
_with1.WriteAttributeString("ss", "Position", ss, "Bottom");
_with1.WriteAttributeString("ss", "LineStyle", ss, myBorder.LineStyle.ToString());
_with1.WriteAttributeString("ss", "Weight", ss, myBorder.Weight.ToString());
_with1.WriteEndElement();
//Border
_with1.WriteStartElement("Border");
_with1.WriteAttributeString("ss", "Position", ss, "Left");
_with1.WriteAttributeString("ss", "LineStyle", ss, myBorder.LineStyle.ToString());
_with1.WriteAttributeString("ss", "Weight", ss, myBorder.Weight.ToString());
_with1.WriteEndElement();
//Border
_with1.WriteStartElement("Border");
_with1.WriteAttributeString("ss", "Position", ss, "Right");
_with1.WriteAttributeString("ss", "LineStyle", ss, myBorder.LineStyle.ToString());
_with1.WriteAttributeString("ss", "Weight", ss, myBorder.Weight.ToString());
_with1.WriteEndElement();
//Border
_with1.WriteStartElement("Border");
_with1.WriteAttributeString("ss", "Position", ss, "Top");
_with1.WriteAttributeString("ss", "LineStyle", ss, myBorder.LineStyle.ToString());
_with1.WriteAttributeString("ss", "Weight", ss, myBorder.Weight.ToString());
_with1.WriteEndElement();
//Border
}
else
{
_with1.WriteStartElement("Border");
_with1.WriteAttributeString("ss", "Position", ss, myBorder.Position.ToString());
_with1.WriteAttributeString("ss", "LineStyle", ss, myBorder.LineStyle.ToString());
_with1.WriteAttributeString("ss", "Weight", ss, myBorder.Weight.ToString());
_with1.WriteEndElement();
//Border
}
}
}
_with1.WriteEndElement();
//Borders
//FONT LOGIC:
_with1.WriteStartElement("Font");
ExcelFont myFont = m_Styles[I].Font;
_with1.WriteAttributeString("ss", "FontName", ss, myFont.FontName.ToString());
_with1.WriteAttributeString("ss", "Size", ss, myFont.Size.ToString());
_with1.WriteAttributeString("ss", "Color", ss, myFont.Color.ToString().Remove(1, 2));
if (myFont.Bold == true)
_with1.WriteAttributeString("ss", "Bold", ss, "1");
if (myFont.Italic == true)
_with1.WriteAttributeString("ss", "Italic", ss, "1");
if (myFont.Underline != 0)
_with1.WriteAttributeString("ss", "Underline", ss, myFont.Underline.ToString());
_with1.WriteEndElement();
ExcelInterior myInterior = m_Styles[I].Interior;
_with1.WriteStartElement("Interior");
_with1.WriteAttributeString("ss", "Color", ss, myInterior.Color.ToString().Remove(1, 2));
_with1.WriteAttributeString("ss", "Pattern", ss, "Solid");
_with1.WriteEndElement();
_with1.WriteStartElement("NumberFormat");
_with1.WriteEndElement();
_with1.WriteStartElement("Protection");
_with1.WriteEndElement();
_with1.WriteEndElement();
//Style
}
_with1.WriteEndElement();
//Styles
//WORKSHEETS:
Worksheet myWorksheet = m_Worksheets[0];
_with1.WriteStartElement("Worksheet");
_with1.WriteAttributeString("ss", "Name", ss, myWorksheet.Name);
_with1.WriteStartElement("Table");
_with1.WriteAttributeString("ss", "ExpandedColumnCount", ss, myWorksheet.Table.ExpandedColumnCount.ToString());
_with1.WriteAttributeString("ss", "ExpandedRowCount", ss, Convert.ToString(myWorksheet.Table.ExpandedRowCount + 100));
//Temporary fix: sometimes 1 row is not added.
_with1.WriteAttributeString("ss", "FullColumns", ss, myWorksheet.Table.FullColumns.ToString());
_with1.WriteAttributeString("ss", "FullRows", ss, Convert.ToString(myWorksheet.Table.FullRows + 100));
//Temporary fix: sometimes 1 row is not added.
_with1.WriteAttributeString("ss", "DefaultRowHeight", ss, myWorksheet.Table.DefaultRowHeight.ToString());
for (int J = 0; J <= myWorksheet.Table.Columns.Count - 1; J++)
{
_with1.WriteStartElement("Column");
_with1.WriteAttributeString("ss", "AutoFitWidth", ss, myWorksheet.Table.Columns[J].AutoFitWidth.ToString());
_with1.WriteAttributeString("ss", "Width", ss, myWorksheet.Table.Columns[J].Width.ToString());
_with1.WriteEndElement();
}
for (int J = 0; J <= myWorksheet.Table.Rows.Count - 1; J++)
{
Row myRow = myWorksheet.Table.Rows[J];
_with1.WriteStartElement("Row");
_with1.WriteAttributeString("ss", "Index", ss, Convert.ToString(myRow.Index + 1));
_with1.WriteAttributeString("ss", "AutoFitHeight", ss, myRow.AutoFitHeight.ToString());
_with1.WriteAttributeString("ss", "Height", ss, myRow.Height.ToString());
if (!string.IsNullOrEmpty(myRow.StyleID))
_with1.WriteAttributeString("ss", "StyleID", ss, myRow.StyleID);
for (int K = 0; K <= myRow.Cells.Count - 1; K++)
{
_with1.WriteStartElement("Cell");
_with1.WriteAttributeString("ss", "Index", ss, myRow.Cells[K].Index.ToString());
if (!string.IsNullOrEmpty(myRow.Cells[K].StyleID))
_with1.WriteAttributeString("ss", "StyleID", ss, myRow.Cells[K].StyleID);
_with1.WriteAttributeString("ss", "MergeAcross", ss, myRow.Cells[K].MergeAcross.ToString());
_with1.WriteStartElement("Data");
_with1.WriteAttributeString("ss", "Type", ss, myRow.Cells[K].Type);
_with1.WriteValue(myRow.Cells[K].Value);
_with1.WriteEndElement();
//Data
_with1.WriteEndElement();
//Cell
}
_with1.WriteEndElement();
//Row
}
_with1.WriteEndElement();
//Table
_with1.WriteEndElement();
//Worksheet
//ADD WORKSHEET OPTIONS [TO BE ADDED IN THE FUTURE].
//sw.WriteLine(" <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">")
//sw.WriteLine(" <PageSetup>")
//sw.WriteLine(" <Header x:Margin=""0.3""/>")
//sw.WriteLine(" <Footer x:Margin=""0.3""/>")
//sw.WriteLine(" <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>")
//sw.WriteLine(" </PageSetup>")
//sw.WriteLine(" <Unsynced/>")
//sw.WriteLine(" <Print>")
//sw.WriteLine(" <ValidPrinterInfo/>")
//sw.WriteLine(" <HorizontalResolution>300</HorizontalResolution>")
//sw.WriteLine(" <VerticalResolution>300</VerticalResolution>")
//sw.WriteLine(" </Print>")
//sw.WriteLine(" <Selected/>")
//sw.WriteLine(" <Panes>")
//sw.WriteLine(" <Pane>")
//sw.WriteLine(" <Number>3</Number>")
//sw.WriteLine(" <ActiveRow>26</ActiveRow>")
//sw.WriteLine(" </Pane>")
//sw.WriteLine(" </Panes>")
//sw.WriteLine(" <ProtectObjects>False</ProtectObjects>")
//sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>")
//sw.WriteLine(" </WorksheetOptions>")
_with1.WriteEndElement();
//Workbook
_with1.WriteEndDocument();
_with1.Close();
}
}
}
}
您只是在工作表中的行中循环：
for (int J = 0; J <= myWorksheet.Table.Rows.Count - 1; J++)
您需要循环浏览Silverlight网格中的行。