Interop.Excelを使用したExcelの生成
19810 ワード
object filePath = @"C:\" + DateTime.Now.ToShortDateString().Replace("-", "") + DateTime.Now.ToLongTimeString().Replace(":", "") + ".xlsx"; //
public void GeneExcel(DataSet ds)
{
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
Workbooks workbooks = xlApp.Workbooks;
Workbook workBook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet workSheet = (Worksheet)workBook.Worksheets[1];// sheet1
// WorkBook
//Workbooks workbooks = xlApp.Workbooks;
//Workbook workBook = xlApp.Workbooks.Open(filePath.ToString(),
// Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//// WorkSheet
//Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
try
{
workSheet.Name = " ";
workSheet.Cells[1, 1] = " ";
workSheet.Cells[1, 2] = " 、 ";
workSheet.Cells[1, 23] = " ";
workSheet.Cells[2, 2] = "D";
workSheet.Cells[2, 5] = "R";
workSheet.Cells[2, 8] = "A";
workSheet.Cells[2, 11] = "S";
workSheet.Cells[2, 14] = "T";
workSheet.Cells[2, 17] = "I";
workSheet.Cells[2, 20] = "C";
workSheet.Cells[2, 23] = " ";
workSheet.Cells[2, 24] = " ";
workSheet.Cells[3, 2] = " ";
workSheet.Cells[3, 3] = " ";
workSheet.Cells[3, 4] = " ";
workSheet.Cells[3, 5] = " ";
workSheet.Cells[3, 6] = " ";
workSheet.Cells[3, 7] = " ";
workSheet.Cells[3, 8] = " ";
workSheet.Cells[3, 9] = " ";
workSheet.Cells[3, 10] = " ";
workSheet.Cells[3, 11] = " ";
workSheet.Cells[3, 12] = " ";
workSheet.Cells[3, 13] = " ";
workSheet.Cells[3, 14] = " ";
workSheet.Cells[3, 15] = " ";
workSheet.Cells[3, 16] = " ";
workSheet.Cells[3, 17] = " ";
workSheet.Cells[3, 18] = " ";
workSheet.Cells[3, 19] = " ";
workSheet.Cells[3, 20] = " ";
workSheet.Cells[3, 21] = " ";
workSheet.Cells[3, 22] = " ";
workSheet.get_Range("A1", "A3").MergeCells = true; //
Range rg1 = workSheet.get_Range(workSheet.Cells[1, 2], workSheet.Cells[1, 22]);
//range.ClearContents(); // Range ，
rg1.MergeCells = true;
rg1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rg1.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
workSheet.get_Range(workSheet.Cells[1, 23], workSheet.Cells[1, 24]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 2], workSheet.Cells[2, 4]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 5], workSheet.Cells[2, 7]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 8], workSheet.Cells[2, 10]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 11], workSheet.Cells[2, 13]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 14], workSheet.Cells[2, 16]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 17], workSheet.Cells[2, 19]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 20], workSheet.Cells[2, 22]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 23], workSheet.Cells[3, 23]).MergeCells = true;
workSheet.get_Range(workSheet.Cells[2, 24], workSheet.Cells[3, 24]).MergeCells = true;
/* Excel */
int waterdataRows = ds.Tables[0].Rows.Count;
int waterdataCols = ds.Tables[0].Columns.Count;
for (int i = 1; i <= waterdataRows; i++)
{
workSheet.Cells[i + 3, 1] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(0);
for (int j = 1; j <= 7; j++)
{
workSheet.Cells[i + 3, 3 * j] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(j);
}
workSheet.Cells[i + 3, 23] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(1);
workSheet.Cells[i + 3, 24] = ds.Tables[0].Rows[i - 1].ItemArray.GetValue(3);
}
//
//Range rgInsert = (Excel.Range)workSheet.Rows[6, Type.Missing];
//rgInsert.Insert(Excel.XlDirection.xlDown, Type.Missing);
//rgInsert.Copy(); //
//rgInsert.Delete(); //
//range1.Copy(rgStyle);
// ( ）
//Excel.Range rng = workSheet.get_Range("B4", Type.Missing);
//rng.Value2 = " ";
//rng.AutoFill(workSheet.get_Range("B4", "B9"),
// Excel.XlAutoFillType.xlFillWeekdays);
Excel.Range rng = workSheet.get_Range("C4", Type.Missing);
rng.Value2 = " ";
rng.AutoFill(workSheet.get_Range("C4", "C9"),
Excel.XlAutoFillType.xlFillMonths);
//rng.Value2 = "1";
//rng.AutoFill(workSheet.get_Range("D4", "D9"),
// Excel.XlAutoFillType.xlFillSeries);
//Excel.Range rangePic = workSheet.get_Range("A10", "A11");
//rangePic.Select();
//workSheet.Shapes.AddPicture(@"C:\6.jpg", Microsoft.Office.Core.MsoTriState.msoFalse,
//Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(rangePic.Left), Convert.ToSingle(rangePic.Top), 10, 10);
#region
Excel.Range rgStyle = workSheet.get_Range("O10", Type.Missing);
rgStyle.Formula = "=SUM(O4:O9)";
rgStyle.Calculate();
//rgStyle.Interior.ColorIndex = ColorIndex. ;//15
//rgStyle.Font.Color = ColorIndex. ; //
//rgStyle.Font.Bold = true;
//rgStyle.Font.Size = 9;
Excel.Style style;
//style = workBook.Styles["NewStyle"];
style = workBook.Styles.Add("NewStyle", Type.Missing);
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = 255;//
style.Interior.Color = (200 << 16) | (200 << 8) | 200; //
//rgStyle.Interior.ColorIndex = ColorIndex. ;//15
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
rgStyle.Value2 = "'Style Test";
rgStyle.Style = "NewStyle";
rgStyle.Columns.AutoFit();
#endregion
#region 、
//Range rgHeight = workSheet.get_Range("C9", Type.Missing);
//rgHeight.Value = "sdfsdfsdf";
//rgHeight.RowHeight = 100;
//range.EntireColumn.AutoFit();
Range rgWidth = workSheet.get_Range("C9", Type.Missing);
rgWidth.Value = "sdfsfscvfffffffffffdf";
//rgWidth.ColumnWidth = 20;
rgWidth.EntireColumn.AutoFit(); //
#endregion
//sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; //
#region Excel
workBook.Saved = true;
//workBook.Save();
//workBook.SaveCopyAs(filePath);
workBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//workBook.SaveAs(filePath, Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Excel.XlFileFormat.xlXMLSpreadsheet SaveAsFileFormat
#endregion
#region Excel
System.Runtime.InteropServices.Marshal.ReleaseComObject(rg1); // Range
System.Runtime.InteropServices.Marshal.ReleaseComObject(rgStyle);
System.Runtime.InteropServices.Marshal.ReleaseComObject(rgWidth);
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
workBook.Close(false, null, null);
xlApp.Workbooks.Close();
xlApp.Quit(); // Excel，
GC.WaitForPendingFinalizers();
GC.Collect();
#endregion
}
catch (Exception ex)
{
}
finally
{
}
System.Diagnostics.Process.Start(filePath.ToString());
}