public static void ExportToExcel(DataTable DT, string projectName)
{
Excel.Application xlexcel;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Excel.Application
{
Visible = false,
DisplayAlerts = false
};
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i < DT.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = DT.Columns[i].ColumnName;
}
for (int i = 0; i < DT.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < DT.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = DT.Rows[i][j];
}
}
Excel.Range XlRange;
XlRange = xlWorkSheet.UsedRange;
int lastcol = XlRange.Columns.Count + 1;
XlRange.Borders.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
XlRange.Font.Name = "Arial";
XlRange.Cells.Font.Size = 10;
for (int i = 1; i <= lastcol; i++)
{
{
Excel.Range allColumns = (Excel.Range)xlWorkSheet.Columns[i];
allColumns.AutoFit();
}
}
Excel.Range HeaderRow = (Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, lastcol]];
HeaderRow.Cells.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGray;
HeaderRow.Cells.Font.FontStyle = "Arial";
HeaderRow.Font.Name = "Arial";
HeaderRow.Cells.Font.Size = 11;
HeaderRow.Cells.Font.Bold = true;
xlWorkSheet.Name = projectName;
xlexcel.Visible = true;
}
public void autoAjustRowsHeight(int from, int to)
{
for (int i = from; i <= to; i++)
{
Excel.Range range = (Excel.Range)worksheet.Rows[i];
range.AutoFit();
}
}
public void autoAjustRowsHeight(int[] rows)
{
foreach (int row in rows)
{
Excel.Range range = (Excel.Range)worksheet.Rows[row];
range.AutoFit();
}
}
public void autoAjustColumnsWidth(int[] columns)
{
foreach (int col in columns)
{
Excel.Range range = (Excel.Range)worksheet.Columns[col];
range.AutoFit();
}
}
//-->函数：创建excel
public static void CreateExcel(string filePath)
{
MSExcel.Application excel; //创建一个excel对象
beforeTime = DateTime.Now; //获取excel开始启动时间
excel = new MSExcel.ApplicationClass(); //创建实例,这时在系统进程中会多出一个excel进程
afterTime = DateTime.Now; //获取excel启动结束的时间
try
{
object missing = Missing.Value; //Missing 用于调用带默认参数的方法
excel.Visible = false; //不显示excel文档
excel.Application.Workbooks.Add(true); //Open Original Excel File
MSExcel.Workbook myBook;
MSExcel.Worksheet mySheet;
myBook = excel.Workbooks[1]; //获取excel程序的工作簿
mySheet = (MSExcel.Worksheet)myBook.ActiveSheet; //获取Workbook的活动工作表（最上层的工作表）
mySheet.Cells[1, 1] = "测试时间";
mySheet.Cells[1, 2] = "转动次数...";
MSExcel.Range r = mySheet.Columns; //获取矩形选择框
r.AutoFit();
excel.DisplayAlerts = false; //进制弹出提示框
mySheet.SaveAs(filePath); //另存为
#region 释放资源
//ReleaseComObject 方法递减运行库可调用包装的引用计数。详细信息见MSDN
Marshal.ReleaseComObject(myBook);
Marshal.ReleaseComObject(mySheet);
Marshal.ReleaseComObject(excel);
Marshal.ReleaseComObject(r);
myBook.Close();
excel.Workbooks.Close();
mySheet = null;
myBook = null;
r = null;
missing = null;
excel.Quit();
excel = null;
#endregion
}
catch (Exception)
{
KillExcelProcess(); //杀掉进程
}
finally
{
//可以把KillExcelProcess();放在该处从而杀掉Excel的进程
KillExcelProcess();
}
}
public static void FormatAutoFitAllColumns(this ExcelSheet sheet)
{
Range rangeA = (Range)sheet.Worksheet.Cells;
rangeA = rangeA.EntireColumn;
rangeA.AutoFit();
for (int i = 1; i <= sheet.FindLastUsedColumn(); i++)
{
Range range = (Range)sheet.Worksheet.Columns[i];
range.ColumnWidth += 3;
Cleanup.ReleaseObject(range);
}
Cleanup.ReleaseObject(rangeA);
}
public static void LoadToExcel(Dictionary <int, Zk> resultList)
{
MainWindow.infoTb.Dispatcher.Invoke(delegate { MainWindow.infoTb.Text += "Выгружаем в Excel...\r\n"; });
Excel.Application ExcellApp = new Excel.Application();
Excel.Workbook workBook;
Excel.Worksheet workSheet;
workBook = ExcellApp.Workbooks.Add();
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
Excel.Range cols = ExcellApp.Columns;
for (int i = 0; i < Class2.headerRow.Count; i++)
{
((Excel.Range)(cols.Columns[i + 1])).NumberFormat = Class2.headerRow[i].ToList()[0].Value;
workSheet.Cells[1, i + 1] = Class2.headerRow[i].ToList()[0].Key;
}
int rowNum = 0;
foreach (var item in resultList)
{
for (int i = 0; i < item.Value.forOutput.Count; i++)
{
workSheet.Cells[rowNum + 2, i + 1] = item.Value.forOutput[i]; //workSheet.Cells[i + 2, 1] =
}
rowNum++;
}
Excel.Range headerRow = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[1, Class2.headerRow.Count]];
headerRow.Font.Bold = true;
headerRow.Font.Name = "Times New Roman";
headerRow.Font.Size = "10";
cols.AutoFit();
cols.Font.Name = "Times New Roman";
cols.Font.Size = "10";
cols[6].ColumnWidth = 60;
cols[6].WrapText = true;
// cols[6].ColumnWidth = 50;
// cols[6].ShrinkToFit();
ExcellApp.Visible = true;
}
public static void ExportToExcel(System.Data.DataTable DT)
{
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.Visible = false;
xlexcel.DisplayAlerts = false;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i < DT.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = DT.Columns[i].ColumnName.ToString();
}
for (int i = 0; i < DT.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < DT.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = DT.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Range MainRange = xlWorkSheet.UsedRange;
int lastcol = MainRange.Columns.Count;
for (int i = 1; i <= lastcol; i++)
{
Microsoft.Office.Interop.Excel.Range allColumns = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns[i];
allColumns.AutoFit();
}
Microsoft.Office.Interop.Excel.Range HeaderRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, lastcol]];
HeaderRow.Cells.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGray;
HeaderRow.Cells.Font.Bold = true;
MainRange.Borders.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
xlexcel.Visible = true;
MessageBox.Show("file is ready", "", MessageBoxButton.OK);
}
private void AddFinalTouches(Excel.Worksheet xlSheet)
{
Excel.Range firstCell = xlSheet.Cells[1, 1] as Excel.Range;
if (firstCell != null)
{
Excel.Range titleRow = firstCell.EntireRow as Excel.Range;
titleRow.RowHeight *= 2;
titleRow.AutoFilter(Field: 1, Operator: Excel.XlAutoFilterOperator.xlAnd);
titleRow.AutoFit();
Marshal.ReleaseComObject(titleRow);
Excel.Window xlWindow = this.xlApp.ActiveWindow;
xlWindow.SplitRow = 1;
xlWindow.FreezePanes = true;
Marshal.ReleaseComObject(xlWindow);
}
Marshal.ReleaseComObject(firstCell);
}
private void buttonPrint_Click(object sender, EventArgs e)
{
object misValue = System.Reflection.Missing.Value;
FormWindowState fws = new FormWindowState();
fws = Program.MainWindow.WindowState;
SaveFileDialog sfd = new SaveFileDialog();
string localFilePath = null;
sfd.Filter = "PNG|*.png|JPEG(*.JPG *.JPEG *.JPE)|*.jpg|BMP|*.bmp";
if (sfd.ShowDialog() == DialogResult.OK && sfd.FileName != null && sfd.FileName != "")
{
localFilePath = sfd.FileName.ToString();
}
else
{
return;
}
FileStream performanceStream = new FileStream(localFilePath, FileMode.Create);
try
{
//生成性能图表截图
Program.MainWindow.WindowState = FormWindowState.Maximized;
Program.MainWindow.Refresh();
Bitmap bit = new Bitmap(GraphList.Width - 30, GraphList.Height - 55);
Graphics g = Graphics.FromImage(bit);
g.CopyFromScreen(GraphList.PointToScreen(System.Drawing.Point.Empty), System.Drawing.Point.Empty, GraphList.Size);
bit.Save(performanceStream, System.Drawing.Imaging.ImageFormat.Png);
Program.MainWindow.WindowState = fws;
//取数据
List <string> dsuuids = new List <string>();
Pool pool = Helpers.GetPoolOfOne(XenObject.Connection);
List <DesignedGraph> dglist = new List <DesignedGraph>();
if (pool != null)
{
if (XenObject == null)
{
return;
}
// int index = GraphList.Count - 1;
// if (GraphList.AuthorizedRole)
// {
// GraphList.ExchangeGraphs(1, 0);
// GraphList.ExchangeGraphs(0, 1);
//GraphList.ExchangeGraphs(index-1, index);
//GraphList.SaveGraphs(null);
// }
Dictionary <string, string> gui_config = Helpers.GetGuiConfig(pool);
int i = 0;
while (true)
{
DesignedGraph dg = new DesignedGraph();
string key = Palette.GetLayoutKey(i, XenObject);
if (!gui_config.ContainsKey(key))
{
break;
}
string[] dslist = gui_config[key].Split(',');
foreach (string ds in dslist)
{
AddDataSource(string.Format("{0}:{1}:{2}", XenObject is Host ? "host" : "vm", Helpers.GetUuid(XenObject), ds), dsuuids, dg);
}
key = Palette.GetGraphNameKey(i, XenObject);
if (gui_config.ContainsKey(key))
{
dg.DisplayName = gui_config[key];
}
dglist.Add(dg);
i++;
}
}
//导出到Excel
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkBook = xlApp.Workbooks.Add();
Worksheet xlWorkSheet;
Dictionary <string, string> config = Helpers.GetGuiConfig(pool);
for (int j = 0; j < dglist.Count; j++)
{
xlWorkBook.Sheets.Add(misValue, misValue, 1, XlSheetType.xlWorksheet);
xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);
//每个图表创建一个对应的表单（CPU性能,内存性能...）
xlWorkSheet.Name = dglist[j].DisplayName;
//取每个表单的标签名称（cpu0，cpu1...）
string key = Palette.GetLayoutKey(j, XenObject);
String[] dslist = config[key].Split(',');
for (int i = 0; i < dslist.Length; i++)
{
// xlWorkSheet.Cells[i + 2, 1] = dslist[i];
xlWorkSheet.Cells[i + 2, 1] = DataKey.items[i];
Microsoft.Office.Interop.Excel.Range allColumn = xlWorkSheet.Columns;
allColumn.AutoFit();
}
}
try
{
xlWorkBook.SaveAs(localFilePath.Substring(0, localFilePath.LastIndexOf(".")) + ".xls", XlFileFormat.xlWorkbookNormal, "", "", false, false, XlSaveAsAccessMode.xlExclusive, true, true);
xlWorkBook.Close(true, localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1) + ".xls", false);
xlApp.Quit();
}
catch { }
ExportSuccessfullyDialog esd = new ExportSuccessfullyDialog();
esd.ShowDialog();
}
finally
{
performanceStream.Close();
}
}
/// <summary>
/// 创建表格
/// </summary>
/// <param name="m_Sheet"></param>
public static void CreateTable(Excel._Worksheet m_Sheet, DataInterface dataInterfaceExcel, string[] resource, bool IsQaulity)//string title, Excel._Worksheet m_Sheet, Excel._Workbook m_Book, int startrow
{
try
{
//打印时间
Excel.Range range = m_Sheet.get_Range("A1", "H1"); //取得单元格范围
range.MergeCells = true; //合并单元格
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 12; //字体大小
//range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//单元格横向居中
//range.HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;//单元格纵向靠右
range.Value2 = resource[0];//当前打印时间
//logo
range = m_Sheet.get_Range("A2", "H2"); //取得单元格范围
range.MergeCells = true; //合并单元格
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 28; //字体大小
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格横向居中
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格纵向居中
//表头名称
range = m_Sheet.get_Range("A3", "H3"); //取得单元格范围
range.MergeCells = true; //合并单元格
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 24; //字体大小
range.Font.Bold = true; //字体加粗
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格横向居中
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格纵向居中
range.Value2 = resource[1]; //"加工报表";
range.EntireRow.AutoFit();
int startrow;//等级个数
int qualSum = 1;
if (dataInterfaceExcel.QualityGradeSum > qualSum)
{
qualSum = dataInterfaceExcel.QualityGradeSum;
}
int sizeSum = 1;
if (dataInterfaceExcel.WeightOrSizeGradeSum > sizeSum)
{
sizeSum = dataInterfaceExcel.WeightOrSizeGradeSum;
}
if (IsQaulity)
{
startrow = sizeSum * qualSum;
}
else
{
startrow = sizeSum;
}
//客户信息表格格式 dataInterface.WeightOrSizeGradeSum
#region
range = m_Sheet.get_Range("A4", "H7");
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 14; //字体大小
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格横向居中
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格纵向居中
range.Borders.LineStyle = Excel.XlLineStyle.xlDot; //设置边框
range.Borders.Weight = Excel.XlBorderWeight.xlThin; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous; //设置顶部边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous; //设置底部边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; //设置左边边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous; //设置右边边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.EntireRow.AutoFit();
range.EntireColumn.AutoFit();
range = m_Sheet.get_Range("A4", "A4"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[2]; //客户名称
range = m_Sheet.get_Range("D4", "D4"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[3]; //"农场名称";
range = m_Sheet.get_Range("G4", "G4"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[4]; //"水果品种";
range = m_Sheet.get_Range("A5", "A5"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[5]; //"个数汇总";
range = m_Sheet.get_Range("D5", "D5"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[6]; //"重量汇总";
range = m_Sheet.get_Range("F5", "F5"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify; //单元格横向靠左
range.Value2 = resource[7]; //"吨";
range = m_Sheet.get_Range("G5", "G5"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[8]; //"箱数汇总";
range = m_Sheet.get_Range("A6", "A6"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[9]; //"平均果重";
range = m_Sheet.get_Range("C6", "C6"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify; //单元格横向靠左
range.Value2 = resource[10]; //"克";
range = m_Sheet.get_Range("D6", "D6"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[11]; //"分选程序";
range = m_Sheet.get_Range("G6", "G6"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[22]; //"序列号";
range = m_Sheet.get_Range("A7", "A7"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[12]; //"开始时间";
range = m_Sheet.get_Range("D7", "D7"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[13]; //"结束时间";
#endregion
//等级信息表格格式 dataInterface.WeightOrSizeGradeSum
#region
range = m_Sheet.Range[m_Sheet.Cells[9, 1], m_Sheet.Cells[9 + startrow, 8]]; //取得单元格范围
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 14; //字体大小
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格横向居中
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //单元格纵向居中
range.Borders.LineStyle = Excel.XlLineStyle.xlDot; //设置边框
range.Borders.Weight = Excel.XlBorderWeight.xlThin; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous; //设置顶部边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous; //设置底部边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; //设置左边边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous; //设置右边边框
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
range.EntireRow.AutoFit();
range.EntireColumn.AutoFit();
#endregion
////列标题
#region
range = m_Sheet.get_Range("A9", "A9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[14]; //等级名称
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("B9", "B9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[15]; //尺寸/重量
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("C9", "C9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[16]; //等级个数
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("D9", "D9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[17]; //个数百分比
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("E9", "E9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[18] + "(kg)"; //等级重量
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("F9", "F9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[19]; //重量百分比
// range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("G9", "G9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[20]; //箱数
//range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
range = m_Sheet.get_Range("H9", "H9"); //取得单元格范围
range.Font.Bold = true; //字体加粗
range.Value2 = resource[21]; //箱数百分比
// range.Cells.Interior.Color = System.Drawing.Color.FromArgb(0, 204, 151).ToArgb();//设置单元格颜色
#endregion
//自动设置列宽 放到最后
range = m_Sheet.Columns;
range.AutoFit();
}
catch (Exception ex)
{
Trace.WriteLine("ExcelReportFunc中函数备份设置CreateTable出错" + ex);
#if REALEASE
GlobalDataInterface.WriteErrorInfo("ExcelReportFunc中函数备份设置CreateTable出错" + ex);
#endif
}
}
private JObject GetJsonAndFormat(Excel.ListObject table)
{
JObject result = new JObject();
Excel.Range headerRow = table.HeaderRowRange;
Excel.Range entries = table.Range.Rows;
int columnsCount = headerRow.Columns.Count;
int rowsCount = entries.Rows.Count;
for (int i = 2; i <= rowsCount; i++)
{
Excel.Range entry = entries[i];
Excel.Range cell = entry.Cells[1, VocabularyProperties.Key];
string key = string.Empty;
if (cell.Value2 == null)
{
continue;
}
else
{
key = cell.Value2.ToString();
if (string.IsNullOrEmpty(key))
{
continue;
}
}
JObject vocabulary = new JObject();
result.Add(key, vocabulary);
for (int j = 1; j <= columnsCount; j++)
{
cell = entry.Cells[1, j];
string value = string.Empty;
if (cell.Value2 != null)
{
value = cell.Value2.ToString();
value = value.Trim();
}
VocabularyProperties property = (VocabularyProperties)j;
switch (property)
{
case VocabularyProperties.Index:
{
vocabulary.Add("index", value);
}
break;
case VocabularyProperties.Content:
{
vocabulary.Add("content", value);
}
break;
case VocabularyProperties.Furigana:
{
vocabulary.Add("furigana", value);
}
break;
case VocabularyProperties.Romanji:
{
value = value.ToLower();
vocabulary.Add("romanji", value);
}
break;
case VocabularyProperties.HanViet:
{
value = value.ToUpper();
vocabulary.Add("han-viet", value);
}
break;
case VocabularyProperties.Meaning:
{
value = this.UppercaseFirst(value);
vocabulary.Add("meaning", value);
}
break;
case VocabularyProperties.Group:
{
vocabulary.Add("group", value);
}
break;
case VocabularyProperties.Key:
{
vocabulary.Add("key", value);
}
break;
default:
{
}
break;
}
if (!cell.HasFormula &&
cell.Value2 != null &&
cell.Value2.ToString() != value)
{
cell.Value = value;
}
}
}
entries.AutoFit();
entries.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public Excel ColumnAutoFit(int rowIndex, int columnIndex)
{
App.Range range = (App.Range)mApp.Columns[columnIndex];
range.AutoFit();
return(this);
}
//-->函数：打开并修改excel
public static void OpenExcel(string filePath, string[] array1, string[] array2, string[] array3)
{
MSExcel.Application excel; //创建一个excel对象
beforeTime = DateTime.Now; //获取excel开始启动时间
excel = new MSExcel.ApplicationClass(); //创建实例,这时在系统进程中会多出一个excel进程
afterTime = DateTime.Now; //获取excel启动结束的时间
try
{
object missing = Missing.Value; //Missing 用于调用带默认参数的方法
excel.Visible = false; //不显示excel文档
excel.Application.Workbooks.Open(filePath); //Open Original Excel File
MSExcel.Workbook myBook;
MSExcel.Worksheet mySheet;
myBook = excel.Workbooks[1]; //获取excel程序的工作簿
mySheet = (MSExcel.Worksheet)myBook.Worksheets[1]; //获取Workbook的第一张作表
#region 写入数据
//mySheet.Cells[10, 10] = "";
//查找最下面一行空行
int rowsNum = mySheet.UsedRange.Rows.Count;
//mySheet.UsedRange.RowHeight
for (int i = 0; i < array1.Length; i++)
{
mySheet.Cells[rowsNum + 1, i + 1] = array1[i];
mySheet.Cells[rowsNum + 2, i + 1] = array2[i];
mySheet.Cells[rowsNum + 3, i + 1] = array3[i];
}
#endregion
#region 设置某一区域的格式
MSExcel.Range r = mySheet.Columns; //获取矩形选择框
r.AutoFit();
#endregion
excel.DisplayAlerts = false; //禁止弹出提示框
mySheet.SaveAs(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//另存为
#region 释放资源
//ReleaseComObject 方法递减运行库可调用包装的引用计数。详细信息见MSDN
Marshal.ReleaseComObject(myBook);
Marshal.ReleaseComObject(mySheet);
Marshal.ReleaseComObject(excel);
Marshal.ReleaseComObject(r);
myBook.Close();
excel.Workbooks.Close();
r = null;
mySheet = null;
myBook = null;
missing = null;
excel.Quit();
excel = null;
#endregion
}
catch (Exception e)
{
KillExcelProcess(); //杀掉进程
}
finally
{
//可以把KillExcelProcess();放在该处从而杀掉Excel的进程
KillExcelProcess();
}
}
/// <summary>
/// 自动调整，设置自动换行以及自动调整列宽
/// </summary>
/// <param name="range">Range对象</param>
public void AutoAdjustment(Excel.Range range)
{
range.WrapText = true;
range.AutoFit();
}
internal static void SaveToXls(ProductDatabase productDatabase, string filename)
{
Process[] excelProcsOld = Process.GetProcessesByName("EXCEL");
#region Запуск Эксель
Excel.Application excelApp = new Excel.Application();
Excel.Workbooks workbooks;
Excel.Workbook excelBook;
workbooks = excelApp.Workbooks;
excelBook = workbooks.Add();
Excel.Worksheet excelSheet = (Excel.Worksheet)(excelBook.Sheets[1]);
//excelSheet.get_Range("B1", "B1").EntireColumn.get_Resize(Type.Missing,40); //для столбца
#endregion
#region Общий стиль
Excel.Range excelcells = (Excel.Range)excelSheet.Columns["A:AC", Type.Missing];
excelcells.ColumnWidth = 25;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
excelcells.Font.Name = "Times New Roman";
excelcells.Font.Size = 10;
//excelcells = (Excel.Range)excelSheet.Rows[Type.Missing, "1:"+(lastRow-1).ToString()];
excelcells.Cells.WrapText = true;
excelcells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
excelcells.Borders.ColorIndex = 0;
excelcells.Borders.Weight = Excel.XlBorderWeight.xlThin;
#endregion
#region Запись шапки
int lastRow = 1;
excelSheet.Cells[lastRow, 1] = "№ п/п";
excelSheet.Cells[lastRow, 2] = "Место эксплуатации изделия";
excelSheet.Cells[lastRow, 3] = "Наименование изделия";
excelSheet.Cells[lastRow, 4] = "Индекс";
excelSheet.Cells[lastRow, 5] = "Номер заводской";
excelSheet.Cells[lastRow, 6] = "Номер инвентарный";
excelSheet.Cells[lastRow, 7] = "Номенклатура ВВТ";
excelSheet.Cells[lastRow, 8] = "Краткое назначение изделия";
excelSheet.Cells[lastRow, 9] = "Организация-изготовитель";
excelSheet.Cells[lastRow, 10] = "Дата изготовления(дд.мм.гггг)";
excelSheet.Cells[lastRow, 11] = "Срок гарантии";
excelSheet.Cells[lastRow, 12] = "Дата окончания обязательств (дд.мм.гггг)";
excelSheet.Cells[lastRow, 13] = "Срок эксплуатации изделия";
excelSheet.Cells[lastRow, 14] = "Наименование документа, подтверждающий принятие изделия на ответственное хранение (аренду)";
excelSheet.Cells[lastRow, 15] = "Номер документа, принятия на ответственное хранение (аренду)";
excelSheet.Cells[lastRow, 16] = "Дата принятия объекта на ответственное хранение (аренду) (дд.мм.гггг)";
excelSheet.Cells[lastRow, 17] = "№ приказа о вводе в эксплуатацию";
excelSheet.Cells[lastRow, 18] = "Дата приказа о вводе в эксплуатацию (дд.мм.гггг)";
excelSheet.Cells[lastRow, 19] = "Первоначальная стоимость, руб.";
excelSheet.Cells[lastRow, 20] = "Драг. мелаллы: Золото, грамм";
excelSheet.Cells[lastRow, 21] = "Драг. мелаллы: Серебро, грамм";
excelSheet.Cells[lastRow, 22] = "Драг. мелаллы: Платина, грамм";
excelSheet.Cells[lastRow, 23] = "Драг. мелаллы: Мпг, грамм";
excelSheet.Cells[lastRow, 24] = "Материально ответственное лицо (ФИО)";
excelSheet.Cells[lastRow, 25] = "Состояние изделия";
excelSheet.Cells[lastRow, 26] = "Дата проведения последнего сервисного обслуживания";
excelSheet.Cells[lastRow, 27] = "Планируемый год списания";
excelSheet.Cells[lastRow, 28] = "Примечание";
excelSheet.Cells[lastRow, 29] = "Статус";
lastRow++;
for (int i = 1; i <= 29; i++)
{
excelSheet.Cells[lastRow, i] = i;
}
lastRow++;
#endregion
#region Заполнение ячеек
foreach (Product src in productDatabase.Products)
{
excelSheet.Cells[lastRow, 1] = src.Number;
excelSheet.Cells[lastRow, 2] = src.ExploitationPlace;
excelSheet.Cells[lastRow, 3] = src.Name;
excelSheet.Cells[lastRow, 4] = src.Index;
excelSheet.Cells[lastRow, 5] = src.FactoryNumber;
excelSheet.Cells[lastRow, 6] = src.InventoryNumber;
excelSheet.Cells[lastRow, 7] = src.BbtNomenclature;
excelSheet.Cells[lastRow, 8] = src.Purpose;
excelSheet.Cells[lastRow, 9] = src.Manufacturer;
excelSheet.Cells[lastRow, 10] = src.ProductionDate;
excelSheet.Cells[lastRow, 11] = src.Guarantee;
excelSheet.Cells[lastRow, 12] = src.EndDate;
excelSheet.Cells[lastRow, 13] = src.ExploitationPeriod;
excelSheet.Cells[lastRow, 14] = src.ConfirmationDoc;
excelSheet.Cells[lastRow, 15] = src.ConfirmationNumber;
excelSheet.Cells[lastRow, 16] = src.ReceivingDate;
excelSheet.Cells[lastRow, 17] = src.OrderNumber;
excelSheet.Cells[lastRow, 18] = src.ExploitationDate;
excelSheet.Cells[lastRow, 19] = src.PrimaryCost;
excelSheet.Cells[lastRow, 20] = src.Aurum;
excelSheet.Cells[lastRow, 21] = src.Argentum;
excelSheet.Cells[lastRow, 22] = src.Platinum;
excelSheet.Cells[lastRow, 23] = src.Mpg;
excelSheet.Cells[lastRow, 24] = src.ResponsiblePerson;
excelSheet.Cells[lastRow, 25] = src.Condition;
excelSheet.Cells[lastRow, 26] = src.ServiceDate;
excelSheet.Cells[lastRow, 27] = src.Write_off;
excelSheet.Cells[lastRow, 28] = src.Comment;
excelSheet.Cells[lastRow, 29] = src.State;
lastRow++;
}
#endregion
//Автоподгон по ширине
excelcells.AutoFit();
//Подцветка строки и столбца с нумерацией
excelcells = (Excel.Range)excelSheet.Columns["A:A", Type.Missing];
excelcells.Interior.ColorIndex = 34;
excelcells = (Excel.Range)excelSheet.Rows["2:2", Type.Missing];
excelcells.Interior.ColorIndex = 34;
//Сохранение файла
// excelBook.PrintPreview();
excelBook.SaveAs(@filename, Excel.XlFileFormat.xlExcel8);
Marshal.ReleaseComObject(excelSheet);
excelBook.Close(true);
Marshal.ReleaseComObject(excelBook);
Marshal.ReleaseComObject(workbooks);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
excelSheet = null;
excelBook = null;
excelApp = null;
//убиваем все рабочие процессы Экселя
Process[] excelProcsNew = Process.GetProcessesByName("EXCEL");
foreach (Process procNew in excelProcsNew)
{
int exist = 0;
foreach (Process procOld in excelProcsOld)
{
if (procNew.Id == procOld.Id)
{
exist++;
}
}
if (exist == 0)
{
procNew.Kill();
}
}
}
/// <summary>
/// 导出Excel表格
/// </summary>
/// <param name="filePath"></param>
public static void CreateExcel(string filePath, DataInterface dataInterfaceExcel, string[] resource, bool IsQaulity, bool IsSize)
{
try
{
string FilePath = filePath;
FileInfo fi = new FileInfo(FilePath);
if (fi.Exists)//判断文件是否已经存在，如果存在就删除！
{
fi.Delete();
}
//if (sheetNames != null && sheetNames != "")
//{
Excel.Application m_Excel = new Excel.Application(); //创建一个Excel对象(同时启动EXCEL.EXE进程)
// m_Excel.Visible = true;
m_Excel.SheetsInNewWorkbook = 3; //工作表的个数
Excel._Workbook m_Book = (Excel._Workbook)(m_Excel.Workbooks.Add(Missing.Value)); //添加新工作簿
Excel._Worksheet m_Sheet = m_Book.Worksheets[1];
CreateTable(m_Sheet, dataInterfaceExcel, resource, IsQaulity);
FillTableData(m_Sheet, dataInterfaceExcel, 10, IsQaulity, IsSize);
//自动设置列宽 放到最后
Excel.Range range = m_Sheet.Columns;
range.AutoFit();
//设置列宽
range = m_Sheet.get_Range("B7", "B7"); //取得单元格范围
range.ColumnWidth = 15;
range = m_Sheet.get_Range("G4", "G4"); //取得单元格范围
range.ColumnWidth = 15;
//最后载入logo图片
range = m_Sheet.get_Range("A2", "H2"); //取得单元格范围
Bitmap bitmap = new Bitmap(PrintProtocol.logoPathName); //创建位图对象;
float width = (float)(bitmap.Width * range.Height / bitmap.Height);
m_Sheet.Shapes.AddPicture(PrintProtocol.logoPathName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
range.Left + range.Width / 2 - width / 2, range.Top, width, range.Height);
#region 保存Excel,清除进程
m_Book.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//保存Excel
m_Book.Close(false, Missing.Value, Missing.Value);
m_Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);
GC.Collect();
m_Book = null;
m_Sheet = null;
m_Excel = null;
#endregion
// }
}
catch (Exception ex)
{
Trace.WriteLine("ExcelReportFunc中函数备份设置CreateTable出错" + ex);
#if REALEASE
GlobalDataInterface.WriteErrorInfo("ExcelReportFunc中函数备份设置CreateTable出错" + ex);
#endif
}
}