解决思路:
1、找个可用的Aspose.Cells(有钱还是买个正版吧,谁开发个东西也不容易);
2、在.Net方案中引用此Cells;
3、写个函数ToExcel(传递一个DataTable),可以另写个SetCellStyle的函数专门给各列设置样式。
4、在按钮的Click事件中调用此ToExcel()即可。
注:想更详细了解的到Aspose网站去,那里有很多在线文档,够你看的了。也可以下载个Demo程序研究。
部分代码贴在这里,代码还有需要改进的地方,暂时这么用着,给自己以后方便查找,再者给真正接触此控件的同志们抛个砖头:
ExportToExcel()的:
/// <summary>
/// 利用Aspose.Cells对DataTable数据生成Excel文件
/// </summary>
/// <returns>是否成功</returns>
/// <param name="response">包含存储路径,直接用Response即可</param>
/// <param name="dt">数据体</param>
/// <param name="FileName">文件名</param>
/// <param name="SheetName">sheet名</param>
/// <param name="Title">表头</param>
/// <param name="ColTitle">列标题,字符串数组</param>
/// <param name="ColName">列名,字符串数组</param>
/// <param name="ColWidth">列宽,整数数组</param>
/// <param name="ColStyle">列样式,整数数组,1=居中文本、2=特殊字体Georgia、3=居左文本、4=整数(x)、5=2位小数(x,xxx.xx)、6=日期(yyyy-M-d不带时间)、7=百分数(0.123=12.3%)</param>
/// <param name="ColTitleRow">列标题的开始行号,有title的设为1即可</param>
/// <param name="err">返回的错误信息</param>
public bool ExportToExcel(System.Web.HttpResponse response,DataTable dt, string FileName, string SheetName, string Title,
ArrayList ColTitle, ArrayList ColName, ArrayList ColWidth, ArrayList ColStyle, int ColTitleRow, ref string err)
{
//先检查各数组是否个数一致
if (ColTitle.Count != ColName.Count || ColTitle.Count != ColWidth.Count || ColTitle.Count != ColStyle.Count)
{
err = "数据组个数不一致";
return false;
}
try
{
Workbook workbook = new Workbook();
//打开模版文件
// string path = System.Web.HttpContext.Current.Server.MapPath("~");
// path = path.Substring(0, path.LastIndexOf("//"));
// path += @"/designer/Workbooks/NumberFormatting.xls";
// workbook.Open(path);
//打开sheet
workbook.Worksheets.Clear();
Worksheet worksheet = workbook.Worksheets.Add(SheetName);
worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//cells.ClearContents(0,0,60,30);
//加入样式
ArrayList styles = new ArrayList();
styles = SetCellStyle(workbook, ColStyle);
//加入Title即表头,类似“xxx汇总表”,其占居所有列数
Range w;//范围
if(ColTitleRow>0)
{
//Put数据到某个cell中
cells[0,0].PutValue(Title);
//设置行高
cells.SetRowHeight(0, 35);
//合并单元格
cells.Merge(0,0,1,(byte)ColName.Count);
//CreateRange函数参数说明:cells.CreateRange(firstrow, firstcol, rownumber, colnumber)
w=cells.CreateRange(0,0,1,(byte)ColName.Count);
//设置该合并单元的Style
//w.Style = (Aspose.Cells.Style)styles[ColStyle.Count];
w.Style = workbook.Styles["SheetTitle"];
}
//给各列的标题行PutValue,类似“序号,类型,名称,价格,数量,合计”
int currow = ColTitleRow;
byte curcol = 0;
foreach(string s in ColTitle)
{
cells[currow,curcol++].PutValue(s);
cells.SetRowHeight(ColTitleRow, 25);
}
//设置列标题行的Style
w=cells.CreateRange(currow,0,1,ColName.Count);
w.Style = (Aspose.Cells.Style)styles[ColStyle.Count+1];
//上面这行也可以写成
//w.Style = workbook.Styles["ColTitle"];//ColTitle在函数SetCellStyle中设置了
currow++;
//将数据体按顺序插入各cell
for(int i=0; i<dt.Rows.Count; i++)
{
curcol = 0;
for(int j=0; j<ColName.Count; j++)
{
object val = dt.Rows[i][ColName[j].ToString()].ToString().Trim();
switch (int.Parse(ColStyle[j].ToString()))
{
case 4://整数
if (val.ToString().Length>0)
val = Int32.Parse(val.ToString());
else
val = "";
break;
case 5://2位小数
if (val.ToString().Length>0)
val = Decimal.Parse(val.ToString());
else
val = "";
break;
case 6://日期
if (val.ToString().Length>0)
val = DateTime.Parse(val.ToString());
else
val = "";
break;
case 7://百分数,1=100%
if (val.ToString().Length>0)
val = Decimal.Parse(val.ToString());
else
val = "";
break;
default:
break;
}//end switch
cells[currow,curcol++].PutValue(val);
}//end for j
currow ++;
} //end for i
curcol = 0;
//设置数据体Style
for(int i=0; i<dt.Columns.Count; i++)
{
w = cells.CreateRange(ColTitleRow+1, i, dt.Rows.Count, 1);
w.Style = (Aspose.Cells.Style)styles[i];
}
//w=cells.CreateRange(ColTitleRow+1,col,currow,ColName.Count);
//w.Style=excel.Styles["Data"];
//设置各列宽度
foreach(int s in ColWidth)
cells.SetColumnWidth(curcol++, s);
/*
//********可参考的格式设置*******************************************************
//Set number format with built-in index
for (int i = 0; i < 37; i ++)
{
cells[i, 1].PutValue(1234.5);
//int Number = cells[i, 0].IntValue;
//Set the display number format
cells[i, 1].Style.Number = i;
}
//Set number format with custom format string
for (int i = 1; i < 4; i ++)
{
cells[i, 3].PutValue(1234.5);
//Set the display custom number format
cells[i, 3].Style.Custom = cells[i, 2].StringValue;
}
//********可参考的格式设置*******************************************************
*/
//workbook.Save(FileName, FileFormatType.Default, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);
workbook.Save(FileName, FileFormatType.Default, SaveType.OpenInExcel, response);
return true;
}
catch (Exception ex)
{
err = ex.Message;
return false;
}
}//end ExportToExcel()
#endregion
样式设置SetStyle()的:
#region SetCellStyle()设置格式,如果需要增加新的格式,请在case后面增加,不要修改前面的
public ArrayList SetCellStyle(Workbook workbook, ArrayList styleindex)
{
//通用设置样式的
ArrayList CellStyle = new ArrayList(styleindex.Count + 2);
Aspose.Cells.Style style = null;
for (int i=0; i<styleindex.Count; i++)
{
int index = workbook.Styles.Add();
style = workbook.Styles[index];
style.Name = "Custom_Style" + ((int)(i + 1)).ToString();
style.ForegroundColor = Color.White;
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Font.Name = "宋体";
style.Font.Size = 10;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
switch((int)styleindex[i])
{
case 1://居中文本
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
break;
case 2://特殊字体Georgia
style.Font.Name = "Georgia";
break;
case 3://文本列
style.HorizontalAlignment = TextAlignmentType.Left;
break;
case 4://整数列 1 Decimal 0
style.HorizontalAlignment = TextAlignmentType.Center;
style.Number = 1;
break;
case 5://2位小数 39={Currency #,##0.00;-#,##0.00} 40={#,##0.00;[Red]-#,##0.00}
style.HorizontalAlignment = TextAlignmentType.Right;
style.Number = 40;
break;
case 6://日期列 14 Date yyyy-m-d
style.HorizontalAlignment = TextAlignmentType.Center;
style.Number = 14;//这个格式不是太好,还需要调整
break;
case 7://百分比% 10 Percentage 0.00%
style.HorizontalAlignment = TextAlignmentType.Center;
style.Number = 10;
break;
default:
break;
}//end switch
CellStyle.Add(style);
}
//特别增加一个用于表头的style.Name = "SheetTitle";
int sindex = workbook.Styles.Add();
style = workbook.Styles[sindex];
style.Name = "SheetTitle";
style.Font.Size = 14;
style.Font.IsBold = true;
style.Font.Name = "楷体_GB2312";
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
CellStyle.Add(style);
//特别增加一个用于列标题的style.Name = "ColTitle";
sindex = workbook.Styles.Add();
style = workbook.Styles[sindex];
style.Name = "ColTitle";
style.Font.Size = 12;
style.Font.IsBold = true;
style.Font.Name = "宋体";
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
CellStyle.Add(style);
//返回样式数组
return CellStyle;
}//end SetCellStyle
#endregion
原文地址引自:
http://blog.csdn.net/wsq2002/article/details/4470685 感谢原作者。
官方网站:http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets#ImportingDatatoWorksheets-array