DataTable dt = GetList();
string path = "文件目录";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx";//文件类型
saveFileDialog.Title = "导出Excel文件";//文件标题
saveFileDialog.AddExtension = true;//自动添加扩展名
saveFileDialog.RestoreDirectory = true;//记忆上次打开的目录
saveFileDialog.FileName = "默认名称";//默认名称
if (saveFileDialog.ShowDialog() != DialogResult.OK)
return;
int RowsNumber = 10;//每页填充多少行数据
int PageRows = 17;//每页总行数
string localFilePath = saveFileDialog.FileName.ToString();//获取保存的文件名
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(path);
DataTable GroupTable = GetGroupByLists(dt);//使用分组来获取多少个sheet
for (int i = 1; i < GroupTable.Rows.Count; i++)//循环复制工作表
{
book.Worksheets.AddCopy(0);
}
for (int i = 0; i < GroupTable.Rows.Count; i++)
{
Aspose.Cells.Worksheet sheet = book.Worksheets[i];
DataRow[] rows = dt.Select("id = " + GroupTable.Rows[i][0].GetString());//根据分组取得对应的数据
sheet.Cells["A1"].PutValue(rows[0]["id"].GetString());
sheet.Name = string.IsNullOrEmpty(rows[0]["id"].GetString());//设置工作表名字
int PageCount = Math.Ceiling(rows.Length.GetDouble() / RowsNumber).GetInt();//获取当前id打印多少页
int yushu = rows.Length % RowsNumber;//获取最后一页有多少行
int StartRows = 0;//复制开始行
int EndRows = PageRows - 1;//复制结束行
int tableStareIndex = 0;//数据源获取起始行数
int tableEndIndex = PageCount == 1 ? rows.Length : RowsNumber;//数据源获取结束行数
for (int z = 1; z < PageCount; z++)//循环总页数复制行
{
for (int x = StartRows; x <= EndRows; x++)
{
sheet.Cells.CopyRow(sheet.Cells, x, x + PageRows);
}
StartRows = StartRows + PageRows;
EndRows = EndRows + PageRows;
}
for (int y = 0; y < PageCount; y++)//循环页数
{
for (int a = tableStareIndex; a < tableEndIndex; a++)
{
//开始数据填充
}
tableStareIndex = tableStareIndex + RowsNumber;//调整dt获取的行数因第一页已经取了前面十行,第二页如果还是取前十行会重复,还需要判断是否是最后一页,是的话直接添加余数
if (y + 2 == PageCount)
{
tableEndIndex = tableEndIndex + yushu;
}
else
{
tableEndIndex = tableEndIndex + RowsNumber;
}
}
}
book.Worksheets.ActiveSheetIndex = 0;//设置默认打开的工作表索引,默认打开第一页
book.Save(localFilePath);
System.Diagnostics.Process.Start(localFilePath);//打开excel
public DataTable GetGroupByLists(DataTable dt)
{
DataTable table = new DataTable();
table.Columns.AddRange(
new DataColumn[] { new DataColumn("ItemCode", typeof(string)) });
DataTable dtName = dt.DefaultView.ToTable(true, "ItemCode");
for (int i = 0; i < dtName.Rows.Count; i++)
{
DataRow dr = table.NewRow();
dr[0] = dtName.Rows[i][0].ToString();//存放名字
table.Rows.Add(dr);//添加进table中
}
return dtName;
}
思路大概为查出数据后先获取分组的数据,然后根据分组的数据进行循环添加sheet,每一组一个sheet.然后循环分组的数据通过select来获取这个分组数据的对应数据.然后根据行数进行同个sheet中的分页.然后插入数据