private void CreateReport2(IDictionary idc, string rptStyle)
{
var sp1 = new SpreadsheetClass();
var doc = new XmlDocument();
doc.LoadXml(rptStyle);
var xn = new XmlNamespaceManager(doc.NameTable);
xn.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var nodes = doc.SelectNodes("//ss:Data", xn);
//根据key替换数据
foreach (XmlNode node in nodes)
{
var tmpValue = node.InnerText.Trim();
if (tmpValue.Length > 2 && tmpValue.Substring(0, 1) == "♂")
{
node.InnerText = idc.Contains(tmpValue) ? idc[tmpValue].ToString() : string.Empty;
//style = style.Replace(tmpValue, node.InnerText);
}
}
//sp1.XMLData = style;
sp1.XMLData = doc.OuterXml;
var fileName = "e:\\rpt.xls"; //可以考虑把保存文件路径写成配置的
sp1.Export(fileName, SheetExportActionEnum.ssExportActionOpenInExcel);
}
public void UpdateEntity(SpreadsheetClass sheetEntities)
{
foreach (GoogleSheet item in sheetEntities.Servers)
{
string range = $"{item.serverName}!A1:D";
ValueRange requestBody = new ValueRange();
requestBody.Values = new List <IList <object> >();
foreach (var itm in item.dataBases)
{
List <object> objectList = new List <object>();
objectList.Add(item.serverName);
objectList.Add(itm.Name);
objectList.Add(itm.Size);
objectList.Add(item.lastUpdate);
requestBody.Values.Add(objectList);
}
List <object> schemaInfo = new List <object>();
schemaInfo.Add(item.serverName);
schemaInfo.Add("Свободно");
schemaInfo.Add($"{Math.Round(item.serverSpace - item.CurrentSize(), 2)}");
schemaInfo.Add(DateTime.Now.ToString("D"));
requestBody.Values.Add(schemaInfo);
var appendRequest = service.Spreadsheets.Values.Append(requestBody, sheetEntities.SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
}
}
private void CreateReport(IDictionary idc, string rptStyle)
{
var sp1 = new SpreadsheetClass();
var doc = new XmlDocument();
doc.LoadXml(rptStyle);
var xn = new XmlNamespaceManager(doc.NameTable);
xn.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var nodes = doc.SelectNodes("//ss:Data", xn);
//根据key替换数据
foreach (XmlNode node in nodes)
{
var tmpValue = node.InnerText.Trim();
if (tmpValue.Length > 2 && tmpValue.Substring(0, 1) == "♂")
{
string val = tmpValue.Substring(1);
node.InnerText = idc.Contains(val) ? idc[val].ToString() : string.Empty;
rptStyle = rptStyle.Replace(tmpValue, node.InnerText);
}
}
//sp1.XMLData = style;
sp1.XMLData = doc.OuterXml;
//dvShow.InnerHtml = sp1.HTMLData;
sp1.XMLData = sp1.XMLData.ToString().Replace("<ss:Table", "<ss:Table ss:id=\"CX\"");
Response.Write(sp1.HTMLData);
}
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
excelHtml = CDSAdomd.GetExcelHtml(CDSAdomd.GetCellSet("Provider=MSOLAP; Datasource=localhost; Initial Catalog=PsiMedicaTestCubes;", "SELECT {[Measures].[Episodes], [Measures].[Payment], [Measures].[Payment per Episode]} ON COLUMNS, {[Primary Diagnosis].[All Primary Diagnosis]} ON ROWS FROM VCEpisodes"));
OWC.Spreadsheet s = new SpreadsheetClass();
s.HTMLData = CDSAdomd.GetExcelHtml(CDSAdomd.GetCellSet("Provider=MSOLAP; Datasource=localhost; Initial Catalog=PsiMedicaTestCubes;", "SELECT {[Measures].[Episodes], [Measures].[Payment], [Measures].[Payment per Episode]} ON COLUMNS, {[Primary Diagnosis].[All Primary Diagnosis]} ON ROWS FROM VCEpisodes"));
}
public static void DataGrid2Excel(DataGrid dg, string filename)
{
OWC.SpreadsheetClass xlsheet = new SpreadsheetClass();
int ihead = 0;
foreach (DataGridColumn dgc in dg.Columns)
{
xlsheet.ActiveSheet.Cells[1, ihead + 1] = dgc.HeaderText;
// xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]).Font.Bold = true;
// xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]).Font.Color = "red";
ihead++;
}
int icols = dg.Items[0].Cells.Count;
for (int j = 0; j < dg.Items.Count; j++)
{
for (int i = 0; i < icols; i++)
{
xlsheet.ActiveSheet.Cells[j + 2, i + 1] = dg.Items[j].Cells[i].Text.Replace(" ", " ");
}
}
try
{
//System.Web.HttpContext.Current.Server.MapPath
xlsheet.ActiveSheet.Export(filename, OWC.SheetExportActionEnum.ssExportActionOpenInExcel);
}
catch
{
return;
}
}
protected void Page_Load(object sender, EventArgs e)
{
//创建图表控件
ChartSpace laySpace = new ChartSpace();
//添加一个表容器
SpreadsheetClass myexcel = new SpreadsheetClass();
Worksheet mysheet = myexcel.ActiveSheet;
//添加表标题
myexcel.Cells[1, 1] = "籍贯";
myexcel.Cells[1, 2] = "人数";
//连接数据库
String str = ConfigurationManager.ConnectionStrings["connection"].ConnectionString.ToString();
SqlConnection con = new SqlConnection(str);
con.Open();
String sel = "select jiguan,count(jiguan) as number from xx group by jiguan";
SqlDataAdapter adsa = new SqlDataAdapter(sel, con);
DataSet adds = new DataSet();
adsa.Fill(adds);
if (adds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < adds.Tables[0].Rows.Count; i++)
{
mysheet.Cells[i + 2, 1] = adds.Tables[0].Rows[i][0].ToString();
mysheet.Cells[i + 2, 2] = adds.Tables[0].Rows[i][1].ToString();
}
//导出表格
myexcel.Export(Server.MapPath(".") + @"\test.xls", SheetExportActionEnum.ssExportActionOpenInExcel, SheetExportFormat.ssExportXMLSpreadsheet);
}
con.Close();
}
static void Main(string[] args)
{
spreadsheet = google.CreateSheet(spreadsheet);
Console.WriteLine(spreadsheet.SpreadsheetId);
SetTimer();
Console.ReadKey();
}
private void CreateReport(IDictionary idc, string rptStyle)
{
var sp1 = new SpreadsheetClass();
var doc = new XmlDocument();
doc.LoadXml(rptStyle);
var xn = new XmlNamespaceManager(doc.NameTable);
xn.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var nodes = doc.SelectNodes("//ss:Data", xn);
//根据key替换数据
foreach (XmlNode node in nodes)
{
var tmpValue = node.InnerText.Trim();
if (tmpValue.Length > 2 && tmpValue.Substring(0, 1) == "♂")
{
string val = tmpValue.Substring(1);
node.InnerText = idc.Contains(val) ? idc[val].ToString() : string.Empty;
rptStyle = rptStyle.Replace(tmpValue, node.InnerText);
}
}
//sp1.XMLData = style;
sp1.XMLData = doc.OuterXml;
//dvShow.InnerHtml = sp1.HTMLData;
sp1.XMLData = sp1.XMLData.ToString().Replace("<ss:Table", "<ss:Table ss:id=\"CX\"");
Response.Write(sp1.HTMLData);
}
public Config()
{
using (StreamReader r = new StreamReader("settings.json"))
{
string json = r.ReadToEnd();
this.Settings = (SpreadsheetClass)JsonConvert.DeserializeObject(json, typeof(SpreadsheetClass));
}
}
// 导出签到名单到Excel文件
public static int exportSignupList(String excelFile, Dictionary <String, String[]> conditions)
{
try
{
List <QuestionItem> listQuestions;
List <SignupUserItem> list;
DbSelect.getQuestionList(out listQuestions, null);
// 读取签到名单
DbSelect.getSignupList(out list, conditions);
// 生成Excel文件
SpreadsheetClass factory = new SpreadsheetClass();
Worksheet sheet = factory.ActiveSheet;
List <string> columnHeaderNames = new List <string> {
"签到编号", "姓名", "身份证号码", "手机号码", "签到时间"
};
List <int> columnHeaderWidth = new List <int> {
10, 15, 12, 15
};
int columnHeaderQuestionWidth = 42;
listQuestions.ForEach((QuestionItem q) =>
{
columnHeaderNames.Add(q.Question);
columnHeaderWidth.Add(columnHeaderQuestionWidth);
});
sheet.Name = String.Format("签到名单({0}.{1})", DateTime.Today.Year, DateTime.Today.Month.ToString("00"));
// 字段名
for (int i = 0; i < columnHeaderNames.Count; i++)
{
Range col = sheet.get_Range((char)('A' + i) + "1");
sheet.Cells[1, i + 1] = columnHeaderNames[i];
col.set_ColumnWidth(columnHeaderWidth[i]);
col.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
col.Font.set_Bold(true);
}
// 记录
for (int i = 0, ROW_OFFSET = 2, COL_OFFSET = 1; i < list.Count; i++)
{
sheet.Cells[ROW_OFFSET + i, COL_OFFSET] = list[i].AssignID;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 1] = list[i].Name;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 2] = '\'' + list[i].CardID;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 3] = '\'' + list[i].Mobile;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 4] = list[i].SignupTime.ToString();
if (list[i].Answers.Count == listQuestions.Count)
{
for (int j = 0; j < listQuestions.Count; j++)
{
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + j + 4] = list[i].Answers[j].Answer;
}
}
}
factory.Export(excelFile, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
return(OK);
}
catch (Exception ex)
{
throw new Exception(String.Format("导出签到名单时出错：{0}", ex.Message));
}
}
public void TestRowColumnCountMethods()
{
Assert.AreEqual(100, this.dataBase.ColumnCount());
Assert.AreEqual(100, this.dataBase.RowCount());
this.dataBase = new SpreadsheetClass(10, 100);
Assert.AreEqual(100, this.dataBase.ColumnCount());
Assert.AreEqual(10, this.dataBase.RowCount());
}
private void CreateReport(IDictionary idc, string rptName, string treeId, string orgId)
{
var bll = new BLL.BLLReport();
//var styleObj = bll.GetRptStyleByName(rptName);
var styleObj = bll.GetRptStyle(rptName, treeId, orgId);
var style = styleObj.RptStyle;
if (!string.IsNullOrEmpty(style))
{
var sp1 = new SpreadsheetClass();
var doc = new XmlDocument();
doc.LoadXml(style);
var xn = new XmlNamespaceManager(doc.NameTable);
xn.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var nodes = doc.SelectNodes("//ss:Data", xn);
//根据key替换数据
foreach (XmlNode node in nodes)
{
var tmpValue = node.InnerText.Trim();
if (tmpValue.Length > 2 && tmpValue.Substring(0, 1) == "♂")
{
node.InnerText = idc.Contains(tmpValue) ? idc[tmpValue].ToString() : string.Empty;
//style = style.Replace(tmpValue, node.InnerText);
}
}
//sp1.XMLData = style;
sp1.XMLData = doc.OuterXml;
//dvShow.InnerHtml = sp1.HTMLData;
sp1.XMLData = sp1.XMLData.Replace("ss:Table", "ss:Table ss:id=\"CX\"");
Response.Write(sp1.HTMLData);
}
else
{
//Response.Write("<script>alert('set style first.')</script>");
//dvShow.InnerHtml = "报表样式未设置，请先设置报表样式";
Response.Write("<span>请先设置报表样式<span>");
}
}
public SpreadsheetClass CreateSheet(SpreadsheetClass sheetEntities)
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.Properties = new SpreadsheetProperties();
spreadsheet.Properties.Title = "Google Sheets Exm";
List <Sheet> sheets = new List <Sheet>();
foreach (GoogleSheet item in sheetEntities.Servers)
{
Sheet sheet = new Sheet();
sheet.Properties = new SheetProperties();
sheet.Properties.Title = item.serverName;
sheets.Add(sheet);
}
spreadsheet.Sheets = sheets;
SpreadsheetsResource.CreateRequest request = this.service.Spreadsheets.Create(spreadsheet);
var x = request.Execute();
sheetEntities.SpreadsheetId = x.SpreadsheetId;
return(sheetEntities);
}
/// <summary>
/// Method to load spreadsheet from XML file
/// present at specified location.
/// </summary>
/// <param name="sender"> tool strip menu item.</param>
/// <param name="e"> parameter arguments.</param>
private void LoadXMLFileToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog browserDialog = new OpenFileDialog();
browserDialog.DefaultExt = "xml";
DialogResult result = browserDialog.ShowDialog();
if (result == DialogResult.OK && browserDialog.CheckFileExists == true)
{
// resetting the datagridview and spreadsheet class.//
this.dataGridView1.Rows.Clear();
this.dataGridView1.Columns.Clear();
this.spreadSheet = new SpreadsheetClass(50, 26);
this.Form1_Load(sender, e);
this.InitializeDataGridView();
XmlReader xmlReader = null;
xmlReader = XmlReader.Create(browserDialog.FileName);
this.spreadSheet.LoadFromXml(xmlReader);
xmlReader.Close();
}
}
private void CreateReport(IDictionary idc, string rptName, string treeId, string orgId)
{
var bll = new BLL.BLLReport();
//var styleObj = bll.GetRptStyleByName(rptName);
var styleObj = bll.GetRptStyle(rptName, treeId, orgId);
var style = styleObj.RptStyle;
if (!string.IsNullOrEmpty(style))
{
var sp1 = new SpreadsheetClass();
var doc = new XmlDocument();
doc.LoadXml(style);
var xn = new XmlNamespaceManager(doc.NameTable);
xn.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var nodes = doc.SelectNodes("//ss:Data", xn);
//根据key替换数据
foreach (XmlNode node in nodes)
{
var tmpValue = node.InnerText.Trim();
if (tmpValue.Length > 2 && tmpValue.Substring(0, 1) == "♂")
{
node.InnerText = idc.Contains(tmpValue) ? idc[tmpValue].ToString() : string.Empty;
//style = style.Replace(tmpValue, node.InnerText);
}
}
//sp1.XMLData = style;
sp1.XMLData = doc.OuterXml;
//dvShow.InnerHtml = sp1.HTMLData;
sp1.XMLData = sp1.XMLData.Replace("ss:Table", "ss:Table ss:id=\"CX\"");
Response.Write(sp1.HTMLData);
}
else
{
//Response.Write("<script>alert('set style first.')</script>");
//dvShow.InnerHtml = "报表样式未设置，请先设置报表样式";
Response.Write("<span>请先设置报表样式<span>");
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
RandomExamBLL objBll = new RandomExamBLL();
RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("id")));
SpreadsheetClass xlsheet = new SpreadsheetClass();
Worksheet ws = (Worksheet)xlsheet.Worksheets[1];
ws.Cells.Font.set_Size(10);
ws.Cells.Font.set_Name("宋体");
ws.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单";
Range rang1 = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]);
rang1.set_MergeCells(true);
rang1.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
rang1.Font.set_Name("宋体");
//write headertext
ws.Cells[2, 1] = "序号";
((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[2, 2] = "姓名";
ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true);
ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
if (PrjPub.IsWuhan())
{
ws.Cells[2, 3] = "员工编码";
}
else
{
ws.Cells[2, 3] = "工资编号";
}
ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true);
ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[2, 4] = "职名";
ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true);
ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[2, 5] = "组织机构";
ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true);
ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[2, 8] = "考试地点";
ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_MergeCells(true);
ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
for (int j = 0; j < gvChoose.Rows.Count; j++)
{
ws.Cells[3 + j, 1] = ((Label)gvChoose.Rows[j].FindControl("lblNo")).Text;
ws.Cells[3 + j, 2] = ((Label)gvChoose.Rows[j].FindControl("LabelName")).Text;
ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true);
ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
ws.Cells[3 + j, 3] = "'" + ((Label)gvChoose.Rows[j].FindControl("LabelWorkNo")).Text;
ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true);
ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
ws.Cells[3 + j, 4] = ((Label)gvChoose.Rows[j].FindControl("LabelPostName")).Text;
ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true);
ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
ws.Cells[3 + j, 5] = ((Label)gvChoose.Rows[j].FindControl("Labelorgid")).Text;
ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true);
ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[3 + j, 8] = ((Label)gvChoose.Rows[j].FindControl("lblComputeRoom")).Text;
ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_MergeCells(true);
ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
}
ws.Name = "1-1";
ws.Cells.Columns.AutoFit();
try
{
((Worksheet)xlsheet.Worksheets[1]).Activate();
string path = Server.MapPath("../Excel/Excel.xls");
if (File.Exists(path))
{
File.Delete(path);
}
xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate);
FileInfo file = new FileInfo(path);
this.Response.Clear();
this.Response.Buffer = true;
this.Response.Charset = "utf-7";
this.Response.ContentEncoding = Encoding.UTF7;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
this.Response.AddHeader("Content-Disposition",
"attachment; filename=" + HttpUtility.UrlEncode(objRandomExam.ExamName + "参加考试学员名单") + ".xls");
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
this.Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
this.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
this.Response.WriteFile(file.FullName);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
}
private void ExportToExcel(DataTable dt, Int32 listado)
{
SpreadsheetClass xlsheet = new SpreadsheetClass();
Int32 row = 1;
//-- TITULOS GENERICOS
//--------------------------------
xlsheet.ActiveSheet.Cells[row, 1] = "Encuesta Nro";
xlsheet.ActiveSheet.Cells[row, 2] = "Historia Clínica";
xlsheet.ActiveSheet.Cells[row, 3] = "Fecha";
xlsheet.ActiveSheet.Cells[row, 4] = "Sexo";
xlsheet.ActiveSheet.Cells[row, 5] = "Edad";
xlsheet.ActiveSheet.Cells[row, 6] = "Peso";
xlsheet.ActiveSheet.Cells[row, 7] = "Talla";
xlsheet.ActiveSheet.Cells[row, 8] = "IMC";
xlsheet.ActiveSheet.Cells[row, 9] = "Dieta Habitual";
switch (listado)
{
case 1:
//------------------------------------------------
//-- LISTADO 1
//------------------------------------------------
xlsheet.ActiveSheet.Cells[row, 10] = "Alimento";
xlsheet.ActiveSheet.Cells[row, 11] = "Cantidad";
row++;
foreach (DataRow dr in dt.Rows)
{
xlsheet.ActiveSheet.Cells[row, 1] = dr["EncuestaNro"].ToString();
xlsheet.ActiveSheet.Cells[row, 2] = dr["HistoriaClinica"].ToString();
xlsheet.ActiveSheet.Cells[row, 3] = dr["Fecha"].ToString();
xlsheet.ActiveSheet.Cells[row, 4] = dr["Sexo"].ToString();
xlsheet.ActiveSheet.Cells[row, 5] = dr["Edad"].ToString();
xlsheet.ActiveSheet.Cells[row, 6] = dr["Peso"].ToString();
xlsheet.ActiveSheet.Cells[row, 7] = dr["Talla"].ToString();
xlsheet.ActiveSheet.Cells[row, 8] = dr["IMC"].ToString();
xlsheet.ActiveSheet.Cells[row, 9] = dr["TipoDeDieta"].ToString();
xlsheet.ActiveSheet.Cells[row, 10] = dr["Nombre"].ToString();
xlsheet.ActiveSheet.Cells[row, 11] = dr["Cantidad"].ToString();
row++;
}
break;
case 2:
//------------------------------------------------
//-- LISTADO 2
//------------------------------------------------
xlsheet.ActiveSheet.Cells[row, 10] = "Nutriente";
xlsheet.ActiveSheet.Cells[row, 11] = "Cantidad";
row++;
foreach (DataRow dr in dt.Rows)
{
xlsheet.ActiveSheet.Cells[row, 1] = dr["EncuestaNro"].ToString();
xlsheet.ActiveSheet.Cells[row, 2] = dr["HistoriaClinica"].ToString();
xlsheet.ActiveSheet.Cells[row, 3] = dr["Fecha"].ToString();
xlsheet.ActiveSheet.Cells[row, 4] = dr["Sexo"].ToString();
xlsheet.ActiveSheet.Cells[row, 5] = dr["Edad"].ToString();
xlsheet.ActiveSheet.Cells[row, 6] = dr["Peso"].ToString();
xlsheet.ActiveSheet.Cells[row, 7] = dr["Talla"].ToString();
xlsheet.ActiveSheet.Cells[row, 8] = dr["IMC"].ToString();
xlsheet.ActiveSheet.Cells[row, 9] = dr["TipoDeDieta"].ToString();
xlsheet.ActiveSheet.Cells[row, 10] = dr["Nombre"].ToString();
xlsheet.ActiveSheet.Cells[row, 11] = dr["CantidadNutriente"].ToString();
row++;
}
break;
case 3:
//------------------------------------------------
//-- LISTADO 3
//------------------------------------------------
xlsheet.ActiveSheet.Cells[row, 10] = "Alimento";
xlsheet.ActiveSheet.Cells[row, 11] = "Cantidad";
xlsheet.ActiveSheet.Cells[row, 12] = "Nutriente";
xlsheet.ActiveSheet.Cells[row, 13] = "Cantidad";
row++;
foreach (DataRow dr in dt.Rows)
{
xlsheet.ActiveSheet.Cells[row, 1] = dr["EncuestaNro"].ToString();
xlsheet.ActiveSheet.Cells[row, 2] = dr["HistoriaClinica"].ToString();
xlsheet.ActiveSheet.Cells[row, 3] = dr["Fecha"].ToString();
xlsheet.ActiveSheet.Cells[row, 4] = dr["Sexo"].ToString();
xlsheet.ActiveSheet.Cells[row, 5] = dr["Edad"].ToString();
xlsheet.ActiveSheet.Cells[row, 6] = dr["Peso"].ToString();
xlsheet.ActiveSheet.Cells[row, 7] = dr["Talla"].ToString();
xlsheet.ActiveSheet.Cells[row, 8] = dr["IMC"].ToString();
xlsheet.ActiveSheet.Cells[row, 9] = dr["TipoDeDieta"].ToString();
xlsheet.ActiveSheet.Cells[row, 10] = dr["Alimento"].ToString();
xlsheet.ActiveSheet.Cells[row, 11] = dr["CantidadAlimento"].ToString();
xlsheet.ActiveSheet.Cells[row, 12] = dr["Nutriente"].ToString();
xlsheet.ActiveSheet.Cells[row, 13] = dr["CantidadNutriente"].ToString();
row++;
}
break;
default:
break;
}
string fileName = string.Format("{0}.xls", Guid.NewGuid());
string fullNameFile = string.Format("{0}\\Excels\\{1}", Server.MapPath(Request.ApplicationPath), fileName);
// save it off to the filesystem...
xlsheet.Export(fullNameFile, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportHTML);
FileStream MyFileStream = new FileStream(fullNameFile, FileMode.Open);
long FileSize = MyFileStream.Length;
byte[] Buffer = new byte[(int)FileSize];
MyFileStream.Read(Buffer, 0, (int)MyFileStream.Length);
MyFileStream.Close();
// set content header so browser knows you'r sending Excel workbook...
Response.Clear();
Response.ContentType = "application/x-msexcel";
Response.Buffer = false;
//Response.AppendHeader("Content-Type", "application/ms-excel");
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Encuesta-{0}", fileName));
Response.AppendHeader("Content-Transfer-Encoding", "binary");
Response.BinaryWrite(Buffer);
Response.End();
// clean up old files...
File.Delete(fullNameFile);
}
double versionNumber = 5.0; //current Version #
public Form1()
{
spc = new SpreadsheetClass(50, 26);
spc.CellPropertyChanged += OnCellPropertyChanged; //subscribe to propertyChanged event
InitializeComponent();
}
public void DataSetToExcel(DataSet ds, string Duser, string titlename, string filepath)
{
SpreadsheetClass class2 = new SpreadsheetClass();
DataTable table = ds.Tables[0];
DataRow[] rowArray = table.Select();
int num = 0;
int num2 = 1;
int num3 = num2 + 1;
int num4 = num3 + 1;
int num5 = num4 + 1;
int count = table.Columns.Count;
string str = null;
//class2.get_Range(class2.Cells[num2, num2], class2.Cells[num2, count]).MergeCells = 1;
if ((titlename == "") || (titlename == null))
{
class2.ActiveSheet.Cells[num2, num2] = "添加标题处(高级报表)";
}
class2.ActiveSheet.Cells[num2, num2] = titlename.Trim();
if ((Duser == "") || (Duser == null))
{
str = "DFSOFT";
}
else
{
str = Duser;
}
/*class2.get_Range(class2.Cells[num2, num2], class2.Cells[num2, count]).Font.Size = 13.0;
class2.get_Range(class2.Cells[num2, num2], class2.Cells[num2, count]).Font.Bold = 1;
class2.get_Range(class2.Cells[num4, num2], class2.Cells[num4, count]).Font.Bold = 1;
class2.get_Range(class2.Cells[num3, num2], class2.Cells[num3, count]).MergeCells = 1;*/
class2.ActiveSheet.Cells[num3, num2] = "日期:" + DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日 ";
num = 0;
while (num < count)
{
class2.ActiveSheet.Cells[num4, num + 1] = table.Columns[num].Caption.ToString();
num++;
}
foreach (DataRow row in rowArray)
{
for (num = 0; num < count; num++)
{
class2.ActiveSheet.Cells[num5, num + 1] = row[num].ToString().Trim();
}
num5++;
}
//class2.get_Range(class2.Cells[num4, num2], class2.Cells[num5 - 1, count]).Borders.LineStyle = 1;
try
{
class2.Export(filepath + "//exportfiles//~$" + str + ".xls", SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
}
catch (Exception exception)
{
throw new Exception("系统调用错误或有打开的Excel文件！" + exception);
}
HttpResponse response = HttpContext.Current.Response;
response.ContentEncoding = Encoding.GetEncoding("GB2312");
response.AppendHeader("Content-disposition", "attachment;filename=" + str + ".xls");
response.ContentType = "application/ms-excel";
FileInfo info = new FileInfo(filepath + "//exportfiles//~$" + str + ".xls");
response.Clear();
response.AddHeader("content-length", info.Length.ToString());
response.WriteFile(info.FullName);
response.End();
}
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
excelHtml = CDSAdomd.GetExcelHtml(CDSAdomd.GetCellSet("Provider=MSOLAP; Datasource=localhost; Initial Catalog=PsiMedicaTestCubes;","SELECT {[Measures].[Episodes], [Measures].[Payment], [Measures].[Payment per Episode]} ON COLUMNS, {[Primary Diagnosis].[All Primary Diagnosis]} ON ROWS FROM VCEpisodes"));
OWC.Spreadsheet s = new SpreadsheetClass();
s.HTMLData = CDSAdomd.GetExcelHtml(CDSAdomd.GetCellSet("Provider=MSOLAP; Datasource=localhost; Initial Catalog=PsiMedicaTestCubes;","SELECT {[Measures].[Episodes], [Measures].[Payment], [Measures].[Payment per Episode]} ON COLUMNS, {[Primary Diagnosis].[All Primary Diagnosis]} ON ROWS FROM VCEpisodes"));
}
private void CreateReport2(IDictionary idc, string rptStyle)
{
var sp1 = new SpreadsheetClass();
var doc = new XmlDocument();
doc.LoadXml(rptStyle);
var xn = new XmlNamespaceManager(doc.NameTable);
xn.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
var nodes = doc.SelectNodes("//ss:Data", xn);
//根据key替换数据
foreach (XmlNode node in nodes)
{
var tmpValue = node.InnerText.Trim();
if (tmpValue.Length > 2 && tmpValue.Substring(0, 1) == "♂")
{
node.InnerText = idc.Contains(tmpValue) ? idc[tmpValue].ToString() : string.Empty;
//style = style.Replace(tmpValue, node.InnerText);
}
}
//sp1.XMLData = style;
sp1.XMLData = doc.OuterXml;
var fileName = "e:\\rpt.xls"; //可以考虑把保存文件路径写成配置的
sp1.Export(fileName, SheetExportActionEnum.ssExportActionOpenInExcel);
}
public static void DataGrid2Excel(DataGrid dg, string filename)
{
OWC.SpreadsheetClass xlsheet = new SpreadsheetClass();
int ihead = 0;
foreach (DataGridColumn dgc in dg.Columns)
{
xlsheet.ActiveSheet.Cells[1, ihead + 1] = dgc.HeaderText;
// xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]).Font.Bold = true;
// xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]).Font.Color = "red";
ihead++;
}
int icols = dg.Items[0].Cells.Count;
for (int j = 0; j < dg.Items.Count; j++)
{
for (int i = 0; i < icols; i++)
{
xlsheet.ActiveSheet.Cells[j + 2, i + 1] = dg.Items[j].Cells[i].Text.Replace(" ", " ");
}
}
try
{
//System.Web.HttpContext.Current.Server.MapPath
xlsheet.ActiveSheet.Export(filename, OWC.SheetExportActionEnum.ssExportActionOpenInExcel);
}
catch
{
return;
}
}
protected void btnOutPut_Click(object sender, ImageClickEventArgs e)
{
DataSet ds = GetDataSet();
DataTable dt = ds.Tables[0];
int orgID = Convert.ToInt32(Request.QueryString.Get("OrgID"));
string OrgName = "";
if (orgID != 1)
{
OrganizationBLL orgBll = new OrganizationBLL();
Organization org = orgBll.GetOrganization(orgID);
OrgName = org.ShortName;
}
string strExamName = "";
string strExamTime = "";
string qsExamId = Request.QueryString.Get("eid");
ExamBLL examBll = new ExamBLL();
RailExam.Model.Exam exam = examBll.GetExam(int.Parse(qsExamId));
if (exam != null)
{
strExamName = exam.ExamName;
strExamTime = exam.BeginTime.ToString() + "--" + exam.EndTime.ToString();
}
SpreadsheetClass xlsheet = new SpreadsheetClass();
Worksheet ws = (Worksheet)xlsheet.Worksheets[1];
ws.Cells.Font.set_Size(10);
ws.Cells.Font.set_Name("宋体");
ws.Cells[1, 1] = PrjPub.GetRailName() + OrgName;
Range rang1 = ws.get_Range(ws.Cells[1, 1], ws.Cells[2, 13]);
rang1.set_MergeCells(true);
rang1.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
rang1.Font.set_Bold(true);
rang1.Font.set_Size(17);
rang1.Font.set_Name("宋体");
ws.Cells[3, 1] = strExamName + "学员成绩登记表";
rang1 = ws.get_Range(ws.Cells[3, 1], ws.Cells[3, 13]);
rang1.set_MergeCells(true);
rang1.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
rang1.Font.set_Bold(true);
rang1.Font.set_Size(12);
rang1.Font.set_Name("宋体");
ws.Cells[4, 1] = "考试日期： " + strExamTime;
rang1 = ws.get_Range(ws.Cells[4, 1], ws.Cells[4, 13]);
rang1.set_MergeCells(true);
rang1.set_HorizontalAlignment(XlHAlign.xlHAlignRight);
rang1.Font.set_Name("宋体");
//write headertext
ws.Cells[5, 1] = "序号";
((Range)ws.Cells[5, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[5, 2] = "姓名";
ws.get_Range(ws.Cells[5, 2], ws.Cells[5, 4]).set_MergeCells(true);
ws.get_Range(ws.Cells[5, 2], ws.Cells[5, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[5, 5] = "组织机构（车间）";
ws.get_Range(ws.Cells[5, 5], ws.Cells[5, 7]).set_MergeCells(true);
ws.get_Range(ws.Cells[5, 5], ws.Cells[5, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[5, 8] = "职名";
ws.get_Range(ws.Cells[5, 8], ws.Cells[5, 10]).set_MergeCells(true);
ws.get_Range(ws.Cells[5, 8], ws.Cells[5, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[5, 11] = "分数";
ws.get_Range(ws.Cells[5, 11], ws.Cells[5, 13]).set_MergeCells(true);
ws.get_Range(ws.Cells[5, 11], ws.Cells[5, 13]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
decimal decScore = 0;
for (int j = 0; j < dt.Rows.Count; j++)
{
ws.Cells[6 + j, 1] = j + 1;
ws.Cells[6 + j, 2] = dt.Rows[j]["ExamineeName"].ToString();
ws.get_Range(ws.Cells[6 + j, 2], ws.Cells[6 + j, 4]).set_MergeCells(true);
ws.get_Range(ws.Cells[6 + j, 2], ws.Cells[6 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
ws.Cells[6 + j, 5] = dt.Rows[j]["OrganizationName"].ToString();
ws.get_Range(ws.Cells[6 + j, 5], ws.Cells[6 + j, 7]).set_MergeCells(true);
ws.get_Range(ws.Cells[6 + j, 5], ws.Cells[6 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
ws.Cells[6 + j, 8] = dt.Rows[j]["PostName"].ToString();
ws.get_Range(ws.Cells[6 + j, 8], ws.Cells[6 + j, 10]).set_MergeCells(true);
ws.get_Range(ws.Cells[6 + j, 8], ws.Cells[6 + j, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
decScore += decimal.Parse(dt.Rows[j]["Score"].ToString());
ws.Cells[6 + j, 11] = dt.Rows[j]["Score"].ToString();
ws.get_Range(ws.Cells[6 + j, 11], ws.Cells[6 + j, 13]).set_MergeCells(true);
ws.get_Range(ws.Cells[6 + j, 11], ws.Cells[6 + j, 13]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
}
decimal dec1 = 0;
if (dt.Rows.Count > 0)
{
dec1 = decScore / dt.Rows.Count;
}
ws.Cells[6 + dt.Rows.Count, 1] = "平均分：";
((Range)ws.Cells[5, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
ws.Cells[6 + dt.Rows.Count, 2] = dec1.ToString("0.00");
ws.get_Range(ws.Cells[6 + dt.Rows.Count, 2], ws.Cells[6 + dt.Rows.Count, 13]).set_MergeCells(true);
ws.get_Range(ws.Cells[6 + dt.Rows.Count, 2], ws.Cells[6 + dt.Rows.Count, 13]).set_HorizontalAlignment(XlHAlign.xlHAlignRight);
for (int k = 1; k <= 13; k++)
{
for (int j = 5; j <= 6 + dt.Rows.Count; j++)
{
((Range)ws.Cells[j, k]).BorderAround(1, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
}
}
ws.Name = "1-1";
ws.Cells.Columns.AutoFit();
try
{
((Worksheet)xlsheet.Worksheets[1]).Activate();
string path = Server.MapPath("../Excel/Excel.xls");
if (File.Exists(path))
{
File.Delete(path);
}
xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate);
System.IO.FileInfo file = new System.IO.FileInfo(path);
this.Response.Clear();
this.Response.Buffer = true;
this.Response.Charset = "utf-7";
this.Response.ContentEncoding = System.Text.Encoding.UTF7;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + "Excel.xls");
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
this.Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
this.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
this.Response.WriteFile(file.FullName);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
}
