导出的列为 a,b,c,1,2,3,....,20,21,22..
a,b,c为固定导出列
1,2,3,...,20,21,22...为动态列
条件:
根据页面输入的ID条件 得到dataset (a,b,c,1,2,3,...20,21,22,...)
需求:
显示我想显示的列名,规定导出Execl的单元格格式
问题:
我该怎么循环判断最终导出Execl显示的列呢
PS:dataset直接导出Execl不符合我的需求(列名为英文 ,单元格为小数,需求为0.00%)
如果可以解决直接导出符合我的需求,也可以 。
先谢谢每一个帮助的人
a,b,c为固定导出列
1,2,3,...,20,21,22...为动态列
条件:
根据页面输入的ID条件 得到dataset (a,b,c,1,2,3,...20,21,22,...)
需求:
显示我想显示的列名,规定导出Execl的单元格格式
问题:
我该怎么循环判断最终导出Execl显示的列呢
PS:dataset直接导出Execl不符合我的需求(列名为英文 ,单元格为小数,需求为0.00%)
如果可以解决直接导出符合我的需求,也可以 。
先谢谢每一个帮助的人
dataset.Tables[0].Columns[0].ColumnName = "中文名";2.用下面改变列格式,比如要改变第三列格式(索引为2):
DataTable dt = dataset.Tables[0];
dt.Columns.Add("列名", typeof(string));
foreach (DataRow row in dt.Rows)
row["列名"] = string.Format("{0:#.00}%", row[2]);
dt.Columns.RemoveAt(2);
dt.Columns["列名"].SetOrdinal(2);
这里面有详细代码,这篇文章导出的是 csv文件,你把这个后缀改成excel就可以直接用了
/// 删除指定sheet下的指定列
/// </summary>
/// <param name="p_objSheets">sheets</param>
/// <param name="SheetIndex">sheet索引</param>
/// <param name="DeleteColumnIndex">列索引</param>
public static void DeleteColumn(Excel.Sheets p_objSheets, int SheetIndex, int DeleteColumnIndex)
{
try
{
Excel._Worksheet m_objSheet = (Excel._Worksheet)(p_objSheets.get_Item(SheetIndex));
Excel.Range m_objRange = (Excel.Range)m_objSheet.Columns[DeleteColumnIndex, System.Reflection.Missing.Value];
m_objRange.EntireColumn.Delete(Excel.XlDirection.xlDown);
}
catch
{
return;
}
}用这个方法可以删除EXCEL多余列
/// 导出数据
/// </summary>
public static void Export(GridView gv, string filename)
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
ColumnInfo cinfo = new ColumnInfo(doc, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 19 * 387;//列宽度
sheet.AddColumnInfo(cinfo); //填充标题行
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
sheet.Cells.Add(1, 1 + i, gv.HeaderRow.Cells[i].Text);
} //填充内容
Cell cll;
for (int i = 0; i < gv.Rows.Count; i++)
{
for (int j = 0; j < gv.Columns.Count; j++)
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString());
cll.Font.FontFamily = FontFamilies.Roman;
}
} doc.Send();
}
private void CreateExecl(DataSet ds, int yID, int sID)
{ Sheet[] sheets = new Sheet[1];
int Max = eb.MaxWeek(yID, sID);
Column[] column = new Column[Max + 3];
ds.Tables[0].Columns[0].ColumnName = "aaa";
ds.Tables[0].Columns[1].ColumnName = "bbb";
ds.Tables[0].Columns[2].ColumnName = "ccc";
for (int m = 3; m < Max + 3; m++)
{
System.Data.DataTable dt = ds.Tables[0];
dt.Columns.Add("第" + (m - 2) + "周", typeof(string));
foreach (DataRow row in dt.Rows)
{
row["第" + (m - 2) + "周"] = string.Format("{0:P}", row[m]);
}
dt.Columns.RemoveAt(m);
dt.Columns["第" + (m - 2) + "周"].SetOrdinal(m);
} string[] ColumnName = new string[Max + 3]; for (int i = 0; i < column.Count(); i++)
{
string title = ds.Tables[0].Columns[i].ColumnName; column[i] = new Column(title, ds.Tables[0].Columns[i].ColumnName);
} sheets[0] = new Sheet("123", ds.Tables[0], column);
byte[] bytes = Excel2003.ExportBytes(sheets); string filename = HttpUtility.UrlEncode("123", System.Text.Encoding.UTF8).ToString() + ".xls";
WebUtitily.SendContent(this.Page, filename, bytes);
Response.End();
}
{
log.Debug("DataExchanger:ExportToCSV started! FilePath=" + filePath);
if (string.IsNullOrEmpty(filePath))
{
log.Info("DataExchanger:ExportToCSV function's filePath is null!");
return false;
} if (filePath.IndexOf(".csv") == -1)
{
log.Info("DataExchanger:ExportToCSV " + filePath + " is not a valid csv type!");
return false;
}
using (StreamWriter sw = new StreamWriter(filePath, false, Encoding.Unicode))
{
for (int i = 0; i < ds.Columns.Count; i++)
{
sw.Write(ds.Columns[i].ColumnName + "\t");
} sw.Write("\n");
for (int i = 0; i < ds.Rows.Count; i++)
{
for (int j = 0; j < ds.Columns.Count; j++)
{
sw.Write(ds.Rows[i][j].ToString() + "\t");
} sw.Write("\n");
} sw.Close();
} log.Debug("DataExchanger:ExportToCSV completed!");
return true;
}