public void BindDataSource()
{
dataGridView1.AutoGenerateColumns = false;
dataGridView1.RowHeadersVisible = true;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView1.BackgroundColor = System.Drawing.SystemColors.Control;
dataGridView2.AutoGenerateColumns = false;
dataGridView2.RowHeadersVisible = true;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dataGridView2.BackgroundColor = System.Drawing.SystemColors.Control;
TotalImpl service = new TotalImpl();
var result = service.GetTotalDetailsList(new Model.OrderSearch()
{
CustomerId = cbxCustomerId.SelectedValue.ToTryInt(), Issue = txtIssue.Text.Trim()
});
if (result.Code == 0)
{
list1 = result.Body;
dataGridView1.DataSource = result.Body;
}
result = service.GetTotalList(new Model.OrderSearch()
{
CustomerId = cbxCustomerId.SelectedValue.ToTryInt(), Issue = txtIssue.Text.Trim()
});
if (result.Code == 0)
{
list2 = result.Body;
dataGridView2.DataSource = result.Body;
}
}
private void button1_Click_1(object sender, EventArgs e)
{
TotalImpl sd = new TotalImpl();
sd.Add("2018046");
}
private void button1_Click(object sender, EventArgs e)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = txtIssue.Text.Trim();
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
workbook.Worksheets.Add();
workbook.Worksheets.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[2];
Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[3];
int columnIndex = 0;
int currentRow = 0;
int beginColumn = 0;
int endColumn = 0;
#region 客户统计
for (int i = 0; i < list2.Count; i++)
{
if (i == 0)
{
currentRow = 1;
beginColumn = 2;
endColumn = 7;
}
else
{
currentRow = 1;
beginColumn = 1 + i * 5 + columnIndex * 3;
endColumn = beginColumn + 5;
}
//写入客户姓名
MergeCells(worksheet, currentRow, beginColumn, currentRow, endColumn, list2[i].CustomerName);
//写入标题
for (int j = 0; j < dataGridView1.ColumnCount - 1; j++)
{
worksheet.Cells[2, beginColumn + j] = dataGridView1.Columns[j].HeaderText;
Range range = worksheet.Range[worksheet.Cells[2, beginColumn + j], worksheet.Cells[2, beginColumn + j]];
range.Font.Size = 18;
range.Font.Bold = true;
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
//写入内容
var temp = list1.Where(x => x.CustomerId == list2[i].CustomerId).ToList();
for (int j = 0; j < temp.Count; j++)
{
worksheet.Cells[3 + j, beginColumn + 0] = temp[j].Issue;
worksheet.Cells[3 + j, beginColumn + 1] = temp[j].OrderTypeName;
worksheet.Cells[3 + j, beginColumn + 2] = temp[j].InMoney;
worksheet.Cells[3 + j, beginColumn + 3] = temp[j].ReturnMoney;
worksheet.Cells[3 + j, beginColumn + 4] = temp[j].OutMoney;
worksheet.Cells[3 + j, beginColumn + 5] = temp[j].total;
Range range = worksheet.Range[worksheet.Cells[3 + j, beginColumn + 0], worksheet.Cells[3 + j, beginColumn + 5]];
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
currentRow = 3 + j;
}
//写入合计
worksheet.Cells[currentRow + 1, beginColumn + 0] = list2[i].Issue;
worksheet.Cells[currentRow + 1, beginColumn + 1] = "合计";
worksheet.Cells[currentRow + 1, beginColumn + 2] = list2[i].InMoney;
worksheet.Cells[currentRow + 1, beginColumn + 3] = list2[i].ReturnMoney;
worksheet.Cells[currentRow + 1, beginColumn + 4] = list2[i].OutMoney;
worksheet.Cells[currentRow + 1, beginColumn + 5] = list2[i].total;
Range range1 = worksheet.Range[worksheet.Cells[currentRow + 1, beginColumn + 0], worksheet.Cells[currentRow + 1, beginColumn + 5]];
range1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range1.Font.ColorIndex = 3;
range1.Font.Bold = true;
columnIndex = columnIndex + 1;
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
#endregion
#region 每个订单详情
TotalImpl service = new TotalImpl();
List <Model.OrderResult> list3 = service.GetOrderResult(new Model.OrderSearch()
{
Issue = txtIssue.Text.Trim()
}).Body;
currentRow = 0;
int currentColumn = 0;
for (int i = 0; i < list3.Count; i++)
{
if (i % 10 == 0)
{
currentRow = 0 + 1;
currentColumn = (i / 10) * 2 + 2;
}
else
{
currentRow = currentRow + 1;
}
worksheet1.Cells[currentRow, currentColumn] = (list3[i].Sort + list3[i].OrderTypeName);
worksheet1.Cells[currentRow, currentColumn + 1] = list3[i].Money;
Range range = worksheet1.Range[worksheet1.Cells[currentRow, currentColumn], worksheet1.Cells[currentRow, currentColumn]];
range.Font.Bold = true;
Range range1 = worksheet1.Range[worksheet1.Cells[currentRow, currentColumn + 1], worksheet1.Cells[currentRow, currentColumn + 1]];
range1.Font.ColorIndex = 3;
}
worksheet1.Columns.EntireColumn.AutoFit();//列宽自适应
#endregion
#region 总统计
for (int i = 0; i < dataGridView2.ColumnCount; i++)
{
worksheet2.Cells[1, 2 + i] = dataGridView2.Columns[i].HeaderText;
Range range = worksheet2.Range[worksheet2.Cells[1, 2 + i], worksheet2.Cells[1, 2 + i]];
range.Font.Size = 18;
range.Font.Bold = true;
range.Interior.ColorIndex = 46;
range.Interior.Pattern = -4105;
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
currentRow = 1;
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
currentRow = currentRow + 1;
for (int j = 0; j < dataGridView2.ColumnCount; j++)
{
worksheet2.Cells[currentRow, 2 + j] = dataGridView2.Rows[i].Cells[j].Value;
Range range = worksheet2.Range[worksheet2.Cells[currentRow, 2 + j], worksheet2.Cells[currentRow, 2 + j]];
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
}
worksheet2.Cells[currentRow + 1, 2 + 0] = txtIssue.Text;
worksheet2.Cells[currentRow + 1, 2 + 1] = "合计";
worksheet2.Cells[currentRow + 1, 2 + 2] = list2.Sum(x => x.InMoney);
worksheet2.Cells[currentRow + 1, 2 + 3] = list2.Sum(x => x.ReturnMoney);
worksheet2.Cells[currentRow + 1, 2 + 4] = list2.Sum(x => x.OutMoney);
worksheet2.Cells[currentRow + 1, 2 + 5] = list2.Sum(x => x.total);
Range range2 = worksheet2.Range[worksheet2.Cells[currentRow + 1, 2], worksheet2.Cells[currentRow + 1, 2 + 5]];
range2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range2.Font.Bold = true;
range2.Font.ColorIndex = 3;
worksheet2.Columns.EntireColumn.AutoFit();//列宽自适应
#endregion
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + saveDialog.FileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
