/// <summary>
/// 导出数据到xml格式的Excel
/// </summary>
public bool ExportToExcel(string filename,ref string errmsg)
{
bool r = false;
CUM_UserInfoService bll = new CUM_UserInfoService();
int count = bll.GetUserCount(" and User_IsAdmin=0");
if (count == 0)
{
errmsg = "没有用户记录,无法导出";
return false;
}
int rcount=0;
IList<CUM_UserInfoBM> list = bll.GetUserInfoList(count, 1, " and User_IsAdmin=0", out rcount);
if (list == null || list.Count == 0)
{
errmsg = "没有用户记录,无法导出";
return false;
}
DateTime dtime = DateTime.Now;
try
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\"?>\r\n");
sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\r\n");
sb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n");
sb.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n");
sb.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
sb.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n");
sb.Append(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n");
sb.Append(" <Author>zifar-computer</Author>\r\n");
sb.Append(" <LastAuthor>zifar-computer</LastAuthor>\r\n");
sb.Append(" <Created>" + dtime.ToString("yyyy-MM-dd") + "T" + dtime.ToString("HH:mm:ss") + "Z</Created>\r\n");
sb.Append(" <Company>zifar.com</Company>\r\n");
sb.Append(" <Version>11.5606</Version>\r\n");
sb.Append(" </DocumentProperties>\r\n");
sb.Append(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
sb.Append(" <WindowHeight>13680</WindowHeight>\r\n");
sb.Append(" <WindowWidth>24795</WindowWidth>\r\n");
sb.Append(" <WindowTopX>480</WindowTopX>\r\n");
sb.Append(" <WindowTopY>60</WindowTopY>\r\n");
sb.Append(" <ProtectStructure>False</ProtectStructure>\r\n");
sb.Append(" <ProtectWindows>False</ProtectWindows>\r\n");
sb.Append(" </ExcelWorkbook>\r\n");
sb.Append(" <Styles>\r\n");
sb.Append(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n");
sb.Append(" <Alignment ss:Vertical=\"Center\"/>\r\n");
sb.Append(" <Borders/>\r\n");
sb.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\r\n");
sb.Append("<Interior/>\r\n");
sb.Append("<NumberFormat/>\r\n");
sb.Append(" <Protection/>\r\n");
sb.Append(" </Style>\r\n");
sb.Append("<Style ss:ID=\"s21\">\r\n");
sb.Append(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>\r\n");
sb.Append(" <Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>\r\n");
sb.Append(" </Style>\r\n");
sb.Append(" <Style ss:ID=\"s22\">\r\n");
sb.Append(" <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
sb.Append("</Style>");
sb.Append(" </Styles>\r\n");
sb.Append(" <Worksheet ss:Name=\"Sheet1\">\r\n");
sb.Append(" <Table ss:ExpandedColumnCount=\"23\" ss:ExpandedRowCount=\"" + (rcount+1) + "\" x:FullColumns=\"1\"\r\n");
sb.Append(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">\r\n");
sb.Append(" <Column ss:Width=\"57\" ss:Span=\"5\"/>\r\n");
sb.Append(" <Column ss:Index=\"7\" ss:Width=\"33\"/>\r\n");
sb.Append(" <Column ss:Width=\"57\"/>\r\n");
sb.Append(" <Column ss:Width=\"33\"/>\r\n");
sb.Append(" <Column ss:Width=\"57\"/>\r\n");
sb.Append(" <Column ss:Width=\"33\"/>\r\n");
sb.Append(" <Column ss:Width=\"57\" ss:Span=\"3\"/>\r\n");
sb.Append(" <Column ss:Index=\"16\" ss:Width=\"83.25\"/>\r\n");
sb.Append(" <Column ss:Width=\"33\"/>\r\n");
sb.Append(" <Column ss:Width=\"57\" ss:Span=\"3\"/>\r\n");
sb.Append(" <Column ss:Index=\"22\" ss:Width=\"33\" ss:Span=\"1\"/>\r\n");
sb.Append(" <Row ss:AutoFitHeight=\"0\">\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">用户姓名</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">人员工号</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">身证证号</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">手机号码</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">是否启用</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">所属部门</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">职务</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">出生日期</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">年龄</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">办公电话</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">职称</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">文化程度</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">婚姻状况</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">政治面貌</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">电子邮箱</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">参加工作时间</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">工龄</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">毕业学校</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">所修专业</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">毕业时间</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">家庭住址</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">民族</Data></Cell>\r\n");
sb.Append(" <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">性别</Data></Cell>\r\n");
sb.Append(" </Row>\r\n");
int rowcount = 0;
string style = "";
foreach (CUM_UserInfoBM bm in list)
{
if (rowcount % 2 == 0)
{
style = " ss:StyleID=\"s22\"";
}
else
{
style = "";
}
sb.Append(" <Row ss:AutoFitHeight=\"0\">\r\n");
sb.Append(" <Cell"+style+"><Data ss:Type=\"String\">" + (bm.User_Name == null ? " " : bm.User_Name) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_No == null ? " " : bm.User_No) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_IDCard == null ? " " : bm.User_IDCard) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Mobile == null ? " " : bm.User_Mobile) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_ISvalid == 1 ? "是" : "否") + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + GetOrgNameByOrgID(bm.User_Dept) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Job == null ? " " : bm.User_Job) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + GetDateStr(bm.User_Born) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + bm.User_Age.ToString() + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_DeptTel == null ? " " : bm.User_DeptTel) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Title == null ? " " : bm.User_Title) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Diploma == null ? " " : bm.User_Diploma) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Mar == null ? " " : bm.User_Mar) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Polity == null ? " " : bm.User_Polity) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Email == null ? " " : bm.User_Email) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + GetDateStr(bm.User_WorkDate) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + bm.User_WorkYear.ToString() + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_School == null ? " " : bm.User_School) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Profession == null ? " " : bm.User_Profession) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + GetDateStr(bm.User_FishDate) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_FamAddress == null ? " " : bm.User_FamAddress) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Nation == null ? " " : bm.User_Nation) + "</Data></Cell>\r\n");
sb.Append(" <Cell" + style + "><Data ss:Type=\"String\">" + (bm.User_Sex == null ? " " : bm.User_Sex) + "</Data></Cell>\r\n");
sb.Append(" </Row>\r\n");
rowcount++;
}
sb.Append(" </Table>\r\n");
sb.Append(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
sb.Append(" <Unsynced/>\r\n");
sb.Append(" <Print>\r\n");
sb.Append(" <ValidPrinterInfo/>\r\n");
sb.Append(" <PaperSizeIndex>9</PaperSizeIndex>\r\n");
sb.Append(" <HorizontalResolution>300</HorizontalResolution>\r\n");
sb.Append(" <VerticalResolution>300</VerticalResolution>\r\n");
sb.Append(" </Print>\r\n");
sb.Append(" <Selected/>\r\n");
sb.Append(" <Panes>\r\n");
sb.Append(" <Pane>\r\n");
sb.Append(" <Number>3</Number>\r\n");
sb.Append(" <ActiveRow>6</ActiveRow>\r\n");
sb.Append(" <ActiveCol>11</ActiveCol>\r\n");
sb.Append(" </Pane>\r\n");
sb.Append(" </Panes>\r\n");
sb.Append(" <ProtectObjects>False</ProtectObjects>\r\n");
sb.Append(" <ProtectScenarios>False</ProtectScenarios>\r\n");
sb.Append(" </WorksheetOptions>\r\n");
sb.Append(" </Worksheet>\r\n");
sb.Append("</Workbook>");
IOHelper.SaveFile(sb.ToString(), filename, Encoding.UTF8, false);
r = true;
}
catch (Exception ex)
{
errmsg = ex.Message;
}
finally
{
}
return r;
}
导出的xml默认就是用excel打开的,导出结果如图: