protected void gvReturns_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvReturns.PageIndex = e.NewPageIndex;
Pivot pvt = new Pivot((DataTable)Session[_nameSessionData]);
string[] filagru = { "Asesor", "Lider", "Cliente", "Dni" };
string[] filalid = { "Asesor", "Lider" };
string[] fila = (chkresumen.Checked) ? filalid : filagru;
//string[] fila = { "lider", "Cliente" };
//string[] col = {"Ano", "Mes","Semana", "Dia" };
string[] col = { "Ano", "Mes", "Semana" };
if (optunidad.Checked)
{
gvReturns.DataSource = pvt.PivotData("Total Pares", AggregateFunction.Sum, fila, col);
}
else
{
gvReturns.DataSource = pvt.PivotData("Venta Total", AggregateFunction.Sum, fila, col);
}
gvReturns.DataBind();
if (!chkresumen.Checked)
{
MergeRows(gvReturns, 3);
}
else
{
MergeRows(gvReturns, 2);
}
//MergeRows(gvReturns, 2);
}
private void sbconsulta(int valor)
{
DataSet dsreturn = www.aquarella.com.pe.bll.Payments.getventaunmo(valor, Convert.ToDateTime(txtDateStart.Text), Convert.ToDateTime(txtDateEnd.Text), _user._asesor);
Pivot pvt = new Pivot(dsreturn.Tables[0]);
string[] filagru = { "Asesor", "Lider", "Cliente", "Dni" };
string[] filalid = { "Asesor", "Lider" };
string[] fila = (chkresumen.Checked) ? filalid : filagru;
//string[] col = { "Ano","Mes","Semana", "Dia" };
string[] col = { "Ano", "Mes", "Semana" };
if (optunidad.Checked)
{
gvReturns.DataSource = pvt.PivotData("Total Pares", AggregateFunction.Sum, fila, col);
}
else
{
gvReturns.DataSource = pvt.PivotData("Venta Total", AggregateFunction.Sum, fila, col);
}
gvReturns.DataBind();
if (!chkresumen.Checked)
{
MergeRows(gvReturns, 3);
}
else
{
MergeRows(gvReturns, 2);
}
Session[_nameSessionData] = dsreturn.Tables[0];
}
public static HtmlString ReportWithPivot(this DataTable source, string cssClass, string rowField, string dataField, AggregateFunction aggregate, Dictionary <int, string> departments, Dictionary <int, string> heads, IEnumerable <string> columnFields)
{
Pivot p = new Pivot(source);
DataTable dt = p.PivotData(rowField, dataField, aggregate, departments, heads, columnFields);
return(dt.Report(cssClass, columnFields.ToList().Count(), dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray()));
}
protected void sbconsultar()
{
try
{
//Session[_nameSessionData]= Lider.Lider.fget_afiliados(Convert.ToDateTime(txtDateStart.Text), Convert.ToDateTime(txtDateEnd.Text)).Tables[0];
msnMessage.Visible = false;
DataSet dsreturn = Facturacion.Get_VentaSemanal(Convert.ToDateTime(txtDateStart.Text), Convert.ToDateTime(txtDateEnd.Text));
Pivot pvt = new Pivot(dsreturn.Tables[0]);
//string[] fila = { };
string[] fila = { "AQ" };
//string[] col = { "Ano","Mes","Semana", "Dia" };
string[] col = { "anio", "Mes", "dia" };
gvReturns.DataSource = pvt.PivotData("Total", AggregateFunction.Sum, fila, col);
gvReturns.DataBind();
// MergeRows(gvReturns, 2);
Session[_nameSessionData] = dsreturn.Tables[0];
//gvReturns.DataSource = Documents_Trans.get_reportsemana(Convert.ToDateTime(txtDateStart.Text), Convert.ToDateTime(txtDateEnd.Text)).Tables[0];// (DataTable)Session[_nameSessionData];
//gvReturns.DataBind();
//Session[_nameSessionData] = gvReturns.DataSource;
//MergeRows(gvReturns, 4);
}
catch (Exception ex)
{
msnMessage.Visible = true;
msnMessage.LoadMessage(ex.Message, UserControl.ucMessage.MessageType.Error);
}
}
private void sbconsulta(string valor)
{
this.msnMessage.Visible = false;
string _tempo = dwtemporada.SelectedValue;
DataSet dsreturn = www.aquarella.com.pe.bll.Stock.getstockcategoria(valor, _tempo);
Pivot pvt = new Pivot(dsreturn.Tables[0]);
string[] fila = { "Categoria", "Codigo", "Descripcion", "tempo", "stock", "foto", "precio" };
//string[] col = { "Ano","Mes","Semana", "Dia" };
//string[] col = { "Ano", "Mes", "Semana" };
string[] col = { "talla" };
gvReturns.DataSource = pvt.PivotData("Cantidad", AggregateFunction.Sum, fila, col);
gvReturns.DataBind();
fijarcolumna();
MergeRows(gvReturns, 1);
Session[_nameSessionData] = dsreturn.Tables[0];
}
public void ExportarExcel()
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "invocarfuncion", "mostrardescarga(); ", true);
DataTable dt = new DataTable();
DataTable dt1 = (DataTable)Session[_nameSessionData];
Pivot pvt = new Pivot((DataTable)Session[_nameSessionData]);
string[] fila = { "Categoria", "Codigo", "foto", "Descripcion", "tempo", "stock", "precio" };
string[] col = { "talla" };
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
dt = pvt.PivotData("Cantidad", AggregateFunction.Sum, fila, col);
string attachment = "attachment; filename=stockdetallado.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
// Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);
GridView gvexport = new GridView();
gvexport.DataSource = dt;
gvexport.DataBind();
gvexport.RenderControl(htextw);
string cadena = stw.ToString();
string iniImagen = "<img WIDTH = '34' HEIGHT = '34' alt = 'Logo_FR' style = 'margin: 50px 50px 50px 150px; vertical-align:middle; padding-left:10px' src = 'http";
string finImagen = ".JPG' />";
cadena = cadena.Replace("http", iniImagen);
cadena = cadena.Replace(".JPG", finImagen);
finImagen = ".jpg' />";
cadena = cadena.Replace(".jpg", finImagen);
cadena = cadena.Replace("<tr", "<tr height = 40");
cadena = cadena.Replace("<td", "<td height = 40");
cadena = cadena.Replace("foto", " Foto ");
ScriptManager.RegisterStartupScript(this, typeof(Page), "invocarfuncion", "ocultarDescarga(); ", true);
Response.Write(cadena);
Response.End();
}
protected void gvReturns_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvReturns.PageIndex = e.NewPageIndex;
Pivot pvt = new Pivot((DataTable)Session[_nameSessionData]);
string[] fila = { "Categoria", "Codigo", "Descripcion", "tempo", "stock", "foto", "precio" };
//string[] col = {"Ano", "Mes","Semana", "Dia" };
string[] col = { "talla" };
gvReturns.DataSource = pvt.PivotData("Cantidad", AggregateFunction.Sum, fila, col);
gvReturns.DataBind();
fijarcolumna();
MergeRows(gvReturns, 1);
}
//Binds all the GridView used in the page.//
private void BindGridView()
{
// Retrieve the data table from Excel Data Source.
DataTable dt = ExcelLayer.GetDataTable("_Data\\DataForPivot.xls", "Sheet1$");
/*
* Note:: If you wish to read the data from excel, uncomment the above code and comment the below code.//
*/
//DataTable dt = SqlLayer.GetDataTable("GetEmployee");
Pivot pvt = new Pivot(dt);
grdRawData.DataSource = dt;
string dtstr = DataTableToJson(dt);
grdRawData.DataBind();
//Example of Pivot on Both the Axis.//
grdBothPivot.DataSource = pvt.PivotData(new string[] { "CTC", "IsActive" }, AggregateFunction.Sum, new string[] { "Designation", "Year" }, new string[] { "Company", "Department", "Name" }, true, true, true, true);
grdBothPivot.DataBind();
DataTable dtIndex = new DataTable();
DataColumn dc; //创建列
DataRow dr; //创建行
//构造列
for (int i = 0; i < grdBothPivot.Columns.Count; i++)
{
dc = new DataColumn();
dc.ColumnName = grdBothPivot.Columns[i].HeaderText;
dtIndex.Columns.Add(dc);
}
//构造行
for (int i = 0; i < grdBothPivot.Rows.Count; i++)
{
dr = dtIndex.NewRow();
for (int j = 0; j < grdBothPivot.Columns.Count; j++)
{
dr[j] = grdBothPivot.Rows[i].Cells[j].Text;
}
dtIndex.Rows.Add(dr);
}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
int rowIndex = Int32.Parse(e.CommandArgument.ToString());//获取点击了第几行
int posID = Int32.Parse(grVPositionManage.DataKeys[rowIndex].Values[0].ToString());
if (e.CommandName == "DimSetting")
{
Response.Redirect("~/System/DimensionSet.aspx?mode=0&posid=" + posID.ToString());
}
if (e.CommandName.Equals("Export") || e.CommandName.Equals("ExportNewData"))//答题信息导出 or 数据导出
{
PositionBLL temp = new PositionBLL();
DimensionBLL dBll = new DimensionBLL();
DataSet dimDS = dBll.GetDimensionAll();
DataSet dsAll = new DataSet();
for (int i = 0; i < dimDS.Tables[0].Rows.Count; i++)
{
int dimID = Int32.Parse(dimDS.Tables[0].Rows[i][0].ToString());
DataSet dsa = temp.GetExportDSIncAllDim(posID,dimID);
dsa.Tables[0].TableName = dBll.GetDimension(dimID.ToString()).Dimnm;
dsAll.Tables.Add(dsa.Tables[0].Copy());
}
//2012.3.14，岗位导出时删除无关维度表 begin.
for (int i = dsAll.Tables.Count - 1; i >= 0; i--)
{
if (dsAll.Tables[i].Rows.Count < 1)
{
dsAll.Tables.Remove(dsAll.Tables[i]);
}
}
dsAll.AcceptChanges();
//2012.3.14，岗位导出时删除无关维度表 end.
//新的导出格式---数据导出
DataSet dsPivot = new DataSet();
if (e.CommandName.Equals("ExportNewData"))
{
//开始进行pivot
string activityName = string.Empty;
string testerName = string.Empty;
TesterInfoBLL tbll = new TesterInfoBLL();
ActivityBLL abll = new ActivityBLL();
GuidBLL gbll = new GuidBLL();
foreach (DataTable t in dsAll.Tables)
{
Pivot p = new Pivot(t);
DataTable dt = p.PivotData("序列号", "原始得分", AggregateFunction.Sum, "题目编号");
p = null;
dt.TableName = t.TableName;
dt.Columns.Add("项目名称").SetOrdinal(0);
dt.Columns.Add("姓名").SetOrdinal(1);
foreach (DataRow row in dt.Rows)
{
string guid = row["序列号"].ToString();
row["项目名称"] = abll.GetActivityNM(gbll.GetActivityId(guid));
row["姓名"] = tbll.GetUserNameByGUID(guid);
}
dsPivot.Tables.Add(dt);
}
//pivot结束
}
string filePath = Server.MapPath("../userfiles") + @"\position_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xls";
string fileName = Path.GetFileName(filePath);
if (e.CommandName.Equals("Export"))//原始格式--答题信息导出
{
//2013.10.22 add by conghui for export issue begin.
foreach (DataTable dt in dsAll.Tables)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Rows[j][i] == DBNull.Value || dt.Rows[j][i] == null)//如果单元格为空
{
if (i == 2)//第二列是日期列
{
dt.Rows[j][i] = "1900-1-1";
}
else
{
dt.Rows[j][i] = "";
}
}
else//单元格非空
{
if (i == 2)//将日期强制转化为字符串
{
dt.Rows[j][i] = dt.Rows[j][i].ToString();
}
}
}
}
}
//2013.10.22 add by conghui for export issue end.
ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, dsAll);
}
else if (e.CommandName.Equals("ExportNewData"))//新的格式--答题数据导出
{
ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, dsPivot);
}
Stream stream = null;
stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
long bytesToRead = stream.Length;
Response.Clear();
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
while (bytesToRead > 0)
{
if (Response.IsClientConnected)
{
byte[] buffer = new Byte[10000];
int length = stream.Read(buffer, 0, 10000);
Response.OutputStream.Write(buffer, 0, length);
Response.Flush();
bytesToRead = bytesToRead - length;
}
else
{
bytesToRead = -1;
}
}
}
}
private void ExportarExcelBK()
{
DataTable dt = new DataTable();
DataTable dt1 = (DataTable)Session[_nameSessionData];
Pivot pvt = new Pivot((DataTable)Session[_nameSessionData]);
string[] fila = { "Categoria", "Codigo", "foto", "Descripcion", "tempo", "stock", "precio" };
string[] col = { "talla" };
dt = pvt.PivotData("Cantidad", AggregateFunction.Sum, fila, col);
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
Page page = new Page();
String inicio;
Style stylePrueba = new Style();
stylePrueba.Width = Unit.Pixel(200);
string strRows = "";
string strRowsHead = "";
strRowsHead = strRowsHead + "<tr height=38 >";
for (int i = 0; i < dt.Columns.Count; i++)
{
strRowsHead = strRowsHead + "<td height=38 bgcolor='#969696' width='38'> " + dt.Columns[i].ColumnName + " </ td > ";
}
strRowsHead = strRowsHead + "</tr>";
foreach (DataRow row in dt.Rows)
{
strRows = strRows + "<tr height='38' >";
for (int i = 0; i < dt.Columns.Count; i++)
{
string dato = row[i].ToString();
if (i == 2)
{
strRows = strRows + "<td height='38' align='center' style='text-align:center' WIDTH='38'><img WIDTH='34' HEIGHT='34' alt='Logo_FR' style='margin: 50px 50px 50px 50px' src='" + row[i].ToString() + "'/></td>";
}
else
{
strRows = strRows + "<td width='400' >" + row[i].ToString() + "</ td > ";
}
}
strRows = strRows + "</tr>";
}
inicio = "<div> " +
"<table <Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='2' cellPadding='2' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'>" +
strRowsHead +
strRows +
"</table>" +
"</div>";
sb.Append(inicio);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=stockdetallado.xls");
Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;
Response.Write(sb.ToString());
Response.End();
}
protected void btnUpload_Click(object sender, EventArgs e)
{
int rpta;
this.msnMessage.Visible = false;
try
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
Random r = new Random();
int varAleatorio = r.Next(0, 999);
string FilePath = Server.MapPath(FolderPath + FileName + varAleatorio);
FileUpload1.SaveAs(FilePath);
int val_dwTipArc = 1;
rpta = validarArchivo(FilePath, Extension, val_dwTipArc);
Int32 nfilas = 0;
DataTable dtExcel = new DataTable();
if (rpta == 1)
{
DataTable dt = new DataTable();
dt = Getdt(FilePath, Extension, val_dwTipArc, ref nfilas);
DataSet dsreturn = www.aquarella.com.pe.bll.Stock.getstockcategoriaExcel(dt);
dtExcel = dsreturn.Tables[0];
Session[_nameSessionData] = dtExcel;
}
else
{
msnMessage.LoadMessage("Debe seleccionar un tipo de archivo correcto.", UserControl.ucMessage.MessageType.Error);
dtExcel = (DataTable)Session[_nameSessionData];
}
File.Delete(FilePath);
Pivot pvt = new Pivot(dtExcel);
string[] fila = { "Categoria", "Codigo", "Descripcion", "tempo", "stock", "foto", "precio" };
string[] col = { "talla" };
gvReturns.DataSource = pvt.PivotData("Cantidad", AggregateFunction.Sum, fila, col);
gvReturns.DataBind();
fijarcolumna();
MergeRows(gvReturns, 1);
}
else
{
msnMessage.LoadMessage("Debe seleccionar un de archivo.", UserControl.ucMessage.MessageType.Error);
DataTable dt = new DataTable();
dt = (DataTable)Session[_nameSessionData];
Pivot pvt = new Pivot(dt);
string[] fila = { "Categoria", "Codigo", "Descripcion", "tempo", "stock", "foto", "precio" };
string[] col = { "talla" };
gvReturns.DataSource = pvt.PivotData("Cantidad", AggregateFunction.Sum, fila, col);
gvReturns.DataBind();
fijarcolumna();
MergeRows(gvReturns, 1);
}
}
catch (Exception ex)
{
string msg;
msg = ex.Message;
msnMessage.LoadMessage("Ocurrio un error: " + ex.Message, UserControl.ucMessage.MessageType.Error);
}
}
public void CreateSheet()
{
if (this._controlType == "crosstab")//交叉表
{
// Retrieve the data table from Excel Data Source.
DataTable dt = ExcelLayer.GetDataTable("DataForPivot.xls", "Sheet1$");
/*
* Note:: If you wish to read the data from excel, uncomment the above code and comment the below code.//
*/
//DataTable dt = SqlLayer.GetDataTable("GetEmployee");
Pivot pvt = new Pivot(dt);
int rowDimentionCount = 2; //行维度数量
int columnDimentionCount = 3; //列维度数量
int valueDimentionCount = 2; //值维度数量
string[] rowDimensions = { "Year", "Company" };
string[] columnDimensions = { "Department", "Name", "Designation" };
columnDimentionCount = columnDimensions.Length;
string[] valueDimensions = { "CTC", "IsActive" };
valueDimentionCount = valueDimensions.Length;
bool rowGroup = true; // 行小计
bool colGroup = true; // 列小计
bool rowSum = true; // 行合计
bool colSum = true; // 列合计
DataTable dtnew = pvt.PivotData(valueDimensions, AggregateFunction.Sum, rowDimensions, columnDimensions, rowGroup, colGroup, rowSum, colSum);
int columnCount = dtnew.Columns.Count;//列数
string title = "测试";
int rowIndex = 0;
if (title != "")
{
//创建标题行
CreateRow(_sheet, null, rowIndex, columnCount, 20);
_sheet.GetRow(0).GetCell(0).SetCellValue(title);
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1);
_sheet.AddMergedRegion(cellRangeAddress);
rowIndex += 1;
}
string[] rows = dtnew.Columns[rowDimentionCount].ColumnName.Split('.');//标题维度信息
int rowLength = rows.Length;
for (int i = 0; i < rowLength; i++)
{
CreateRow(_sheet, null, i + rowIndex, columnCount, 200);
}
//创建标题行
for (int j = 0; j < columnCount; j++)
{
for (int i = 0; i < rowLength; i++)
{
string[] currentvalue = dtnew.Columns[j].ColumnName.Split('.');
if (j < rowDimentionCount)
{
_sheet.GetRow(i + rowIndex).GetCell(j).SetCellValue(rowDimensions[j]);
}
else
{
if (i == rowLength - 1)
{
int currentcol = (j - rowDimentionCount) % valueDimentionCount;
_sheet.GetRow(i + rowIndex).GetCell(j).SetCellValue(valueDimensions[currentcol]);
}
else
{
_sheet.GetRow(i + rowIndex).GetCell(j).SetCellValue(currentvalue[i]);
}
}
}
//合并行标题上的行标题数据
if (j < rowDimentionCount)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex + rowLength - 1, j, j);
_sheet.AddMergedRegion(cellRangeAddress);
}
}
int rowCount = dtnew.Rows.Count;//行数
for (int i = 0; i < rowCount; i++)
{
CreateRow(_sheet, null, i + rowLength + rowIndex, columnCount, 200);
}
//填充数据
for (int j = 0; j < columnCount; j++)
{
for (int i = 0; i < rowCount; i++)
{
string currentvalue = dtnew.Rows[i][j].ToString();
_sheet.GetRow(i + rowLength + rowIndex).GetCell(j).SetCellValue(currentvalue);
//行标题样式
_sheet.GetRow(i + rowLength + rowIndex).GetCell(j).CellStyle = null;
}
}
//合并单元格
//合计列总计
int mergeCol = columnCount;
if (colSum)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex + rowLength - 2, columnCount - valueDimentionCount, columnCount - 1);
_sheet.AddMergedRegion(cellRangeAddress);
mergeCol = columnCount - valueDimentionCount;
}
//合并行标题的列标题数据
MergeRowWorkSheet(rowIndex, 0, rowLength, mergeCol, valueDimentionCount, rowDimentionCount);
//合计行总计
if (rowSum)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex + rowLength + rowCount - 1, rowIndex + rowLength + rowCount - 1, 0, rowDimentionCount - 1);
_sheet.AddMergedRegion(cellRangeAddress);
}
//合并列标题的行标题数据
MergeWorkSheet(rowLength + rowIndex, 0, rowCount, rowDimentionCount);
}
}
