这个比较实用,变化也很多的,该实例是动态增加表头,并且将必须的数据导出来作为一个EXCEL模板:
[DirectMethod]
public void btnSave_Click(string str)
{
//string AdminManagesURL = "ShowExcel.aspx?ClassId=" + Request.QueryString["ClassID"] + "&ClassCouse=" + str;
//LoadConfig AdminManagesConfig = new LoadConfig(AdminManagesURL, LoadMode.IFrame);
//FramePanel.LoadContent(AdminManagesConfig);
this.windowBook.Hidden = true;
try
{
//Server.MapPath(fileName)
GetExcle(str); //str是页面上通过JS获取的参数,在这里得到的是课程:语文,数学,物理
}
catch (Exception ex)
{
Ext.Net.Notification.Show(new NotificationConfig
{
Title = "温馨提示",
Icon = Icon.Information,
PinEvent = "mouseover",
Html = ex.Message
});
}
this.windowBook.Hidden = true;
}
这个是导出成为Excel
public void GetExcle(string str)
{
DataTable table = GetNewFellowExcelDataTable(str);
string fileName = string.Format("学生成绩导入模板{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel; charset=utf-8";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
HttpContext.Current.Response.Write(GetImportFailureStudentExcel(table, getTable(str)));
HttpContext.Current.Response.End();
}
private DataTable GetNewFellowExcelDataTable(string ClassCourse)
{
DataTable table = new DataTable("NewFellow");
table.Columns.Add("ColumnName", typeof(string)); //列名
table.Columns.Add("DataType", typeof(Type)); //数据类型
table.Columns.Add("IsMust", typeof(bool)); //是否是必填项
table.Columns.Add("IsList", typeof(bool)); //是否是列表
table.Columns.Add("ListValue", typeof(string)); //列表值
table.Columns.Add("HintText", typeof(string)); //提示
GetImportDataTable(ref table, "序号", typeof(string), false, false, string.Empty, string.Empty);
GetImportDataTable(ref table, "姓名", typeof(string), false, false, string.Empty, string.Empty);
GetImportDataTable(ref table, "学籍号", typeof(StringBuilder), false, false, string.Empty, string.Empty);
GetImportDataTable(ref table, "性别", typeof(string), false, false, string.Empty, string.Empty);
string[] strCourse = ClassCourse.TrimEnd(',').Split(',');
foreach (string str in strCourse)
{
if (str != "")
{
GetImportDataTable(ref table, str, typeof(string), false, false, string.Empty, string.Empty);
}
}
return table;
}
public static string GetImportFailureStudentExcel(DataTable sourceTable, DataTable failureTable)
{
//将要生成的Excel文件
StringBuilder sb = new StringBuilder();
sb.AppendLine("<?xml version=/"1.0/"?>");
sb.AppendLine("<?mso-application progid=/"Excel.Sheet/"?>");
//Excel工作薄开始
sb.AppendLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:o=/"urn:schemas-microsoft-com:office:office/" xmlns:x=/"urn:schemas-microsoft-com:office:excel/" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:html=/"http://www.w3.org/TR/REC-html40//">");
sb.AppendLine(" <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
sb.AppendLine(" <Created>" + DateTime.Now + "</Created>");
sb.AppendLine(" <Version>11.6408</Version>");
sb.AppendLine(" </DocumentProperties>");
//Excel工作薄结束
//工作薄样式
sb.AppendLine(" <Styles>");
sb.AppendLine(" <Style ss:ID=/"Default/" ss:Name=/"Normal/">");
sb.AppendLine(" <Alignment ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
sb.AppendLine(" <Borders>");
sb.AppendLine(" <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
sb.AppendLine(" <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
sb.AppendLine(" <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
sb.AppendLine(" <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
sb.AppendLine(" </Borders>");
sb.AppendLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/"/>");
sb.AppendLine(" <Interior />");
sb.AppendLine(" <Protection />");
sb.AppendLine(" </Style>");
sb.AppendLine(" <Style ss:ID=/"s21/">");
sb.AppendLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
sb.AppendLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#FF0000/" />");
sb.AppendLine(" </Style>");
sb.AppendLine(" <Style ss:ID=/"s22/">");
sb.AppendLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
sb.AppendLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#000000/" />");
sb.AppendLine(" </Style>");
sb.AppendLine(" <Style ss:ID=/"s23/">");
sb.AppendLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/" />");
sb.AppendLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#FF0000/" />");
sb.AppendLine(" <NumberFormat ss:Format=/"@/" />");
sb.AppendLine(" </Style>");
sb.AppendLine(" <Style ss:ID=/"s24/">");
sb.AppendLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/" />");
sb.AppendLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#000000/" />");
sb.AppendLine(" <NumberFormat ss:Format=/"@/" />");
sb.AppendLine(" </Style>");
//文本样式
sb.AppendLine(" <Style ss:ID=/"s25/">");
sb.AppendLine(" <NumberFormat ss:Format=/"@/"/>");
sb.AppendLine(" </Style>");
sb.AppendLine(" </Styles>");
//第i个工作表
sb.AppendLine(" <Worksheet ss:Name=/"" + sourceTable.TableName + "/">");
sb.AppendLine(" <Table ss:ExpandedColumnCount=/"" + sourceTable.Rows.Count + "/" ss:DefaultColumnWidth=/"100/">");
//ExpandedColumnCount:代表Excel文档中的列数
//ExpandedRowCount:代表Excel文档中的行数
//生成标题
StringBuilder validationString = new StringBuilder();
string listFormat = "<DataValidation xmlns=/"urn:schemas-microsoft-com:office:excel/"><Range>C{0}</Range><Type>List</Type><CellRangeList /><Value>"{1}"</Value></DataValidation>";
string dateFormat = "<DataValidation xmlns=/"urn:schemas-microsoft-com:office:excel/"><Range>C{0}</Range><Type>Date</Type><CellRangeList /><Min>1</Min><Max>402132</Max></DataValidation>";
string msgFormat = "<DataValidation xmlns=/"urn:schemas-microsoft-com:office:excel/"><Range>C{0}</Range><InputMessage>{1}</InputMessage></DataValidation>";
// 生成表头
StringBuilder cellString = new StringBuilder();
for (int i = 0; i < sourceTable.Rows.Count; i++)
{
DataRow row = sourceTable.Rows[i];
bool isMust = Boolean.TrueString.Equals(row["IsMust"].ToString()) ? true : false; //是否是必填项
string styleId = isMust ? "s21" : "s22";
if (Type.GetType(row["DataType"].ToString()).Equals(typeof(StringBuilder)))
{
styleId = isMust ? "s23" : "s24";
sb.AppendLine(string.Format("<Column ss:Index=/"{0}/" ss:StyleID=/"s25/" ss:AutoFitWidth=/"0/" ss:Width=/"120/"/>", i + 1));
}
cellString.AppendLine(" <Cell ss:StyleID=/"" + styleId + "/" ss:Index=/"" + (i + 1) + "/"><Data ss:Type=/"String/">" + row["ColumnName"] + "</Data></Cell>");
bool isList = Boolean.TrueString.Equals(row["IsList"].ToString()) ? true : false; //是否是列表
if (isList)
validationString.AppendFormat(listFormat, i + 1, row["ListValue"]);
if (Type.GetType(row["DataType"].ToString()).Equals(typeof(DateTime)))
validationString.AppendFormat(dateFormat, i + 1);
if (!string.IsNullOrEmpty(row["HintText"].ToString()))
validationString.AppendFormat(msgFormat, i + 1, row["HintText"]);
}
sb.AppendLine(" <Row ss:AutoFitHeight=/"0/" ss:Height=/"25/">");
sb.AppendLine(" " + cellString.ToString() + "");
sb.AppendLine(" </Row>");
// 生成表内容
foreach (DataRow row in failureTable.Rows)
{
sb.AppendLine(" <Row ss:AutoFitHeight=/"0/">");
foreach (DataRow sourceRow in sourceTable.Rows)
{
string coloumnName = sourceRow["ColumnName"].ToString();
string value = row[coloumnName].ToString();
if (Type.GetType(sourceRow["DataType"].ToString()).Equals(typeof(DateTime)))
{
DateTime sourceDate = DateTime.MinValue;
bool isDate = DateTime.TryParse(row[coloumnName].ToString(), out sourceDate);
value = isDate ? sourceDate.ToShortDateString() : row[coloumnName].ToString();
}
sb.AppendLine(string.Format("<Cell><Data ss:Type=/"String/">{0}</Data></Cell>", value));
}
sb.AppendLine(" </Row>");
}
sb.AppendLine(" </Table>");
sb.AppendLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
sb.AppendLine(" <Selected />");
sb.AppendLine(" <FreezePanes />");
sb.AppendLine(" <FrozenNoSplit />");
sb.AppendLine(" <SplitHorizontal>1</SplitHorizontal>");
sb.AppendLine(" <TopRowBottomPane>1</TopRowBottomPane>");
sb.AppendLine(" <ActivePane>2</ActivePane>");
sb.AppendLine(" <Panes>");
sb.AppendLine(" <Pane>");
sb.AppendLine(" <Number>3</Number>");
sb.AppendLine(" <ActiveRow>1</ActiveRow>");
sb.AppendLine(" </Pane>");
sb.AppendLine(" </Panes>");
sb.AppendLine(" <ProtectObjects>False</ProtectObjects>");
sb.AppendLine(" <ProtectScenarios>False</ProtectScenarios>");
sb.AppendLine(" </WorksheetOptions>");
sb.AppendLine(" " + validationString.ToString() + "");
sb.AppendLine(" </Worksheet>");
sb.AppendLine("</Workbook>");
return sb.ToString();
}
private void GetImportDataTable(ref DataTable table, string ColumnName, Type DataType, bool IsMust, bool IsList, string ListValue, string HintText)
{
DataRow row = table.NewRow();
row["ColumnName"] = ColumnName;
row["DataType"] = DataType;
row["IsMust"] = IsMust;
row["IsList"] = IsList;
row["ListValue"] = ListValue;
row["HintText"] = HintText;
table.Rows.Add(row);
}
public DataTable getTable(string ClassCourse)
{
string[] strCourse = ClassCourse.TrimEnd(',').Split(',');
DataTable dt = new DataTable();
dt.Columns.Add("序号");
dt.Columns.Add("姓名");
dt.Columns.Add("学籍号");
dt.Columns.Add("性别");
int count = 0;
foreach (string str in strCourse)
{
if (str != "")
{
dt.Columns.Add(str);
}
}
Student[] studentList = mrg.QueryUnEnroolStudentByClassId(new Guid(Request.QueryString["ClassID"]));
foreach (Student su in studentList)
{
DataRow dr = dt.NewRow();
dr[0] = (++count).ToString();
dr[1] = su.Name.ToString();
dr[2] = su.RegisterCode.ToString();
dr[3] = su.Sex == null ? "男" : su.Sex.Name.ToString();
dt.Rows.Add(dr);
}
return dt;
}
导入该模板,并且写入到XML中:
public void btnExport_Click(object sender, DirectEventArgs e)
{
string fileExt = Path.GetExtension(bfUploadFile.FileName);
if (fileExt != ".xls")
throw new ApplicationException("上传文件必须为.xls文件!");
DataTable dt = ExportExcel.GetStudentListByExcel(bfUploadFile.FileBytes);
XXT_SMSSpecialContent sc = BllAccess.Create_XXT_SMSSpecialContentBll().GetModel(new Guid(Request.QueryString["ID"].ToString()));
Student[] studentList = mrg.QueryUnEnroolStudentByClassId(new Guid(Request.QueryString["ClassID"].ToString()));
string strCouser = "";
for (int i = 4; i < dt.Columns.Count; i++)
{
strCouser =strCouser + dt.Columns[i] + ",";
}
try
{
// dt = ExcelToDS_manual(filePath,ref strCouser);
string[] strCList = strCouser.TrimEnd(',').Split(',');
#region
string str = "";
XmlDocument xmlDoc = new XmlDocument();
//建立Xml的定义声明
XmlDeclaration dec = xmlDoc.CreateXmlDeclaration("1.0", "GB2312", null);
xmlDoc.AppendChild(dec);
//创建根节点
XmlElement root = xmlDoc.CreateElement("Students");
xmlDoc.AppendChild(root);
foreach (DataRow dr in dt.Rows)
{
foreach (Student st in studentList)
{
if (dr[2].ToString().Trim() == st.RegisterCode.Trim())//空格 智v4_last_1 去掉科学计算法
{
XmlNode student = xmlDoc.CreateElement("Student");
XmlElement number = xmlDoc.CreateElement("Number");
number.InnerText = st.RegisterCode;
student.AppendChild(number);
XmlElement name = xmlDoc.CreateElement("Name");
name.InnerText = st.Name;
student.AppendChild(name);
XmlElement content = xmlDoc.CreateElement("Content");
int i = 0;
string strContentList = "";
foreach (string stC in strCList)
{
strContentList = strContentList + stC + ":" + dr[4 + (i++)].ToString() + ";";
}
content.InnerText = strContentList;
student.AppendChild(content);
XmlElement id = xmlDoc.CreateElement("ID");
id.InnerText = st.ID.ToString();
student.AppendChild(id);
root.AppendChild(student);
break;
}
}
}
sc.Content = xmlDoc.OuterXml;
BllAccess.Create_XXT_SMSSpecialContentBll().Update(sc);
#endregion
Ext.Net.Notification.Show(new NotificationConfig
{
Title = "温馨提示",
Icon = Icon.Information,
PinEvent = "mouseover",
Html = "导入学生成绩成功!"
});
AddStudentResult_Bind();
this.winDoRuExcel.Hidden = true;
}
catch (Exception ex)
{
Ext.Net.Notification.Show(new NotificationConfig
{
Title = "温馨提示",
Icon = Icon.Information,
PinEvent = "mouseover",
Html = "导入学生成绩失败!" + ex.Message
});
}
}
public static DataTable GetStudentListByExcel(byte[] xml_file)
{
MemoryStream stream = new MemoryStream(xml_file);
XmlNodeReader reader = null;
string rootValue = string.Empty;
DataTable table = new DataTable();
try
{
XmlDocument xd = new XmlDocument();
xd.Load(stream);
reader = new XmlNodeReader(xd); //创建新的XML reader
string nodeType = null;
DataRow row = null;
int rowIndex = 0;
int colIndex = 0;
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element:
nodeType = reader.Name;
if (nodeType.Equals("Row"))
{
rowIndex++;
colIndex = 0;
if (rowIndex > 1)
{
if (row != null)
table.Rows.Add(row);
}
row = null;
}
else if (nodeType.Equals("Cell"))
{
if (rowIndex > 1)
{
if (reader.GetAttribute("ss:Index") == null)
colIndex++;
else
{
if (colIndex == 0)
{
row = table.NewRow();
}
colIndex = Convert.ToInt32(reader.GetAttribute("ss:Index"));
}
}
}
break;
case XmlNodeType.Text:
if (nodeType.Equals("Data") || nodeType.Equals("ss:Data"))
{
rootValue = reader.Value.Replace("/r/n", "").Trim();
if (rowIndex == 1 && !table.Columns.Contains(rootValue))
table.Columns.Add(rootValue);
if (rowIndex > 1)
{
if (colIndex == 1)
row = table.NewRow();
row[colIndex - 1] = rootValue;
}
}
break;
case XmlNodeType.EndElement:
if (reader.Name.Equals("Table"))
{
if (row != null)
table.Rows.Add(row);
}
break;
}
}
}
catch
{
throw new ApplicationException("导入文件错误, 请下载最新导入模板");
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return table;
}
某些说明可能会在后续中解释!!!!!!!