private static XmlNode WriteCellContent(ExcelCotnent content, XmlNode rowNode, int row, int column
, int columnCount, string value, string styleId)
{
//sharedStrings
XmlNode sstNode = content.SharedStringsContent.DocumentElement;
XmlNode siNode = sstNode.AppendChild(content.SharedStringsContent.CreateElement("si", NamespaceUri));
XmlNode node = siNode.AppendChild(content.SharedStringsContent.CreateElement("t", NamespaceUri));
node.InnerText = value;
node = siNode.AppendChild(content.SharedStringsContent.CreateElement("phoneticPr", NamespaceUri));
XmlAttribute attr = node.Attributes.Append(content.SharedStringsContent.CreateAttribute("fontId"));
attr.Value = "1";
attr = node.Attributes.Append(content.SharedStringsContent.CreateAttribute("type"));
attr.Value = "noConversion";
node = rowNode.AppendChild(content.WorksheetsContent.CreateElement("c", NamespaceUri));
attr = node.Attributes.Append(content.WorksheetsContent.CreateAttribute("r"));
attr.Value = GetExcelCellName(row, column);
attr = node.Attributes.Append(content.WorksheetsContent.CreateAttribute("s"));
attr.Value = styleId;
attr = node.Attributes.Append(content.WorksheetsContent.CreateAttribute("t"));
attr.Value = "s";
node = node.AppendChild(content.WorksheetsContent.CreateElement("v", NamespaceUri));
node.InnerText = (row * columnCount + column).ToString();
return(siNode);
}
private static XmlNode WriteRowContent(ExcelCotnent content, XmlNode rowsNode, int index)
{
XmlNode rowNode = rowsNode.AppendChild(content.WorksheetsContent.CreateElement("row", NamespaceUri));
XmlAttribute attr = rowNode.Attributes.Append(content.WorksheetsContent.CreateAttribute("r"));
attr.Value = (index + 1).ToString();
return(rowNode);
}
private static bool SaveExcelFile(ExcelCotnent content, string fileName)
{
string workbookFile = GlobalMethods.IO.GetFilePath(fileName) + "\\workbook.xml";
if (!GlobalMethods.Xml.SaveXmlDocument(content.WorkbookContent, workbookFile))
{
return(false);
}
string sharedStringsFile = GlobalMethods.IO.GetFilePath(fileName) + "\\sharedStrings.xml";
if (!GlobalMethods.Xml.SaveXmlDocument(content.SharedStringsContent, sharedStringsFile))
{
return(false);
}
string worksheetsFile = GlobalMethods.IO.GetFilePath(fileName) + "\\worksheets.xml";
if (!GlobalMethods.Xml.SaveXmlDocument(content.WorksheetsContent, worksheetsFile))
{
return(false);
}
if (!GlobalMethods.IO.WriteFileBytes(fileName, Properties.Resources.ExportTemplet))
{
return(false);
}
try
{
ZipFile zipFile = new ZipFile(fileName);
zipFile.BeginUpdate();
zipFile.Add(workbookFile, "xl/workbook.xml");
zipFile.Add(sharedStringsFile, "xl/sharedStrings.xml");
zipFile.Add(worksheetsFile, "xl/worksheets/sheet1.xml");
zipFile.CommitUpdate();
zipFile.Close();
return(true);
}
catch (Exception ex)
{
LogManager.Instance.WriteLog("GlobalMethods.SaveExcelFile"
, new string[] { "fileName" }, new string[] { fileName }, ex);
return(false);
}
finally
{
GlobalMethods.IO.DeleteFile(workbookFile);
GlobalMethods.IO.DeleteFile(sharedStringsFile);
GlobalMethods.IO.DeleteFile(worksheetsFile);
}
}
private static bool WriteWorksheetName(ExcelCotnent content, string name)
{
if (content.WorkbookContent == null)
{
return(false);
}
string xpath = "/ns:workbook/ns:sheets/ns:sheet[@name='Sheet1']";
XmlNode sheetNode = content.WorkbookContent.SelectSingleNode(xpath, NamespaceManager);
sheetNode.Attributes["name"].Value = name;
return(true);
}
private static ExcelCotnent GetTempletContent()
{
ExcelCotnent content = new ExcelCotnent();
MemoryStream stream = new MemoryStream(Properties.Resources.ExportTemplet);
ZipInputStream zipstream = new ZipInputStream(stream);
try
{
Heren.Common.ZipLib.Zip.ZipEntry entry = zipstream.GetNextEntry();
while (entry != null)
{
if (entry.Name == "xl/workbook.xml")
{
content.WorkbookContent = GlobalMethods.Xml.GetXmlDocument(zipstream);
}
else if (entry.Name == "xl/sharedStrings.xml")
{
content.SharedStringsContent = GlobalMethods.Xml.GetXmlDocument(zipstream);
}
else if (entry.Name == "xl/worksheets/sheet1.xml")
{
content.WorksheetsContent = GlobalMethods.Xml.GetXmlDocument(zipstream);
}
entry = zipstream.GetNextEntry();
}
XmlNode columnsNode = GetColumnsNode(content);
columnsNode.RemoveAll();
XmlNode rowsNode = GetRowsNode(content);
rowsNode.RemoveAll();
content.SharedStringsContent.DocumentElement.RemoveAll();
return(content);
}
catch (Exception ex)
{
LogManager.Instance.WriteLog("GlobalMethods.ExportExcelFile", ex);
return(null);
}
finally
{
zipstream.Close();
zipstream.Dispose();
stream.Close();
stream.Dispose();
}
}
private static XmlNode WriteColumnContent(ExcelCotnent content, XmlNode columnsNode, int column
, string name, int width, string styleId)
{
//worksheet
XmlNode columnNode = columnsNode.AppendChild(content.WorksheetsContent.CreateElement("col", NamespaceUri));
XmlAttribute attr = columnNode.Attributes.Append(content.WorksheetsContent.CreateAttribute("min"));
attr.Value = (column + 1).ToString();
attr = columnNode.Attributes.Append(content.WorksheetsContent.CreateAttribute("max"));
attr.Value = (column + 1).ToString();
attr = columnNode.Attributes.Append(content.WorksheetsContent.CreateAttribute("width"));
attr.Value = (GlobalMethods.Convert.Pixel2MM(width, false) / 2.54f).ToString();
attr = columnNode.Attributes.Append(content.WorksheetsContent.CreateAttribute("customWidth"));
attr.Value = "1";
return(columnNode);
}
/// <summary>
/// 将指定表格导出为Excel文件
/// </summary>
/// <param name="table">DataTable</param>
/// <param name="fileName">Excel文件名</param>
/// <returns>是否成功</returns>
public static bool ExportExcelFile(DataTable table, string fileName)
{
ExcelCotnent content = GetTempletContent();
if (content == null || content.WorkbookContent == null ||
content.WorksheetsContent == null || content.SharedStringsContent == null)
{
return(false);
}
WriteWorksheetName(content, GlobalMethods.IO.GetFileName(fileName, false));
XmlNode columnsNode = GetColumnsNode(content);
XmlNode rowsNode = GetRowsNode(content);
XmlNode rowNode = WriteRowContent(content, rowsNode, 0);
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
DataColumn column = table.Columns[columnIndex];
string columnName = column.ColumnName;
if (string.IsNullOrEmpty(columnName))
{
columnName = string.Concat("Column", columnIndex + 1);
}
WriteColumnContent(content, columnsNode, columnIndex, columnName, 100, "1");
WriteCellContent(content, rowNode, 0, columnIndex, table.Columns.Count, columnName, "1");
}
for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
{
DataRow row = table.Rows[rowIndex];
rowNode = WriteRowContent(content, rowsNode, rowIndex + 1);
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
string value = row.IsNull(columnIndex) ? string.Empty : row[columnIndex].ToString();
WriteCellContent(content, rowNode, rowIndex + 1, columnIndex, table.Columns.Count, value, "2");
}
}
return(SaveExcelFile(content, fileName));
}
/// <summary>
/// 将指定表格导出为Excel文件
/// </summary>
/// <param name="gridView">DataGridView</param>
/// <param name="onlySelected">是否仅选中内容</param>
/// <param name="fileName">Excel文件名</param>
/// <returns>是否成功</returns>
public static bool ExportExcelFile(DataTableView gridView, bool onlySelected, string fileName)
{
if (gridView == null || string.IsNullOrEmpty(fileName) ||
gridView.Rows.Count <= 0 || gridView.Columns.Count <= 0)
{
return(false);
}
int rowCount = gridView.RowCount, startRow = 0, endRow = rowCount - 1;
int columnCount = gridView.ColumnCount, startColumn = 0, endColumn = columnCount - 1;
if (onlySelected)
{
if (gridView.SelectionMode == DataGridViewSelectionMode.FullRowSelect)
{
if (gridView.SelectedRows.Count <= 0)
{
return(false);
}
startRow = gridView.SelectedRows[gridView.SelectedRows.Count - 1].Index;
endRow = gridView.SelectedRows[0].Index;
if (startRow > endRow)
{
startRow = gridView.SelectedRows[0].Index;
endRow = gridView.SelectedRows[gridView.SelectedRows.Count - 1].Index;
}
rowCount = endRow - startRow + 1;
}
else if (gridView.SelectionMode == DataGridViewSelectionMode.FullColumnSelect)
{
if (gridView.SelectedColumns.Count <= 0)
{
return(false);
}
startColumn = gridView.SelectedColumns[gridView.SelectedColumns.Count - 1].Index;
endColumn = gridView.SelectedColumns[0].Index;
if (startColumn > endColumn)
{
startColumn = gridView.SelectedColumns[0].Index;
endColumn = gridView.SelectedColumns[gridView.SelectedColumns.Count - 1].Index;
}
columnCount = endColumn - startColumn + 1;
}
}
ExcelCotnent content = GetTempletContent();
if (content == null || content.WorkbookContent == null ||
content.WorksheetsContent == null || content.SharedStringsContent == null)
{
return(false);
}
WriteWorksheetName(content, GlobalMethods.IO.GetFileName(fileName, false));
XmlNode columnsNode = GetColumnsNode(content);
XmlNode rowsNode = GetRowsNode(content);
XmlNode rowNode = null;
int excelRowIndex = -1;
//添加mergeCell
if (gridView.Groups.Count > 0)
{
XmlElement groupsNode = content.WorksheetsContent.CreateElement("mergeCells", NamespaceUri);
groupsNode.SetAttribute("count", gridView.Groups.Count.ToString());
for (int index = 0; index < gridView.Groups.Count; index++)
{
XmlElement groupNode = content.WorksheetsContent.CreateElement("mergeCell", NamespaceUri);
string beginCellName = GetExcelCellName(0, gridView.Groups[index].BeginColumn);
string endCellName = GetExcelCellName(0, gridView.Groups[index].EndColumn);
groupNode.SetAttribute("ref", string.Format("{0}:{1}", beginCellName, endCellName));
groupsNode.AppendChild(groupNode);
}
content.WorksheetsContent.DocumentElement.InsertAfter(groupsNode, rowsNode);
//添加首行表头
++excelRowIndex;
rowNode = WriteRowContent(content, rowsNode, excelRowIndex);
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
if (string.IsNullOrEmpty(gridView.Columns[columnIndex].Name))
{
continue;
}
bool isGroupColumn = false;
for (int groupIndex = 0; groupIndex < gridView.Groups.Count; groupIndex++)
{
int nBeginColumn = gridView.Groups[groupIndex].BeginColumn;
int nEndColumn = gridView.Groups[groupIndex].EndColumn;
string value = gridView.Groups[groupIndex].Text;
if (columnIndex == nBeginColumn)
{
WriteCellContent(content, rowNode, excelRowIndex, columnIndex, columnCount, value, "3");
isGroupColumn = true;
}
else if (columnIndex > nBeginColumn && columnIndex <= nEndColumn)
{
WriteCellContent(content, rowNode, excelRowIndex, columnIndex, columnCount, string.Empty, "3");
isGroupColumn = true;
}
}
if (!isGroupColumn)
{
WriteCellContent(content, rowNode, excelRowIndex, columnIndex, columnCount, string.Empty, "1");
}
}
}
//添加第二表头
++excelRowIndex;
rowNode = WriteRowContent(content, rowsNode, excelRowIndex);
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
DataGridViewColumn column = gridView.Columns[startColumn + columnIndex];
string columnName = column.HeaderText;
if (string.IsNullOrEmpty(columnName))
{
columnName = string.Concat("Column", columnIndex + 1);
}
WriteColumnContent(content, columnsNode, columnIndex, columnName, column.Width + 24, "1");
WriteCellContent(content, rowNode, excelRowIndex, columnIndex, columnCount, columnName, "1");
}
//添加数据行
++excelRowIndex;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
DataGridViewRow row = gridView.Rows[startRow + rowIndex];
if (row.IsNewRow)
{
continue;
}
rowNode = WriteRowContent(content, rowsNode, excelRowIndex + rowIndex);
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
DataGridViewColumn column = gridView.Columns[startColumn + columnIndex];
string value = row.Cells[column.Index].Value == null ? string.Empty : row.Cells[column.Index].Value.ToString();
WriteCellContent(content, rowNode, excelRowIndex + rowIndex, columnIndex, columnCount, value, "2");
}
}
return(SaveExcelFile(content, fileName));
}
/// <summary>
/// 将指定表格导出为Excel文件
/// </summary>
/// <param name="gridView">DataGridView</param>
/// <param name="onlySelected">是否仅选中内容</param>
/// <param name="fileName">Excel文件名</param>
/// <returns>是否成功</returns>
public static bool ExportExcelFile(DataGridView gridView, bool onlySelected, string fileName)
{
if (gridView == null || string.IsNullOrEmpty(fileName) ||
gridView.Rows.Count <= 0 || gridView.Columns.Count <= 0)
{
return(false);
}
int rowCount = gridView.RowCount, startRow = 0, endRow = rowCount - 1;
int columnCount = gridView.ColumnCount, startColumn = 0, endColumn = columnCount - 1;
if (onlySelected)
{
if (gridView.SelectionMode == DataGridViewSelectionMode.FullRowSelect)
{
if (gridView.SelectedRows.Count <= 0)
{
return(false);
}
startRow = gridView.SelectedRows[gridView.SelectedRows.Count - 1].Index;
endRow = gridView.SelectedRows[0].Index;
if (startRow > endRow)
{
startRow = gridView.SelectedRows[0].Index;
endRow = gridView.SelectedRows[gridView.SelectedRows.Count - 1].Index;
}
rowCount = endRow - startRow + 1;
}
else if (gridView.SelectionMode == DataGridViewSelectionMode.FullColumnSelect)
{
if (gridView.SelectedColumns.Count <= 0)
{
return(false);
}
startColumn = gridView.SelectedColumns[gridView.SelectedColumns.Count - 1].Index;
endColumn = gridView.SelectedColumns[0].Index;
if (startColumn > endColumn)
{
startColumn = gridView.SelectedColumns[0].Index;
endColumn = gridView.SelectedColumns[gridView.SelectedColumns.Count - 1].Index;
}
columnCount = endColumn - startColumn + 1;
}
}
ExcelCotnent content = GetTempletContent();
if (content == null || content.WorkbookContent == null ||
content.WorksheetsContent == null || content.SharedStringsContent == null)
{
return(false);
}
WriteWorksheetName(content, GlobalMethods.IO.GetFileName(fileName, false));
XmlNode columnsNode = GetColumnsNode(content);
XmlNode rowsNode = GetRowsNode(content);
XmlNode rowNode = WriteRowContent(content, rowsNode, 0);
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
DataGridViewColumn column = gridView.Columns[startColumn + columnIndex];
string columnName = column.HeaderText;
if (string.IsNullOrEmpty(columnName))
{
columnName = string.Concat("Column", columnIndex + 1);
}
WriteColumnContent(content, columnsNode, columnIndex, columnName, column.Width + 24, "1");
WriteCellContent(content, rowNode, 0, columnIndex, columnCount, columnName, "1");
}
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
DataGridViewRow row = gridView.Rows[startRow + rowIndex];
if (row.IsNewRow)
{
continue;
}
rowNode = WriteRowContent(content, rowsNode, rowIndex + 1);
for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
DataGridViewColumn column = gridView.Columns[startColumn + columnIndex];
string columnName = column.HeaderText;
if (string.IsNullOrEmpty(columnName))
{
columnName = string.Concat("Column", columnIndex + 1);
}
string value = row.Cells[column.Index].Value == null ? string.Empty : row.Cells[column.Index].Value.ToString();
WriteCellContent(content, rowNode, rowIndex + 1, columnIndex, columnCount, value, "2");
}
}
return(SaveExcelFile(content, fileName));
}
private static XmlNode GetColumnsNode(ExcelCotnent content)
{
return(content.WorksheetsContent.SelectSingleNode("/ns:worksheet/ns:cols", NamespaceManager));
}
private static XmlNode GetGroupsNode(ExcelCotnent content)
{
return(content.WorksheetsContent.SelectSingleNode("/ns:worksheet/ns:mergeCells", NamespaceManager));
}
