public void SpremanjeFaktura()
{
string OtvorenaFaktura = Application.StartupPath + "\\CMR\\CMR.xlsx";
//unos podataka
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
sheets = theWorkbook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
excelApp.Workbooks.Open(OtvorenaFaktura);
var BrojOtpremnice = (worksheet.Cells[1, 10] as Microsoft.Office.Interop.Excel.Range).Value;
int NoviBrojOtpremnice = Convert.ToInt16(BrojOtpremnice) + 1;
excelApp.Cells[1, 10] = NoviBrojOtpremnice;
excelApp.ActiveWorkbook.Save();
theWorkbook.Close(0);
excelApp.Quit();
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
Process.Start(OtvorenaFaktura);
}
public void export(DataTable dt)
{
xlApp = new Excel.Application();
xlApp.Visible = true;
try
{
xlApp.Workbooks.Add(Type.Missing);
xlApp.Interactive = false;
xlApp.EnableEvents = false;
xlSheet = (Excel.Worksheet)xlApp.Sheets[1];
xlSheet.Name = "Итог";
int collInd = 0;
int rowInd = 0;
string data = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
data = dt.Columns[i].ColumnName.ToString();
xlSheet.Cells[1, i + 1] = data;
xlSheetRange = xlSheet.get_Range("A1:Z1", Type.Missing);
xlSheetRange.WrapText = true;
xlSheetRange.Font.Bold = true;
}
for (rowInd = 0; rowInd < dt.Rows.Count; rowInd++)
{
for (collInd = 0; collInd < dt.Columns.Count; collInd++)
{
data = dt.Rows[rowInd].ItemArray[collInd].ToString();
xlSheet.Cells[rowInd + 2, collInd + 1] = data;
}
}
xlSheetRange = xlSheet.UsedRange;
xlSheetRange.Columns.AutoFit();
xlSheetRange.Rows.AutoFit();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
xlApp.Visible = true;
xlApp.Interactive = true;
xlApp.ScreenUpdating = true;
xlApp.UserControl = true;
}
}
public ExcelWriter(string filename, ReportType type)
{
Report = type;
oApplication = new Microsoft.Office.Interop.Excel.Application();
oWorkbook = null;
oWorksheet = null;
string outputPath = Filename = System.Configuration.ConfigurationManager.AppSettings["OutputLocation"];
if (type == ReportType.MonthyTally)
{
Filename = Path.Combine(outputPath, "MonthlyTally");
}
if (!Directory.Exists(Filename))
Directory.CreateDirectory(Filename);
Filename = Path.Combine(Filename, filename);
if (File.Exists(Filename))
File.Delete(Filename);
logger = Logger.CreateLogger();
}
/// <summary>
/// Expecting to get a list of donation for a single doner
/// </summary>
/// <param name="donations"></param>
/// <returns></returns>
private double WriteYearlyDonerReport(List<Donation> donations, Doner doner)
{
double total = 0.0;
try
{
if (oApplication == null)
{
logger.WriteError("Unable to start an excel sheet");
return total;
}
oApplication.Visible = false;
oWorkbook = oApplication.Workbooks.Add(Type.Missing);
oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1];
FormatFont();
int maxRowDepth = 0;
int donationStartingRow = 10;
MergeColumns(1, 1, 10); oWorksheet.Cells[1, 1] = "Christ Gospel Church of Tacoma WA";
MergeColumns(2, 1, 10); oWorksheet.Cells[2, 1] = "3909 Steilacoom Blvd SW";
MergeColumns(3, 1, 10); oWorksheet.Cells[3, 1] = "Lakewood, WA 98499";
MergeColumns(4, 1, 10); oWorksheet.Cells[4, 1] = "(253) 584-3904";
MergeColumns(6, 4, 8); oWorksheet.Cells[6, 4] = String.Format("As of {0:dd/MM/yyyy}", donations.Last().DonationTime.AddMonths(1).AddDays(-1.0));
MergeColumns(8, 1, 10); oWorksheet.Cells[8, 1] = String.Format("{0}:", doner.Name);
// Fill in all the columns (categories) with the donations
foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category)))
{
int row = donationStartingRow;
oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString();
foreach (var item in donations)
{
string value = item.Get(currentCategory).ToString();
if (value.Equals("0"))
value = String.Empty;
oWorksheet.Cells[++row, (int)currentCategory] = value;
}
// keep a running total of how deep we go (rows) into the spreadsheet
maxRowDepth = row > maxRowDepth ? row : maxRowDepth;
}
// Fill out the first column
int rowNum = donationStartingRow;
oWorksheet.Cells[rowNum, 1] = "Date";
foreach (var item in donations)
{
oWorksheet.Cells[++rowNum, 1] = String.Format("{0:MM/yyyy}", item.DonationTime);
}
// donationStartingRow + 1 == where the actual donations start (first row is column name)
int totalsRow = CalculateTotals(donationStartingRow+1, donationStartingRow + 1 + donations.Count , out total);
// Some formatting of Donation Name row (font and bold)
oWorksheet.Rows[donationStartingRow, Type.Missing].Font.Size = 9;
oWorksheet.Rows[donationStartingRow, Type.Missing].Font.Bold = true;
// Totals row bold.
oWorksheet.Cells[totalsRow, Type.Missing].Font.Bold = true;
rowNum = totalsRow + 4;
MergeColumns(++rowNum, 1, 13); oWorksheet.Cells[rowNum, 1] =
"The goods or services that Christ Gospel Church of Tacoma provided in return for your contribution consisted entirely of intangible religious benefits.";
++rowNum; ++rowNum;
oWorksheet.Cells[rowNum, 1] = "Sincerely,";
oWorksheet.Cells[++rowNum, 1] = "Treasury Department";
int lastCol = (int)Donation.Category.Other + 1;
SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[rowNum, lastCol]]);
}
catch (Exception e)
{
logger.WriteError("Failure while writing excel file. Message: {0}, Stack: {1}", e.Message, e.StackTrace);
}
finally
{
Cleanup();
}
return total;
}
private double WriteYearlyDonerReportBackup(List<Donation> donations)
{
double total = 0.0;
try
{
if (oApplication == null)
{
logger.WriteError("Unable to start an excel sheet");
return total;
}
oApplication.Visible = false;
oWorkbook = oApplication.Workbooks.Add(Type.Missing);
oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1];
FormatFont();
int maxRowDepth = 0;
foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category)))
{
int row = 1;
oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString();
foreach (var item in donations)
{
oWorksheet.Cells[++row, (int)currentCategory] = item.Get(currentCategory).ToString();
if (currentCategory == Donation.Category.Other)
{
oWorksheet.Cells[1, (int)currentCategory + 1] = "Specify";
oWorksheet.Cells[row, (int)currentCategory + 1] = item.OtherCategory;
}
}
maxRowDepth = row > maxRowDepth ? row : maxRowDepth;
}
int totalsRow = CalculateTotals(2, maxRowDepth, out total);
// Fill out the first column
int r = 1;
oWorksheet.Cells[r, 1] = "Date";
foreach (var item in donations)
{
oWorksheet.Cells[++r, 1] = item.DonationTime.ToShortDateString();
}
// Some formatting:
oWorksheet.Rows[1, Type.Missing].Font.Size = 9;
oWorksheet.Rows[1, Type.Missing].Font.Bold = true;
oWorksheet.Cells[maxRowDepth + 3, 2].Font.Bold = true;
oWorksheet.Columns[1].Item(1).ColumnWidth = 10.71;
total = Double.Parse(oWorksheet.Cells[maxRowDepth + 3, 2].Value);
int lastRow = totalsRow + 3;
int lastCol = (int)Donation.Category.Other + 1;
// Format cells to have boxes and set print area
AddBoxAroundEachCellInRange(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[totalsRow, (int)Donation.Category.Other + 1]]);
SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[lastRow, lastCol]]);
}
catch { }
finally
{
Cleanup();
}
return total;
}
private void WriteExcelData()
{
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Title = "Save REPORT File";
saveFile.DefaultExt = "xlsx";
saveFile.Filter = "Excel (*.xlsx)|*.xlsx";
saveFile.FilterIndex = 0;
saveFile.RestoreDirectory = true;
string path = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
if (saveFile.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range Range2 = null;
object missing = Type.Missing;
Random Rnd = new Random();
int RndNum = Rnd.Next();
byte[] Template = Properties.Resources.Report;
File.WriteAllBytes(Path.GetTempPath() + RndNum + ".xlsx", Template);
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.DisplayAlerts = false;
wb = excel.Workbooks.Open(Path.GetTempPath() + RndNum + ".xlsx",
missing, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing);
// 원천기술 분석
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.get_Item("Page 5");
ws.PageSetup.Zoom = false;
int cntTechnologyLevel = mainFrm.CriticalTechnology1.Count;
for (int i = 0; i < cntTechnologyLevel; i++)
{
string[] temp = new string[] { mainFrm.CriticalTechnology1[i],
string.Empty,
mainFrm.CriticalTechnology2[i] + "%",
mainFrm.CriticalTechnology3[i] + "%",
mainFrm.CriticalTechnology4[i],
mainFrm.CriticalTechnology5[i],
mainFrm.CriticalTechnology6[i],
mainFrm.CriticalTechnology7[i],
mainFrm.CriticalTechnology8[i],
mainFrm.CriticalTechnology9[i] };
Range2 = ws.Range["B" + (15 + i), "K" + (15 + i)];
Range2.Value = temp;
}
// 기술 수준 파악은 최대가 4개임
if (cntTechnologyLevel < 4)
{
for (int i = 4; i > cntTechnologyLevel; i--)
{
Range2 = ws.Range["B" + (15 + i - 1), "K" + (15 + i - 1)];
Range2.EntireRow.Delete();
}
}
int cntTechnology = mainFrm.OrigrinalTechnology1.Count;
// 원천기술 식별은 5개가 Default이며, 5개가 초과할 경우에는 Row를 Insert하고
if (cntTechnology > 5)
{
for (int i = 0; i < cntTechnology - 5; i++)
{
Range2 = ws.Range["B6", "K6"];
Range2.Insert();
}
}
else if (cntTechnology < 5) // 5개 보다 작을때는 그만큼 Row를 삭제해야함.
{
for (int i = 5; i > cntTechnology; i--)
{
Range2 = ws.Range["B" + (5 + i - 1), "K" + (5 + i - 1)];
Range2.EntireRow.Delete();
}
}
for (int i = 0; i < cntTechnology; i++)
{
string[] temp = new string[] { mainFrm.OrigrinalTechnology1[i], mainFrm.OrigrinalTechnology2[i] };
Range2 = ws.Range["B" + (5 + i), "K" + (5 + i)];
Range2.Value = temp;
}
wb.SaveAs(saveFile.FileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
wb.Close(false, missing, missing);
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
wb.Close(false, missing, missing);
excel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Range2);
excel = null;
wb = null;
ws = null;
Range2 = null;
}
}
}
/// <summary>
/// 把DataTable导出到EXCEL
/// </summary>
/// <param name="reportName">报表名称</param>
/// <param name="dt">数据源表</param>
/// <param name="saveFileName">Excel全路径文件名</param>
/// <returns>导出是否成功</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空！";
return(false);
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
return(false);
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
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;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return(fileSaved);
}
public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
{
ws.Cells[x, y] = value;
}
public string ExcelExport(System.Data.DataTable DT, string title)
{
try
{
//创建Excel
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(System.Type.Missing);
//创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
//如果数据中存在数字类型 可以让它变文本格式显示
ExcelSheet.Cells.NumberFormat = "@";
//设置工作表名
ExcelSheet.Name = title;
//设置Sheet标题
string start = "A1";
string end = ChangeASC(DT.Columns.Count) + "1";
Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计)
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_Range.Font.Size = 22; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
ExcelSheet.Cells[1, 1] = title; //Excel单元格赋值
_Range.EntireColumn.AutoFit(); //自动调整列宽
//写表头
for (int m = 1; m <= DT.Columns.Count; m++)
{
ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString();
start = "A2";
end = ChangeASC(DT.Columns.Count) + "2";
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Font.Size = 15; //设置字体大小
_Range.Font.Bold = true; //加粗
_Range.Font.Name = "宋体"; //设置字体的种类
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
//写数据
for (int i = 0; i < DT.Rows.Count; i++)
{
for (int j = 1; j <= DT.Columns.Count; j++)
{
//Excel单元格第一个从索引1开始
// if (j == 0) j = 1;
ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString();
}
}
//表格属性设置
for (int n = 0; n < DT.Rows.Count + 1; n++)
{
start = "A" + (n + 3).ToString();
end = ChangeASC(DT.Columns.Count) + (n + 3).ToString();
//获取Excel多个单元格区域
_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);
_Range.Font.Size = 12; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式 _Range.EntireColumn.AutoFit(); //自动调整列宽
}
ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
//弹出保存对话框,并保存文件
Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
sfd.DefaultExt = ".xlsx";
sfd.Filter = "Office 2007 File|*.xlsx|Office 2000-2003 File|*.xls|所有文件|*.*";
if (sfd.ShowDialog() == true)
{
if (sfd.FileName != "")
{
ExcelBook.SaveAs(sfd.FileName); //将其进行保存到指定的路径
// MessageBox.Show("导出文件已存储为: " + sfd.FileName, "温馨提示");
}
}
//释放可能还没释放的进程
ExcelBook.Close();
ExcelApp.Quit();
// PubHelper.Instance.KillAllExcel(ExcelApp);
return(sfd.FileName);
}
catch
{
// MessageBox.Show("导出文件保存失败！", "警告！");
return(null);
}
}
/// <summary>
/// 将DataTable中的数据导出到Excel（支持Excel2003和Excel2007）特点： 稳定 可导入 缺点：速度慢 (王伦清)
/// </summary>
/// <param name="dt"> DataTable</param>
/// <param name="url">Excel保存的路径DataTable</param>
/// <returns>导出成功返回True，否则返回false</ returns >
public bool ExportExcel2(DataTable dt, string url)
{
bool falge = false;
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
//设置Excel不可见
objExcel.Visible = false;
objExcel.DisplayAlerts = false;
//设置Excel字段类型全部为字符串
objsheet.Cells.NumberFormat = "@";
//向Excel中写入表格的标头
int displayColumnsCount = 1;
for (int i = 0; i < dt.Columns.Count; i++)
{
objExcel.Cells[1, displayColumnsCount] = dt.Columns[i].ColumnName.Trim();
displayColumnsCount++;
}
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row < dt.Rows.Count; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < dt.Columns.Count; col++)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dt.Rows[row][col].ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{ }
}
}
//保存文件
objWorkbook.SaveAs(url, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
falge = true;
}
catch (Exception ex)
{
falge = false;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
//杀死进程
KillProcess("Excel");
objsheet = null;
objWorkbook = null;
objExcel = null;
}
return(falge);
}
public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
//ws：setting value worksheet X row Y column
{
ws.Cells[x, y] = value;
}
public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName) //add a worksheet
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return(s);
}
private void exportToExcel()
{
try
{
//Create an instance for word app
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//Set animation status for word application
//excel.ShowAnimation = false;
//Set status for word application is to be visible or not.
excel.Visible = false;
//Create a missing variable for missing value
object missing = System.Reflection.Missing.Value;
Object oTemplatePath = System.IO.Path.GetFullPath("invoiceTemplate.xltx");
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(oTemplatePath);
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
worksheet.Cells[12, 1] = tbName.Text;
worksheet.Cells[13, 1] = tbAddress.Text;
worksheet.Cells[14, 1] = tbCity.Text + " " + cmbProvince.Text + " " + tbPostal.Text;
worksheet.Cells[16, 1] = DateTime.Now.ToString("dd.MM.yyyy");
worksheet.Cells[18, 1] = "Invoice #" + o.id;
for (int i = 0; i < services.Count; i++)
{
worksheet.Cells[21 + i, 1] = services[i].Description;
worksheet.Cells[21 + i, 3] = services[i].Price;
worksheet.Cells[21 + i, 4] = services[i].Quantity;
}
if (chbCalculateTax.IsChecked == true) // is checked means NO TAX
{
worksheet.Cells[28, 4] = 0;
worksheet.Cells[29, 4] = 0;
}
//Show discount if exists
decimal disc = 0;
if (decimal.TryParse(tbDiscount.Text, out disc))
{
if (disc > 0)
{
worksheet.Cells[26, 4] = disc / 100;
}
}
excel.Visible = true;
//var xlApp = new Excel.Application();
//var xlWorkBook = xlApp.Workbooks.Add();
//var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Microsoft.Office.Interop.Excel.ExcelTitleRow(list[0], 1, xlWorkSheet);
// int row = 2;
// foreach (var item in list)
// {
// ExcelFillRow(item, row++, xlWorkSheet);
// }
// for (int i = 1; i < list[0].MaxLevel - 1; i++)
// {
// ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Columns[i]).ColumnWidth = 2;
// }
//((Excel.Range)xlWorkSheet.Columns[list[0].MaxLevel - 1]).ColumnWidth = 30;
// ((Excel.Range)xlWorkSheet.Rows[1]).WrapText = true;
// ((Excel.Range)xlWorkSheet.Rows[1]).HorizontalAlignment = HorizontalAlignment.Center;
// ((Excel.Range)xlWorkSheet.Cells[1, 1]).WrapText = false;
// workbook.SaveAs(fileName);
//workbook.Close();
//excel.Quit();
}
catch (AccessViolationException)
{
MessageBox.Show(
"Have encountered access violation. This could be issue with Excel 2000 if that is only version installed on computer",
"Access Violation");
}
catch (Exception)
{
MessageBox.Show("Unknown error",
"Unknown error");
}
}
private void button1_Click(object sender, EventArgs e)
{
//Вводимые переменные
//string ind;
string dat = Convert.ToString(dateTimePicker1);
string zad = textBox1.Text;
string nrvs = textBox2.Text;
string nkr = textBox3.Text;
string nig = textBox4.Text;
string hodig = textBox5.Text;
string hodigv = textBox6.Text;
string hodign = textBox7.Text;
string vhvost = textBox10.Text;
string vhvostv = textBox9.Text;
string vhvostn = textBox8.Text;
string prnahod = textBox13.Text;
string prnahodv = textBox12.Text;
string prnahodn = textBox11.Text;
string gidropl = textBox14.Text;
string mfv = textBox15.Text;
string mfn = textBox16.Text;
string zag = textBox17.Text;
string procpolir = textBox18.Text;
string procdrosel = textBox19.Text;
string pmarkbosch = textBox26.Text;
string kmarkbosch = textBox25.Text;
string pmarkazpi = textBox24.Text;
string kmarkazpi = textBox23.Text;
string uvp1 = maskedTextBox1.Text;
string uvp2 = maskedTextBox2.Text;
string uvp3 = maskedTextBox3.Text;
string uvp4 = maskedTextBox4.Text;
string uvp5 = maskedTextBox5.Text;
string uvp6 = maskedTextBox6.Text;
string uvp7 = maskedTextBox7.Text;
string uvp8 = maskedTextBox8.Text;
string uvp9 = maskedTextBox9.Text;
string uvp10 = maskedTextBox10.Text;
string uvsh1 = maskedTextBox11.Text;
string uvsh2 = maskedTextBox12.Text;
string uvsh3 = maskedTextBox13.Text;
string uvsh4 = maskedTextBox14.Text;
string uvsh5 = maskedTextBox15.Text;
string uvsh6 = maskedTextBox16.Text;
string uvsh7 = maskedTextBox17.Text;
string uvsh8 = maskedTextBox18.Text;
string uvsh9 = maskedTextBox19.Text;
string uvsh10 = maskedTextBox20.Text;
double ki1 = 0;
double ki2 = 0;
double ki3 = 0;
double ki4 = 0;
double ki5 = 0;
double ki6 = 0;
double ki7 = 0;
double ki8 = 0;
double ki9 = 0;
double ki10 = 0;
string diamotv = textBox54.Text;
if (textBox15.TextLength == 0 || textBox16.TextLength == 0)
{
MessageBox.Show("Введите mf");
return;
}
//Вычисляемые переменные
double pmfrvs1 = Convert.ToDouble(mfn);
double pmfrvs2 = Convert.ToDouble(mfv);
double ptrvs1 = 0.202 / pmfrvs1;
double ptrvs2 = 0.202 / pmfrvs2;
double pqrvs1 = Convert.ToDouble(xurma(pmfrvs1));
double pqrvs2 = Convert.ToDouble(xurma(pmfrvs2));
double pmfpol1 = pmfrvs1 * (Convert.ToDouble(procdrosel) / 100) + pmfrvs1;
double pmfpol2 = pmfrvs2 * (Convert.ToDouble(procdrosel) / 100) + pmfrvs2;
double ptpol1 = 0.202 / pmfpol1;
double ptpol2 = 0.202 / pmfpol2;
double pqpol1 = Convert.ToDouble(xurma(pmfpol1));
double pqpol2 = Convert.ToDouble(xurma(pmfpol2));
double pmfpr1 = pmfpol1 - (pmfpol1 * (Convert.ToDouble(procpolir) / 100));
double pmfpr2 = pmfpol2 - (pmfpol2 * (Convert.ToDouble(procpolir) / 100));
double ptpr1 = 0.202 / pmfpr1;
double ptpr2 = 0.202 / pmfpr2;
double pqpr1 = Convert.ToDouble(xurma(pmfpr1));
double pqpr2 = Convert.ToDouble(xurma(pmfpr2));
double raz = 0.176;
double raz2 = 0.395;
if (radioButton5.Checked)
{
if (textBox20.Text != "")
{
ki1 = Convert.ToDouble(textBox20.Text) - raz2;
}
if (textBox21.Text != "")
{
ki2 = Convert.ToDouble(textBox21.Text) - raz2;
}
if (textBox22.Text != "")
{
ki3 = Convert.ToDouble(textBox22.Text) - raz2;
}
if (textBox27.Text != "")
{
ki4 = Convert.ToDouble(textBox27.Text) - raz2;
}
if (textBox28.Text != "")
{
ki5 = Convert.ToDouble(textBox28.Text) - raz2;
}
if (textBox29.Text != "")
{
ki6 = Convert.ToDouble(textBox29.Text) - raz2;
}
if (textBox30.Text != "")
{
ki7 = Convert.ToDouble(textBox30.Text) - raz2;
}
if (textBox31.Text != "")
{
ki8 = Convert.ToDouble(textBox31.Text) - raz2;
}
if (textBox32.Text != "")
{
ki9 = Convert.ToDouble(textBox32.Text) - raz2;
}
if (textBox33.Text != "")
{
ki10 = Convert.ToDouble(textBox33.Text) - raz2;
}
}
if (radioButton1.Checked)
{
if (textBox20.Text != "")
{
ki1 = raz + Convert.ToDouble(textBox20.Text);
}
if (textBox21.Text != "")
{
ki2 = raz + Convert.ToDouble(textBox21.Text);
}
if (textBox22.Text != "")
{
ki3 = raz + Convert.ToDouble(textBox22.Text);
}
if (textBox27.Text != "")
{
ki4 = raz + Convert.ToDouble(textBox27.Text);
}
if (textBox28.Text != "")
{
ki5 = raz + Convert.ToDouble(textBox28.Text);
}
if (textBox29.Text != "")
{
ki6 = raz + Convert.ToDouble(textBox29.Text);
}
if (textBox30.Text != "")
{
ki7 = raz + Convert.ToDouble(textBox30.Text);
}
if (textBox31.Text != "")
{
ki8 = raz + Convert.ToDouble(textBox31.Text);
}
if (textBox32.Text != "")
{
ki9 = raz + Convert.ToDouble(textBox32.Text);
}
if (textBox33.Text != "")
{
ki10 = raz + Convert.ToDouble(textBox33.Text);
}
}
if (radioButton2.Checked)
{
if (textBox20.Text != "")
{
ki1 = Convert.ToDouble(textBox20.Text);
}
if (textBox21.Text != "")
{
ki2 = Convert.ToDouble(textBox21.Text);
}
if (textBox22.Text != "")
{
ki3 = Convert.ToDouble(textBox22.Text);
}
if (textBox27.Text != "")
{
ki4 = Convert.ToDouble(textBox27.Text);
}
if (textBox28.Text != "")
{
ki5 = Convert.ToDouble(textBox28.Text);
}
if (textBox29.Text != "")
{
ki6 = Convert.ToDouble(textBox29.Text);
}
if (textBox30.Text != "")
{
ki7 = Convert.ToDouble(textBox30.Text);
}
if (textBox31.Text != "")
{
ki8 = Convert.ToDouble(textBox31.Text);
}
if (textBox32.Text != "")
{
ki9 = Convert.ToDouble(textBox32.Text);
}
if (textBox33.Text != "")
{
ki10 = Convert.ToDouble(textBox33.Text);
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open((Path.Combine(mypath, "appur.xlsm")), ReadOnly: false, Password: "******");
Microsoft.Office.Interop.Excel.Worksheet x = excel.Sheets["variable"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange = x.UsedRange;
//x.Cells[2, 2] = dat;
x.Cells[2, 3] = zad;
x.Cells[2, 4] = nrvs;
x.Cells[2, 5] = nkr;
x.Cells[2, 6] = nig;
x.Cells[2, 7] = hodig;
x.Cells[2, 8] = hodigv;
x.Cells[2, 9] = hodign;
x.Cells[2, 10] = vhvost;
x.Cells[2, 11] = vhvostv;
x.Cells[2, 12] = vhvostn;
x.Cells[2, 13] = prnahod;
x.Cells[2, 14] = prnahodv;
x.Cells[2, 15] = prnahodn;
x.Cells[2, 16] = gidropl;
x.Cells[2, 17] = mfn;
x.Cells[2, 18] = mfv;
x.Cells[2, 19] = zag;
x.Cells[2, 20] = procpolir;
x.Cells[2, 21] = procdrosel;
x.Cells[2, 22] = pmarkbosch;
x.Cells[2, 23] = kmarkbosch;
x.Cells[2, 24] = pmarkazpi;
x.Cells[2, 25] = kmarkazpi;
x.Cells[2, 26] = uvp1;
x.Cells[2, 27] = uvp2;
x.Cells[2, 28] = uvp3;
x.Cells[2, 29] = uvp4;
x.Cells[2, 30] = uvp5;
x.Cells[2, 31] = uvp6;
x.Cells[2, 32] = uvp7;
x.Cells[2, 33] = uvp8;
x.Cells[2, 34] = uvp9;
x.Cells[2, 35] = uvp10;
x.Cells[2, 36] = uvsh1;
x.Cells[2, 37] = uvsh2;
x.Cells[2, 38] = uvsh3;
x.Cells[2, 39] = uvsh4;
x.Cells[2, 40] = uvsh5;
x.Cells[2, 41] = uvsh6;
x.Cells[2, 42] = uvsh7;
x.Cells[2, 43] = uvsh8;
x.Cells[2, 44] = uvsh9;
x.Cells[2, 45] = uvsh10;
x.Cells[2, 46] = ki1;
x.Cells[2, 47] = ki2;
x.Cells[2, 48] = ki3;
x.Cells[2, 49] = ki4;
x.Cells[2, 50] = ki5;
x.Cells[2, 51] = ki6;
x.Cells[2, 52] = ki7;
x.Cells[2, 53] = ki8;
x.Cells[2, 54] = ki9;
x.Cells[2, 55] = ki10;
x.Cells[2, 56] = diamotv;
x.Cells[2, 57] = pmfpr1;
x.Cells[2, 58] = pmfpr2;
x.Cells[2, 59] = ptpr1;
x.Cells[2, 60] = ptpr2;
x.Cells[2, 61] = pqpr1;
x.Cells[2, 62] = pqpr2;
x.Cells[2, 63] = pmfpol1;
x.Cells[2, 64] = pmfpol2;
x.Cells[2, 65] = ptpol1;
x.Cells[2, 66] = ptpol2;
x.Cells[2, 67] = pqpol1;
x.Cells[2, 68] = pqpol2;
x.Cells[2, 69] = pmfrvs1;
x.Cells[2, 70] = pmfrvs2;
x.Cells[2, 71] = ptrvs1;
x.Cells[2, 72] = ptrvs2;
x.Cells[2, 73] = pqrvs1;
x.Cells[2, 74] = pqrvs2;
Microsoft.Office.Interop.Excel.Worksheet x2 = excel.Sheets["pu"] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange2 = x2.UsedRange;
x2.Cells[56, 11] = textBox34.Text;
//Печать
if (checkBox1.Checked)
{
excel.ActiveWorkbook.PrintOutEx(17, 17);
}
if (checkBox2.Checked)
{
excel.ActiveWorkbook.PrintOutEx(12, 12);
}
if (checkBox3.Checked)
{
excel.ActiveWorkbook.PrintOutEx(13, 13);
}
if (checkBox4.Checked)
{
excel.ActiveWorkbook.PrintOutEx(10, 10);
}
if (checkBox8.Checked)
{
excel.ActiveWorkbook.PrintOutEx(11, 11);
}
if (checkBox7.Checked)
{
excel.ActiveWorkbook.PrintOutEx(14, 14);
}
if (checkBox6.Checked)
{
excel.ActiveWorkbook.PrintOutEx(18, 18);
}
//excel.ActiveWorkbook.PrintOutEx(4, 4);
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
Microsoft.Office.Interop.Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
sheet.CleanPassword();
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="file_name"></param>
/// <param name="sheet_name"></param>
public static void DataTableToExcel(System.Data.DataTable dt, string file_name, string sheet_name)
{
Microsoft.Office.Interop.Excel.Application Myxls = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook Mywkb = Myxls.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet MySht = (Worksheet)Mywkb.ActiveSheet;
MySht.Name = sheet_name;
Myxls.Visible = false;
Myxls.DisplayAlerts = false;
try
{
//写入表头
object[] arrHeader = new object[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
arrHeader[i] = dt.Columns[i].ColumnName;
}
MySht.Range[MySht.Cells[1, 1], MySht.Cells[1, dt.Columns.Count]].Value2 = arrHeader;
//写入表体数据
object[,] arrBody = new object[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
arrBody[i, j] = dt.Rows[i][j].ToString();
}
}
MySht.Range[MySht.Cells[2, 1], MySht.Cells[dt.Rows.Count + 1, dt.Columns.Count]].Value2 = arrBody;
if (Mywkb != null)
{
Mywkb.SaveAs(file_name);
Mywkb.Close(Type.Missing, Type.Missing, Type.Missing);
Mywkb = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统提示");
}
finally
{
//彻底关闭Excel进程
if (Myxls != null)
{
Myxls.Quit();
try
{
if (Myxls != null)
{
int pid;
GetWindowThreadProcessId(new IntPtr(Myxls.Hwnd), out pid);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(pid);
p.Kill();
}
}
catch (Exception ex)
{
MessageBox.Show("结束当前EXCEL进程失败：" + ex.Message);
}
Myxls = null;
}
GC.Collect();
}
}
static void Main(string[] args)
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\db\Database11.accdb; Persist Security Info=False;";
string sql = @"select * from Users where SocialNumber = '12345543211234'";
object id = null;
object name = null;
object birthDate = null;
object PhoneNumber = null;
object Address = null;
object SocialNumber = null;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
// выводим названия столбцов
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}", reader.GetName(0), reader.GetName(1), reader.GetName(2), reader.GetName(3), reader.GetName(4), reader.GetName(5));
while (reader.Read()) // построчно считываем данные
{
id = reader.GetValue(0);
name = reader.GetValue(1);
birthDate = reader.GetValue(2);
PhoneNumber = reader.GetValue(3);
Address = reader.GetValue(4);
SocialNumber = reader.GetValue(5);
Console.WriteLine("{0} \t{1} \t{2} \t{3} \t{4} \t{5}", id, name, birthDate, PhoneNumber, Address, SocialNumber);
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(@"C:\Users\User\Documents\Visual Studio 2010\Projects\ConsoleApplication1\Test\Template\example.xlsx");
Microsoft.Office.Interop.Excel.Worksheet x = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
x.Cells[3, 2] = id;
x.Cells[4, 2] = name;
x.Cells[5, 2] = birthDate;
x.Cells[6, 2] = PhoneNumber;
x.Cells[7, 2] = Address;
x.Cells[8, 2] = SocialNumber;
x.Cells[4, 4] = id;
x.Cells[4, 5] = name;
x.Cells[4, 6] = birthDate;
x.Cells[4, 7] = PhoneNumber;
x.Cells[4, 8] = Address;
x.Cells[4, 9] = SocialNumber;
sheet.SaveAs(@"C:\Users\User\Documents\Visual Studio 2010\Projects\Test\ConsoleApplication1\Result\Example.xlsx");
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
Console.Read();
}
}
private void button1_Click(object sender, EventArgs e)
{
var application = new Microsoft.Office.Interop.Excel.Application();
application.Visible = checkBox1.Checked ? true : false; //показувати ексель;
application.DisplayAlerts = checkBox2.Checked ? true : false;; ////Отключить отображение окон с сообщениями true- show
try // check if file existing
{
application.Workbooks.Open(DEMOFILE);
var RangWorkbook = application.Workbooks.Item[1].Title;
}
catch
{
Microsoft.Office.Interop.Excel.Workbook workbook = application.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets[1];
// Determine whether the directory exists.
if (!Directory.Exists(path))// Try to create the directory.
{
DirectoryInfo di = Directory.CreateDirectory(path);
}
workbook.SaveAs(@"C:\Merz daily activities\Merz daily activities.xlsx");
}
int Active_sheet = 1;
int cnt = application.Sheets.Count;
string[] name = new string[cnt];
for (int i = 1; i <= cnt; i++)
{
name[i - 1] = application.Sheets[i].Name;
}
string date = DateTime.UtcNow.ToString("d").Replace("/", ".");
int count = name.Length;
for (int i = 0; i < name.Length; i++)
{
if (name[i] != date)
{
}
else
{
count--;
Active_sheet++;
}
}
if (count == name.Length)
{
Worksheet addSheet = application.Worksheets.Add();
addSheet.Name = date;
}
if (name.Length == 1)//Якщо у документі присутня тільки одна сторінка то для Active_sheet встановлюємо її значення
{
Active_sheet = 1;
}
var sheet = application.Worksheets.get_Item(Active_sheet);// Вибераєм лист для запису даних
Excel.Range range;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
range = sheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
int prom = rw;
for (rCnt = 1; rCnt <= rw; rCnt++)
{
if (prom - 1 >= 1 || cl > 1)
{
rCnt = rCnt + rw;
}
for (cCnt = 1; cCnt <= cl; cCnt++)
{
sheet.Cells[rCnt, cCnt] = textBox1.Text;
cCnt++;
sheet.Cells[rCnt, cCnt] = comboBox1.Text;
cCnt++;
sheet.Cells[rCnt, cCnt] = textBox2.Text;
}
for (int i = 1; i <= 3; i++)
{
application.Columns[i].AutoFit();
}
}
///
/// ///
///// Видалення пустих сторінок
///
int O = cnt; //перепресвоєння значення для cnt = application.Sheets.Count; після добавлення сторінки
cnt = application.Sheets.Count;
int[] delID = new int[cnt];// кількість сторінок в книзі
name = new string[cnt];
for (int i = 1; i <= cnt; i++)
{
name[i - 1] = application.Sheets[i].Name;
}
string[] delsheets = name; //назви сторінок в книзі
bool sveech = false; // переключатель, якщо знайдена пуста сторінка, то змінюється його стан
for (int i = 0; i < cnt; i++)
{
int x1 = delsheets[i].Length - 1;
delsheets[i] = delsheets[i].Remove(x1); // відкидуєм останю цифру в назві листка, перезаписуєм масив новими значенями
if (delsheets[i] == "Аркуш" || delsheets[i] == "Sheet" || delsheets[i] == "Лист")
{
delID[i] = i + 1;
sveech = true;
}
}
if (sveech == true)
{
for (int i = cnt - 1; i >= 0; i--) //for (int i = CDS - 1; i >= 0; i--)
{
if (delID[i] != 0)
{
application.Worksheets[i + 1].Delete();
}
}
}
///
///
///
MessageBox.Show("File was updated");
CLS();
Workbook book = application.ActiveWorkbook;
// book.SaveAs(@"C:\Merz daily activities\Merz daily activities.xlsx");
book.Save();
book.Close(true);
application.Quit();
Thread.Sleep(5000);
application.Quit();// Close Excel
application = null;
CloseProcess();
}
public void Print(object sender, EventArgs e)
{
if (((FrmMAIN)this.MdiParent).ActiveMdiChild == this)
{
if (dgvCost.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
try
{
int i, j;
saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls";
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "SaveSalesCost";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add();
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int k = 1; k < dgvCost.ColumnCount; k++)
{
xlWorkSheet.Cells[1, k] = dgvCost.Columns[k].HeaderText.ToString();
}
for (i = 0; i < dgvCost.RowCount; i++)
{
for (j = 0; j < dgvCost.ColumnCount - 1; j++)
{
//if (j == 3)
// continue;
if (dgvCost[j, i].Value != null)
{
xlWorkSheet.Cells[i + 2, j + 1] = dgvCost[j, i].Value.ToString();
}
}
}
xlWorkBook.SaveAs(saveFileDialog1.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
xlWorkBook.Close(true);
xlApp.Quit();
MessageBox.Show("출력되었습니다.", "출력 완료", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception err)
{
MessageBox.Show("출력에 실패하였습니다.", "출력 실패", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (xlApp != null)
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
}
}
}
public static void AddDataToExcel(string SampleID, DateTime SampleDate, string PatientID, double Age, double[] BioData, int rowCount, string FilePath)
{
string path = FilePath;
//xlApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook excelWorkbook = xlApp.Workbooks.Open(path);
//Microsoft.Office.Interop.Excel._Worksheet sheet = excelWorkbook.Sheets[1];
//TrimRows(sheet);
//var LastRow = sheet.UsedRange.Rows.Count;
//LastRow = LastRow + sheet.UsedRange.Row - 1;
//for (int i = 1; i <= LastRow; i++)
//{
// //if (application.WorksheetFunction.CountA(sheet.Rows[i]) == 0)
// // (sheet.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete();
//}
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("biomarkers + added parameters");
//TrimRows(mWSheet1);
//Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
//int colCount = range.Columns.Count;
//int rowCount = range.Rows.Count;
rowCount = rowCount + 1;
mWSheet1.Cells[rowCount + 1, 1] = rowCount; //DigiWest ID
mWSheet1.Cells[rowCount + 1, 2] = SampleID; //SampleID
mWSheet1.Cells[rowCount + 1, 3] = ""; //Patient ID IOTA
mWSheet1.Cells[rowCount + 1, 4] = ""; //IOTA Score
mWSheet1.Cells[rowCount + 1, 5] = ""; //Certain or Uncertain
mWSheet1.Cells[rowCount + 1, 6] = SampleDate; //SampleDate
mWSheet1.Cells[rowCount + 1, 7] = ""; //DateOfBirth
mWSheet1.Cells[rowCount + 1, 8] = Age; //Age
mWSheet1.Cells[rowCount + 1, 9] = ""; //CA-125
mWSheet1.Cells[rowCount + 1, 10] = ""; //NeoOva Score
mWSheet1.Cells[rowCount + 1, 11] = BioData[0]; //Neopro1
mWSheet1.Cells[rowCount + 1, 12] = BioData[1]; //Neopro2
mWSheet1.Cells[rowCount + 1, 13] = BioData[2]; //Neopro3
mWSheet1.Cells[rowCount + 1, 14] = BioData[3]; //Neopro4
mWSheet1.Cells[rowCount + 1, 15] = BioData[4]; //Neopro5
mWSheet1.Cells[rowCount + 1, 16] = BioData[5]; //Neopro6
mWSheet1.Cells[rowCount + 1, 17] = PatientID; //PatientID
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void picLoadOut_Click(object sender, EventArgs e)
{
#region 导出信息保存为Excel表
DialogResult ret = MessageBox.Show("导出信息为敏感操作，确定要继续导出吗？(此步操作将写入操作日志)", "信息提醒", MessageBoxButtons.YesNo);
if (ret == DialogResult.Yes)
{
//Response.ContentEncoding = System.Text.Encoding.UTF8;
string fileName = "";
string saveFileName = "";
//fileName.Charset = "GB2312";
SaveFileDialog saveDialog = new SaveFileDialog();
//saveDialog.DefaultExt = "xls";
saveDialog.FileName = fileName;
saveDialog.Filter = "2003~2007工作表*.xls|*.xls|2010及以上版本工作表*.xlsx|*.xlsx";
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！", "来自T仔的提醒");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < this.dgvCustomerList.Columns.Count; i++)
{
xlApp.Cells[1, i + 1] = dgvCustomerList.Columns[i].HeaderText;
}
for (int i = 0; i < dgvCustomerList.Rows.Count; i++)//添加每一项
{
for (int j = 0; j < dgvCustomerList.Columns.Count; j++)
{
xlApp.Cells[i + 2, j + 1] = dgvCustomerList.Rows[i].Cells[j].Value.ToString();
}
}
System.Windows.Forms.Application.DoEvents();
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "信息导出成功", "来自T仔提示", MessageBoxButtons.OK);
#region 获取添加操作日志所需的信息
Operation o = new Operation();
o.OperationTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd,HH:mm:ss"));
o.Operationlog = LoginInfo.WorkerClub + LoginInfo.WorkerName + LoginInfo.WorkerPosition + LoginInfo.WorkerName + "于" + DateTime.Now + "导出了" + "用户信息!";
o.OperationAccount = LoginInfo.WorkerClub + LoginInfo.WorkerName + LoginInfo.WorkerPosition;
#endregion
OperationManager.InsertOperationLog(o);
System.Diagnostics.Process.Start("Explorer.exe", saveFileName);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();
#endregion
}
}
protected int ReadEXCELFile()
{
//创建Application对象
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
xApp.Visible = false; //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
xApp.UserControl = true;
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(ViewState["ServerFileFullName"].ToString(),
oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = xBook.Sheets[1];
int index = 1;
ArrayList ResultTemp = new ArrayList();
ArrayList mymatchTemp = new ArrayList();
bool headsign;
int rowindex, colindex, temprowindex;
string text; string[] textresult;
try
{
switch (tableStyle.Items[tableStyle.SelectedIndex].Text)
{
case "段落方式":
myinfo.InnerText = "Excel文件请选择表格方式";
myinfo.Visible = true;
xBook.Close();
xApp.Quit();
return(2);
break;
case "表格方式：第一行是标签":
headsign = true;
for (rowindex = 1; rowindex <= worksheet.UsedRange.Rows.Count; rowindex++)
{
if (worksheet.UsedRange.Rows[rowindex].Cells.Count < 2)
{
continue;
}
if (headsign)
{
temprowindex = 2;
while (temprowindex <= worksheet.UsedRange.Rows[rowindex].Cells.Count)
{
if (worksheet.Cells[rowindex, temprowindex].Value != null)
{
break;
}
temprowindex++;
}
if (temprowindex > worksheet.UsedRange.Rows[rowindex].Cells.Count)
{
continue;
}
headsign = false;
for (colindex = 1; colindex <= worksheet.UsedRange.Rows[rowindex].Cells.Count; colindex++)
{
if (worksheet.Cells[rowindex, colindex].Value != null)
{
mymatchTemp.Add(worksheet.Cells[rowindex, colindex].Value);
}
}
}
else
{
for (temprowindex = 1; temprowindex <= mymatchTemp.Count; temprowindex++)
{
if (worksheet.Cells[rowindex, temprowindex].Value != null)
{
break;
}
}
if (temprowindex > mymatchTemp.Count)
{
continue;
}
for (colindex = 1; colindex <= mymatchTemp.Count; colindex++)
{
ResultTemp.Add(mymatchTemp[colindex - 1]);
temprowindex = rowindex;
//while (worksheet.Cells[temprowindex, colindex].Value == null) temprowindex--;
if (worksheet.Cells[temprowindex, colindex].Value == null)
{
ResultTemp.Add(" ");
}
else
{
ResultTemp.Add(worksheet.Cells[temprowindex, colindex].Value);
}
}
}
}
break;
case "表格方式：第一列是标签":
for (rowindex = 1; rowindex <= worksheet.UsedRange.Rows.Count; rowindex++)
{
if (worksheet.UsedRange.Rows[rowindex].Cells.Count == 1)
{
text = worksheet.UsedRange.Rows[rowindex].Cells[1].Range.Text;
textresult = Regex.Split(text, @"[\r\t^]+([\S ]*)[：:\r]+");
if (textresult.Length >= 2)
{
for (colindex = 0; colindex < textresult.Length; colindex++)
{
ResultTemp.Add(textresult[colindex]);
if (Regex.IsMatch(textresult[colindex], @"[企业主要产品|企业科技需求|企业技术难题]+"))
{
mymatchTemp.Add(textresult[colindex]);
}
}
}
}
else
{
for (colindex = 1; colindex <= worksheet.UsedRange.Rows[rowindex].Cells.Count; colindex++)
{
if (colindex % 2 != 0)
{
mymatchTemp.Add(worksheet.UsedRange.Rows[rowindex].Cells[colindex].Range.Text);
}
ResultTemp.Add(worksheet.UsedRange.Rows[rowindex].Cells[colindex].Range.Text);
}
}
}
break;
}
result = new string[ResultTemp.Count];
mymatch = new string[mymatchTemp.Count];
for (index = 0; index < ResultTemp.Count; index++)
{
result[index] = ResultTemp[index].ToString();
}
for (index = 0; index < mymatchTemp.Count; index++)
{
mymatch[index] = mymatchTemp[index].ToString();
}
xBook.Close();
xApp.Quit();
return(1);
}
catch (Exception ex)
{
myinfo.InnerText = "出错了：请检查:1,多个数据之间格式是否相同；2,是否选择了正确的表格识别插入方式；3，表格是否规范（是否有未使用的单元格）";
myinfo.Visible = true;
xBook.Close();
xApp.Quit();
return(2);
}
}
public void generarReporte(DataGridView tabla, string nombreHojaReporte, string tituloReporte, string celdaInicioTitulo, string celdaFinTitulo, int indexInicioTitulo, int indexFinTitulo)
{
////Para futura referencia, esta es una forma probable de obtener un rango de celdas basado en indices
////Excel.Range range = hoja.Ranges(hoja.Cells[1, 1], hoja.Cells[1, 2]);
string columnaOrdenamiento = "Filtro";
try
{
Cursor.Current = Cursors.WaitCursor;
exportar.Enabled = false;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet hojaDatos = wb.ActiveSheet;
int IndiceColumna = 0;
foreach (DataGridViewColumn col in tabla.Columns) // Columnas
{
IndiceColumna++;
hojaDatos.Cells[1, IndiceColumna] = col.Name;
}
//agregar campo de ordenamiento
hojaDatos.Cells[1, IndiceColumna + 1] = columnaOrdenamiento;
int IndiceFila = 0;
foreach (DataGridViewRow row in tabla.Rows) // Filas
{
IndiceFila++;
IndiceColumna = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
IndiceColumna++;
hojaDatos.Cells[IndiceFila + 1, IndiceColumna] = "'" + row.Cells[col.Name].Value;
}
hojaDatos.Cells[IndiceFila + 1, IndiceColumna + 1] = columnaOrdenamiento;
}
Excel.Worksheet hojaReporte = excel.Sheets.Add();
hojaReporte.Name = nombreHojaReporte;
hojaReporte.Activate();
Excel.Range oRange = hojaDatos.UsedRange;
Excel.PivotCache oPivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, oRange, Type.Missing);
Excel.Range oRange2 = hojaReporte.Cells[5, 2];
Excel.PivotCaches pch = wb.PivotCaches();
pch.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(oRange2, "reportePersonas", Type.Missing, Type.Missing);
Excel.PivotTable pvt = hojaReporte.PivotTables("reportePersonas") as Excel.PivotTable;
//configuracion de la tabla dinamica
pvt.RowGrand = false; //Ocultar los totales y subtotales de la tabla dinamica
pvt.ColumnGrand = false; //Ocultar los totales y subtotales de la tabla dinamica
pvt.EnableFieldList = false; //desactivar la opcion para apagar o encender campos en la tabla dinamica
pvt.ShowDrillIndicators = false; //quitar los simbolos de + en cada celda
pvt.EnableDrilldown = false; //no permitir minimizar las filas
pvt.InGridDropZones = false; //no permitir drag&drop de las columnas
pvt.ShowTableStyleRowHeaders = false; //no mostrar columna de por medio en negrita/otro color, segun el estilo de tabla
pvt.TableStyle2 = "PivotStyleMedium9"; //settear estilo de tabla
foreach (DataGridViewColumn col in tabla.Columns) // Columnas
{
Excel.PivotField field = (Excel.PivotField)pvt.PivotFields(col.Name);
field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
field.Subtotals[1] = false;
}
//agregar el PivotField para el campo de ordenamiento
Excel.PivotField f = (Excel.PivotField)pvt.PivotFields(columnaOrdenamiento);
f.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
f.Name = "No remover, ocultar solamente";
//hacer que las columnas tengan el tamaño adecuado
hojaReporte.UsedRange.Columns.AutoFit();
//int startIndex = indexColumnaOrdenamiento.IndexOfAny("0123456789".ToCharArray());
//string indicatedColumnLetter = indexColumnaOrdenamiento.Substring(0, startIndex);
string column = obtenerNombreColExcel(tabla.Columns.Count + 2); // se agregan mas dos por la posicion inicial de la tabla y la columna de ordenamiento extra
hojaReporte.Range[column + "1"].EntireColumn.Hidden = true; //ocultando la columna de sort
//agregar el dato de encabezado
hojaReporte.Cells[2, 3] = tituloReporte;
Excel.Range titulo = hojaReporte.Range[celdaInicioTitulo, celdaFinTitulo];
titulo.Merge();
titulo.Font.Bold = true;
titulo.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
titulo.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black;
hojaReporte.Cells[3, indexInicioTitulo] = "Fecha:";
hojaReporte.Cells[3, indexInicioTitulo + 1] = DateTime.Today;
hojaReporte.Cells[3, indexFinTitulo - 1] = "Hora:";
hojaReporte.Cells[3, indexFinTitulo] = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString();
//eliminar la hoja de datos
excel.DisplayAlerts = false; //bypass del bug que evita que se elimine la hoja
hojaDatos.Activate();
hojaDatos.Delete();
hojaReporte.Activate();
excel.DisplayAlerts = true; //retornar la propiedad al valor original
MessageBox.Show("Infome generado exitosamente.", "Operación completa", MessageBoxButtons.OK, MessageBoxIcon.Information);
exportar.Enabled = true;
excel.Visible = true;
Cursor.Current = Cursors.Default;
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
Cursor.Current = Cursors.Default;
exportar.Enabled = true;
MessageBox.Show("Ha ocurrido un error en la creación del documento, póngase en contacto con los desarrolladores del sistema.", "Error - AlbergueHN", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void GEN_Poll_Chart(PowerPoint.Slide Sld)
{
//Select the slide and set its layout
Sld.Select();
Sld.Layout = Microsoft.Office.Interop.PowerPoint.PpSlideLayout.ppLayoutBlank;
//Add a default chart in slide
Sld.Shapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, 200F, 200F, 400F, 300F);
//Add a heading
PowerPoint.Shape textBox = Sld.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 0, 20, 1000, 70);
textBox.TextFrame.TextRange.InsertAfter("Daily Class Poll");
textBox.TextFrame.TextRange.Font.Size = 30;
textBox.TextFrame.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
textBox.TextFrame.HorizontalAnchor = Microsoft.Office.Core.MsoHorizontalAnchor.msoAnchorCenter;
//Access the added chart
Microsoft.Office.Interop.PowerPoint.Chart ppChart = Sld.Shapes[1].Chart;
//Access the chart data
Microsoft.Office.Interop.PowerPoint.ChartData chartData = ppChart.ChartData;
//Create instance to Excel workbook to work with chart data
Microsoft.Office.Interop.Excel.Workbook dataWorkbook = (Microsoft.Office.Interop.Excel.Workbook)chartData.Workbook;
//Accessing the data worksheet for chart
Microsoft.Office.Interop.Excel.Worksheet dataSheet = dataWorkbook.Worksheets[1];
//Setting the range of chart
Microsoft.Office.Interop.Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");
//Create a Table and applying the set range on chart data table
Microsoft.Office.Interop.Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
//Thread.Sleep(3000);
Debug.WriteLine("over 1");
//Setting values for categories and respective series data
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A2"))).FormulaR1C1 = "A";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A3"))).FormulaR1C1 = "B";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A4"))).FormulaR1C1 = "C";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A5"))).FormulaR1C1 = "D";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B5"))).FormulaR1C1 = 0;
//Delete the legend for neatness
Microsoft.Office.Interop.PowerPoint.Legend legend = null;
legend = ppChart.Legend;
legend.Delete();
//Setting chart title
ppChart.ChartTitle.Font.Italic = false;
ppChart.ChartTitle.Text = "Poll Results";
ppChart.ChartTitle.Font.Size = 18;
ppChart.ChartTitle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ppChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
ppChart.ChartTitle.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
//setting all to red bars
var series = ppChart.SeriesCollection(1) as PowerPoint.Series;
var point = series.Points(1) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
point = series.Points(2) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
point = series.Points(3) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
point = series.Points(4) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//Accessing Chart value axis
Microsoft.Office.Interop.PowerPoint.Axis valaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlValue, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
//Setting values axis units
valaxis.MajorUnit = 2000.0F;
valaxis.MinorUnit = 1000.0F;
valaxis.MinimumScale = 0.0F;
valaxis.MaximumScale = 30.0F;
//Accessing Chart Depth axis
Microsoft.Office.Interop.PowerPoint.Axis Depthaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlSeriesAxis, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
Depthaxis.Delete();
//Setting chart rotation
ppChart.Rotation = 20; //Y-Value
ppChart.Elevation = 15; //X-Value
ppChart.RightAngleAxes = false;
//Thread.Sleep(5000);
Debug.WriteLine("over 2");
var ws = new WebSocket("ws://localhost:1234");
var the_message = "";
ws.OnMessage += (sender12, sent) =>
{
try
{
the_message = sent.Data;
var the_message2 = JsonConvert.DeserializeObject <List <PollNode> >(the_message);
var ques = "";
var ans_a = "";
var ans_b = "";
var ans_c = "";
var ans_d = "";
var num_a = "";
var num_b = "";
var num_c = "";
var num_d = "";
var correct_ans = "";
foreach (var m in the_message2)
{
ques = m.Question;
ans_a = m.A;
ans_b = m.B;
ans_c = m.C;
ans_d = m.D;
num_a = m.Freq_A;
num_b = m.Freq_B;
num_c = m.Freq_C;
num_d = m.Freq_D;
correct_ans = m.Correct;
}
Debug.WriteLine("The message recieved from Node.js:");
Debug.WriteLine(the_message);
textBox = Sld.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 50, 90, 500, 70);
textBox.TextFrame.TextRange.InsertAfter(ques + "\n A: " + ans_a + "\n B: " + ans_b + "\n C: " + ans_c + "\n D: " + ans_d);
textBox.TextFrame.TextRange.Font.Size = 20;
if (correct_ans == "A")
{
point = series.Points(1) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if (correct_ans == "B")
{
point = series.Points(1) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if (correct_ans == "C")
{
point = series.Points(3) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if (correct_ans == "D")
{
point = series.Points(4) as PowerPoint.Point;
point.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = Convert.ToInt32(num_a);
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = Convert.ToInt32(num_b);
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = Convert.ToInt32(num_c);
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B5"))).FormulaR1C1 = Convert.ToInt32(num_d);
}
catch (InvalidCastException e)
{
Debug.WriteLine("Source: ", e.Source);
}
};
ws.Connect();
ws.OnError += (sender12, sent) =>
{
Debug.WriteLine("error " + sent.Message);
};
ws.OnClose += (sender12, sent) =>
{
Debug.WriteLine("disconnect with host");
};
}
private object[,] readData(Microsoft.Office.Interop.Excel.Worksheet ws, AppIntegratedInfo appInfo, Hashtable nameIndexMaps)
{
object[,] allData = null;
listDates = new List <DateTime>(100);
int colsCnt = nameIndexMaps.Count;
//读取表头
object[,] tableHeader = excelHelper.getArrayValue(ws, _dataStartRow - _tableHeaderRowsCnt, _dataStartCol, _tableHeaderRowsCnt, _maxReadColCnt);
//表头的列计数器
int headerCnt = 0;
//数组从1开始
for (int i = 1; i <= _maxReadColCnt; i++)
{
object obj = tableHeader[_dataStartRow - _tableHeaderRowsCnt, i];
if (obj != null)
{
string val = obj.ToString().Trim();
if (nameIndexMaps.ContainsKey(val))
{
nameIndexMaps[val] = i;
//找到了一列
headerCnt++;
}
//else
//{
// throw new Exception(string.Format("Excel文件{0}的表{1}中的'{2}'列与测点'{3}'的参数不配置,无法导入", fullPath, ws.Name, val, appInfo.appName));
//}
}
//else
//{
// throw new Exception(string.Format("Excel文件{0}的表{1}中的第{2}列不能为空", fullPath, ws.Name, i));
//}
}
if (headerCnt != colsCnt)
{
//在Excel中没有找到全部参数
var query = from i in nameIndexMaps.Keys.Cast <string>()
where nameIndexMaps[i] == null
select i;
string names = "";
foreach (string name in query)
{
names += name + " ";
}
throw new Exception(string.Format("Excel文件{0}\n表{1}中找不到以下列\n{2}", fullPath, ws.Name, names));
}
//找到所有表头数据
bool goLoop = true;
object[,] dateData = null;
//确定数据的行数
int dateColIndex = (int)nameIndexMaps[PubConstant.timeColumnName];
for (int j = 0; goLoop; j++)
{
dateData = excelHelper.getArrayValue(ws, _dataStartRow + j * _cntPerRead, _dataStartCol, _cntPerRead, dateColIndex);
for (int i = 1; i <= _cntPerRead; i++)
{
object obj = dateData[i, dateColIndex];
if (obj != null)
{
DateTime?cDate = convertToDateTime(obj);
if (cDate != null)
{
//更改数组里的值，以免再一次做转换
listDates.Add(cDate.Value);
}
else
{
throw new Exception(
string.Format("Excel文件{0}的表{1}中，第{2}行日期有误\n 该数据必须是日期或字符串类型,如果是字符串类型,其格式是须是{3}或{4}",
fullPath, ws.Name, _dataStartRow + j * _cntPerRead + i - 1, PubConstant.shortString, PubConstant.customString));
}
}
else
{
goLoop = false;
break;//数据只有这么多行
}
}
}
allData = excelHelper.getArrayValue(ws, _dataStartRow, _dataStartCol, listDates.Count, _maxReadColCnt);//包括时间列
return(allData);
}
private void GEN_Student_Record_Chart(PowerPoint.Slide Sld)
{
Sld.Select();
Sld.Layout = Microsoft.Office.Interop.PowerPoint.PpSlideLayout.ppLayoutBlank;
//Add a default chart in slide
Sld.Shapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, 200F, 200F, 400F, 300F);
//Add a heading
PowerPoint.Shape textBox = Sld.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 0, 20, 1000, 70);
textBox.TextFrame.TextRange.InsertAfter("Student-wise Consolidated Attendance Record");
textBox.TextFrame.TextRange.Font.Size = 30;
textBox.TextFrame.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
textBox.TextFrame.HorizontalAnchor = Microsoft.Office.Core.MsoHorizontalAnchor.msoAnchorCenter;
//Access the added chart
Microsoft.Office.Interop.PowerPoint.Chart ppChart = Sld.Shapes[1].Chart;
//Access the chart data
Microsoft.Office.Interop.PowerPoint.ChartData chartData = ppChart.ChartData;
//Create instance to Excel workbook to work with chart data
Microsoft.Office.Interop.Excel.Workbook dataWorkbook = (Microsoft.Office.Interop.Excel.Workbook)chartData.Workbook;
//Accessing the data worksheet for chart
Microsoft.Office.Interop.Excel.Worksheet dataSheet = dataWorkbook.Worksheets[1];
//Setting the range of chart
Microsoft.Office.Interop.Excel.Range tRange = dataSheet.Cells.get_Range("A1", "C4");
//Create a Table and applying the set range on chart data table
Microsoft.Office.Interop.Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A2"))).FormulaR1C1 = "";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A3"))).FormulaR1C1 = "";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A4"))).FormulaR1C1 = "";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("C2"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("C3"))).FormulaR1C1 = 0;
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("C4"))).FormulaR1C1 = 0;
//Delete the legend for neatness
Microsoft.Office.Interop.PowerPoint.Legend legend = null;
legend = ppChart.Legend;
legend.Delete();
//Setting chart title
ppChart.ChartTitle.Font.Italic = false;
ppChart.ChartTitle.Text = "Attendance Results";
ppChart.ChartTitle.Font.Size = 18;
ppChart.ChartTitle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
ppChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
ppChart.ChartTitle.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
//Accessing Chart value axis
Microsoft.Office.Interop.PowerPoint.Axis valaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlValue, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
//Setting values axis units
valaxis.MajorUnit = 2000.0F;
valaxis.MinorUnit = 1000.0F;
valaxis.MinimumScale = 0.0F;
valaxis.MaximumScale = 30.0F;
//Accessing Chart Depth axis
Microsoft.Office.Interop.PowerPoint.Axis Depthaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlSeriesAxis, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
Depthaxis.Delete();
//Setting chart rotation
ppChart.Rotation = 20; //Y-Value
ppChart.Elevation = 15; //X-Value
ppChart.RightAngleAxes = false;
var ws = new WebSocket("ws://localhost:1234");
var the_message = "";
ws.OnMessage += (sender12, sent) =>
{
try
{
the_message = sent.Data;
var the_message2 = JsonConvert.DeserializeObject <List <StudentRecordNode> >(the_message);
var hiba_p = 0;
var jeehan_p = 0;
var shiza_p = 0;
var hiba_a = 0;
var jeehan_a = 0;
var shiza_a = 0;
foreach (var m in the_message2)
{
if (m.name == "Hiba")
{
}
}
}
catch (InvalidCastException e)
{
Debug.WriteLine("Source: ", e.Source);
}
};
ws.Connect();
ws.OnError += (sender12, sent) =>
{
Debug.WriteLine("error " + sent.Message);
};
ws.OnClose += (sender12, sent) =>
{
Debug.WriteLine("disconnect with host");
};
}
private void btnInsert_Click(object sender, EventArgs e)
{
OpenFileDialog open = new OpenFileDialog();
open.ShowDialog();
if (open.FileName != null)
{
string path = open.FileName;
Microsoft.Office.Interop.Excel.Application ap = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = ap.Workbooks.Open(open.FileName);
//
List <int> errorQuestion = new List <int>();
int countQS = 0;
try
{
Microsoft.Office.Interop.Excel.Worksheet sheet = wb.Sheets[1];
Microsoft.Office.Interop.Excel.Range range = sheet.UsedRange;
int row, col;
row = range.Rows.Count;
col = range.Rows.Count;
for (int i = 1; i <= row; i++)
{
CauHoi ch = new CauHoi();
ch.CauHoi1 = range.Cells[i, 1].Value.ToString();
ch.DapAn_A = range.Cells[i, 2].Value.ToString();
ch.DapAn_B = range.Cells[i, 3].Value.ToString();
ch.DapAn_C = range.Cells[i, 4].Value.ToString();
ch.DapAn_D = range.Cells[i, 5].Value.ToString();
ch.DapAnDung = char.Parse(range.Cells[i, 6].Value.ToString());
ch.Khoi = int.Parse(range.Cells[i, 7].Value.ToString());
ch.DoKho = range.Cells[i, 8].Value.ToString();
ch.MaMH = range.Cells[i, 9].Value.ToString();
db.CauHois.InsertOnSubmit(ch);
try
{
db.SubmitChanges();
countQS++;
}
catch
{
//Lưu lại thứ tự câu hỏi bị lỗi
errorQuestion.Add(i);
}
}
}
catch
{
}
MessageBox.Show("Thêm thành công : " + countQS + " câu");
LoadQuestions();
if (errorQuestion.Count() > 0)
{
StringBuilder build = new StringBuilder();
foreach (int i in errorQuestion)
{
build.Append(i.ToString()).AppendLine();
}
MessageBox.Show("Lỗi thêm câu hỏi ở câu " + build.ToString());
}
}
else
{
MessageBox.Show("Lỗi chọn tập tin", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void CxFlatRoundButton26_Click(object sender, EventArgs e)
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
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);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < dataGridView3.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView3.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView3.Rows.Count; r++)
{
for (int i = 0; i < dataGridView3.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView3.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
public string ExportExcel(DataSet ds, string saveFileName)
{
try
{
if (ds == null)
{
return("数据库为空");
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return("无法创建Excel对象，可能您的机子未安装Excel");
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
return("成功保存到Excel");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
private void ExportExcels(string fileName, DataGridView gridview)
{
try
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
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);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Worksheet objSheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Excel.Worksheet objSheet2 = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
worksheet.Name = "自动率";
objSheet.Name = "行车自动率";
objSheet2.Name = "行车结果自动率";
Save2ExcelSheet(gridview, worksheet);
Save2ExcelSheet(this.dataGridView3, objSheet);
Save2ExcelSheet(this.dataGridView3, objSheet2);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private double WriteMonthlyTally(List<Donation> donations)
{
double total = 0.0;
try
{
if (oApplication == null)
{
logger.WriteError("Unable to start an excel sheet");
return total;
}
oApplication.Visible = false;
oWorkbook = oApplication.Workbooks.Add(Type.Missing);
oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1];
//oWorksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
FormatFont();
int maxRowDepth = 0;
// Fill in all the columns (categories) with the donations
foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category)))
{
int row = 1;
oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString();
foreach (var item in donations)
{
string value = item.Get(currentCategory).ToString();
if (value.Equals("0"))
continue;
oWorksheet.Cells[++row, (int)currentCategory] = value;
if (currentCategory == Donation.Category.Other)
{
oWorksheet.Cells[1, (int)currentCategory + 1] = "Specify";
oWorksheet.Cells[row, (int)currentCategory + 1] = item.OtherCategory;
}
}
// keep a running total of how deep we go (rows) into the spreadsheet
maxRowDepth = row > maxRowDepth ? row : maxRowDepth;
}
int totalsRow = CalculateTotals(2, maxRowDepth, out total);
// Some formatting of row 1 (font and bold)
oWorksheet.Rows[1, Type.Missing].Font.Size = 9;
oWorksheet.Rows[1, Type.Missing].Font.Bold = true;
// Totals row bold.
oWorksheet.Cells[totalsRow, Type.Missing].Font.Bold = true;
// Calculations at the end of the file (jeffersonville data...)
oWorksheet.Cells[totalsRow + 3, 3] = "Total of Tithe & Offering:";
Microsoft.Office.Interop.Excel.Range r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 3, 1], oWorksheet.Cells[totalsRow + 3, 3]];
MergeAndAlignRight(r);
Microsoft.Office.Interop.Excel.Range TitheOffering = oWorksheet.Cells[totalsRow + 3, 4];
TitheOffering.Formula = string.Format("=SUM({0},{1})",
oWorksheet.Cells[totalsRow, (int)Donation.Category.Tithes].Address, oWorksheet.Cells[totalsRow, (int)Donation.Category.Offering].Address);
AddBoxAroundRange(TitheOffering);
SetCurrencyFormat(TitheOffering);
oWorksheet.Cells[totalsRow + 1, 8] = "Jeffersonville 10%:";
r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 1, 6], oWorksheet.Cells[totalsRow + 1, 8]];
MergeAndAlignRight(r);
Microsoft.Office.Interop.Excel.Range Jeff10 = oWorksheet.Cells[totalsRow + 1, 10];
Jeff10.Formula = string.Format("=({0}*0.1)", TitheOffering.Address);
AddBoxAroundRange(Jeff10);
SetCurrencyFormat(Jeff10);
oWorksheet.Cells[totalsRow + 2, 8] = "Jeffersonville First Fruits:";
r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 2, 6], oWorksheet.Cells[totalsRow + 2, 8]];
MergeAndAlignRight(r);
Microsoft.Office.Interop.Excel.Range JeffFF = oWorksheet.Cells[totalsRow + 2, 10];
JeffFF.Formula = string.Format("=({0}*0.5)", oWorksheet.Cells[totalsRow, (int)Donation.Category.FirstFruits].Address);
AddBoxAroundRange(JeffFF);
SetCurrencyFormat(JeffFF);
oWorksheet.Cells[totalsRow + 3, 8] = "Jeffersonville Total Tithes & First Fruits:";
r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 3, 5], oWorksheet.Cells[totalsRow + 3, 8]];
MergeAndAlignRight(r);
oWorksheet.Cells[totalsRow + 3, 10].Formula = string.Format("=SUM({0},{1})", Jeff10.Address, JeffFF.Address);
AddBoxAroundRange(oWorksheet.Cells[totalsRow + 3, 10]);
SetCurrencyFormat(oWorksheet.Cells[totalsRow + 3, 10]);
int lastRow = totalsRow + 3;
int lastCol = (int)Donation.Category.Other + 1;
// Format cells to have boxes and set print area
AddBoxAroundEachCellInRange(oWorksheet.Range[oWorksheet.Cells[1,1], oWorksheet.Cells[totalsRow,(int)Donation.Category.Other +1]]);
SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[lastRow, lastCol]]);
//oWorksheet.Columns[Type.Missing, lastCol].PageBreak = Microsoft.Office.Interop.Excel.XlPageBreak.xlPageBreakManual;
// set the return value equal to the total calculated
}
catch (Exception e )
{
logger.WriteError("Failure while writing excel file. Message: {0}, Stack: {1}", e.Message, e.StackTrace);
}
finally
{
Cleanup();
}
return total;
}
/// <summary>
/// 将DataGridView的数据导入到Excel中
/// </summary>
/// <param name="dgv"></param>
public void setExcel(DataGridView dgv)
{
//总可见列数，总可见行数
int colCount = dgv.Columns.GetColumnCount(DataGridViewElementStates.Visible);
int rowCount = dgv.Rows.GetRowCount(DataGridViewElementStates.Visible);
//dataGridView 没有数据提示
if (dgv.Rows.Count == 0 || rowCount == 0)
{
MessageBox.Show("表中没有数据", "提示");
}
else
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
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);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "统计结果保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
}
public void ExcelXLS(DirectoryInfo RutaExcel, DataTable Datos, string CadenaConexion)
{
ConexionBd conexion = new ConexionBd();
var NuevoArchivo = new FileInfo(RutaExcel + @"\xlExcel7Pass2.xls");
if (NuevoArchivo.Exists)
{
NuevoArchivo.Delete();
NuevoArchivo = new FileInfo(RutaExcel + @"\xlExcel7Pass2.xls");
}
Datos.TableName = "Polizas";
int inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
//Create Excel WorkSheet
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = "Poliza"; //Name worksheet
//Write Column Name
for (int i = 0; i < Datos.Columns.Count; i++)
{
excelWorkSheet.Cells[1, i + 1] = Datos.Columns[i].ColumnName; //.ToUpper();
}
//Write Rows
for (int m = 0; m < Datos.Rows.Count; m++)
{
for (int n = 0; n < Datos.Columns.Count; n++)
{
inColumn = n + 1;
inRow = 2 + m; //1 + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = Datos.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "W" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520");
}
}
}
////Excel Header
//OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "O1");
//cellRang.Merge(false);
//cellRang.Interior.Color = System.Drawing.Color.Blue;
//cellRang.Font.Color = System.Drawing.Color.Black;
//cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
//cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
//cellRang.Font.Size = 16;
//excelWorkSheet.Cells[1, 1] = "Greate Novels Of All Time";
//Style table column names
Excel.Range cellRang = excelWorkSheet.get_Range("A1", "W1");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#00008B");
cellRang = excelWorkSheet.get_Range("X1", "Z1");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520");
excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Formate price column
excelWorkSheet.get_Range("O2").EntireColumn.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"; //.NumberFormat = "0.00";
excelWorkSheet.get_Range("O2").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as Excel._Worksheet).Activate();
excelWorkBook.SaveAs(NuevoArchivo, Excel.XlFileFormat.xlExcel7, Default, Default, false, Default, Excel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
//excelWorkBook.SaveAs(NuevoArchivo, OfficeExcel.XlFileFormat.xlExcel5, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
//excelWorkBook.SaveAs(NuevoArchivo, OfficeExcel.XlFileFormat.xlExcel9795, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
}
public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
public static void ReadExistingExcel(dynamic data)
{
string path = @"C:\Users\Meir\Downloads\ABISample\Copy of Boiler Batch OP-42 Form v01,2013 BLANK.XLSX";
oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the existing sheets
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("OP-42");
mWSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("csv");
// mWSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("csv");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
// Edit values for OP-42
mWSheet1.Cells[5, 20] = data.id.ToString();
mWSheet1.Cells[6, 21] = data.date.ToString();
mWSheet1.Cells[8, 7] = data.company.ToString();
mWSheet1.Cells[10, 7] = data.name.ToString();
mWSheet1.Cells[12, 6] = data.id.ToString();
mWSheet1.Cells[12, 9] = data.job.ToString();
mWSheet1.Cells[14, 7] = data.address.ToString();
//mWSheet1.Cells[15, 20] = data.number.ToString();
//mWSheet1.Cells[17, 21] = data.number.ToString();
mWSheet1.Cells[17, 10] = data.date.ToString();
mWSheet1.Cells[16, 5] = data.name_2.ToString();
mWSheet1.Cells[18, 10] = data.phone.ToString();
mWSheet1.Cells[18, 5] = data.email.ToString();
mWSheet1.Cells[30, 10] = data.date.ToString();
// Edit values for csv
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
// bottom data
for (int index = 1; index < 10; index++)
{
mWSheet2.Cells[0 + index, 1] = data.boro.ToString();
mWSheet2.Cells[0 + index, 2] = data.device.ToString();
mWSheet2.Cells[0 + index, 3] = data.md.ToString();
mWSheet2.Cells[0 + index, 4] = data.serial.ToString();
mWSheet2.Cells[0 + index, 5] = data.house.ToString();
mWSheet2.Cells[0 + index, 6] = data.street.ToString();
mWSheet2.Cells[0 + index, 7] = data.block.ToString();
mWSheet2.Cells[0 + index, 8] = data.lot.ToString();
mWSheet2.Cells[0 + index, 9] = data.date.ToString();
mWSheet2.Cells[0 + index, 10] = data.j.ToString();
mWSheet2.Cells[0 + index, 11] = data.k.ToString();
mWSheet2.Cells[0 + index, 12] = data.l.ToString();
mWSheet2.Cells[0 + index, 13] = data.m.ToString();
mWSheet2.Cells[0 + index, 14] = data.n.ToString();
mWSheet2.Cells[0 + index, 15] = data.o.ToString();
mWSheet2.Cells[0 + index, 16] = data.p.ToString();
mWSheet2.Cells[0 + index, 17] = data.q.ToString();
mWSheet2.Cells[0 + index, 18] = data.r.ToString();
mWSheet2.Cells[0 + index, 19] = data.location.ToString();
mWSheet2.Cells[0 + index, 20] = data.t.ToString();
}
SaveFileDialog saveFileDialog2 = new SaveFileDialog
{
Filter = "xlsx files|*.xlsx",
DefaultExt = "xlsx",
AddExtension = true
};
saveFileDialog2.ShowDialog();
string savePath = saveFileDialog2.FileName;
mWorkBook.SaveAs(savePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
mWorkBook.Close(true, "newABIExcel", false);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
{
Microsoft.Office.Interop.Excel.Worksheet s =
(Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return(s);
}
/// <summary>
/// Initialize File
/// </summary>
private void CreateExcel()
{
app = new Excel.Application();
workbook = app.Workbooks.Open(ExcelFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheets = workbook.Sheets;
//get first sheet of file excel
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worksheets.get_Item(1);
}
public void SpremanjeFaktura()
{
//unos podataka
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
excelApp = new Excel.Application();
theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
sheets = theWorkbook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
int rowIndex, colIndex;
excelApp.Workbooks.Open(OtvorenaFaktura);
//DVO
var qa = (worksheet.Cells[18, 6] as Microsoft.Office.Interop.Excel.Range).Value;
string slovoqa = Convert.ToString(qa);
if (slovoqa == "Datum valute:")
{
rowIndex = 17; colIndex = 8;
excelApp.Cells[rowIndex, colIndex] = DatumDvo;
}
else
rowIndex = 18; colIndex = 8;
excelApp.Cells[rowIndex, colIndex] = DatumDvo;
//valuta
var qb = (worksheet.Cells[19, 6] as Microsoft.Office.Interop.Excel.Range).Value;
string slovoqb = Convert.ToString(qb);
if (slovoqb == "Vrijeme:")
{
rowIndex = 18; colIndex = 8;
excelApp.Cells[rowIndex, colIndex] = DatumValuta;
}
else
rowIndex = 19; colIndex = 8;
excelApp.Cells[rowIndex, colIndex] = DatumValuta;
//faktura
var q = (worksheet.Cells[22, 6] as Microsoft.Office.Interop.Excel.Range).Value;
string slovoq = Convert.ToString(q);
if (slovoq == "OIB:" || slovoq == "oib:" || slovoq == "Oib:")
{
rowIndex = 21; colIndex = 7;
excelApp.Cells[rowIndex, colIndex] = BrojFakture;
}
else
rowIndex = 22; colIndex = 7;
excelApp.Cells[rowIndex, colIndex] = BrojFakture;
//cijena, količina, rabat i PDV
for (int red = 26; red <= 31; red += 1)
{
var trazi = (worksheet.Cells[red, 6] as Microsoft.Office.Interop.Excel.Range).Value;
string STRtrazi = Convert.ToString(trazi);
float val;
if (((float.TryParse(STRtrazi, out val))) && STRtrazi.Length != 0)
{
excelApp.Cells[red, 6] = CijenaFakture;
excelApp.Cells[red, 5] = Kolicina;
if (rabat != 0)
{
excelApp.Cells[red, 9] = Convert.ToDouble(rabat);
excelApp.Cells[red, 10] = Convert.ToDouble(PDV);
}
else
{
excelApp.Cells[red, 9] = Convert.ToDouble(PDV);
}
break;
}
}
//relacija
string RedRelacija = File.ReadAllText(Application.StartupPath + "\\RedRelacija");
string StupacRelacija = File.ReadAllText(Application.StartupPath + "\\StupacRelacija");
excelApp.Cells[Convert.ToInt16(RedRelacija), Convert.ToInt16(StupacRelacija)] = Relacija;
File.Delete(Application.StartupPath + "\\RedRelacija");
File.Delete(Application.StartupPath + "\\StupacRelacija");
//pozicija
for (int stupac = 1; stupac <= 5; stupac++)
{
for (int red = 26; red <= 34; red++)
{
var trazi = (worksheet.Cells[red, stupac] as Microsoft.Office.Interop.Excel.Range).Value;
string STRtrazi = Convert.ToString(trazi);
Boolean equals = String.Equals(STRtrazi, "Pozicija:", StringComparison.OrdinalIgnoreCase);
if (equals == true)
{
excelApp.Cells[red, stupac + 1] = Pozicija;
//MessageBox.Show(red + " " + stupac);
break;
}
}
}
var datum = Convert.ToDateTime(DatumDvo);
int mjesec = datum.Month;
int godina = datum.Year;
FileInfo fInfo = new FileInfo(PutanjaOtvoreneFakture);
string strFilePath = fInfo.DirectoryName;
//plačeno,postojeća relacija
DirectoryInfo dirPlaceno = new DirectoryInfo(strFilePath + "\\" + mjesec + "-" + godina + "\\PLAČENO\\");
string dir = Convert.ToString(dirPlaceno);
//neplačeno,postojeća relacija
DirectoryInfo dirNePlaceno = new DirectoryInfo(strFilePath + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\");
string dirNe = Convert.ToString(dirNePlaceno);
//plačeno,nova relacija
DirectoryInfo dirPlacenoNovo = new DirectoryInfo(PutanjaOtvoreneFakture + "\\" + Relacija+ "\\" + mjesec + "-" + godina + "\\PLAČENO\\");
string dirNovo1 = Convert.ToString(dirPlacenoNovo);
string dirNovo = dirNovo1.Replace("\\\\", "\\");
//neplačeno,nova relacija
DirectoryInfo dirNePlacenoNovo = new DirectoryInfo(PutanjaOtvoreneFakture + "\\" + Relacija + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\");
string dirNeNovo1 = Convert.ToString(dirNePlacenoNovo);
string dirNeNovo = dirNeNovo1.Replace("\\\\", "\\");
string open;
//nova ruta
if (NovaRuta == true)
{
//stvori oba direktorija,PLAČENO i NEPLAČENO
dirPlacenoNovo.Create();
dirNePlacenoNovo.Create();
if (Placeno == true)
{
excelApp.ActiveWorkbook.SaveCopyAs(dirNovo + BrojFakture + " " + DatumDvo + ".xlsx");
open = dirNovo + BrojFakture + " " + DatumDvo + ".xlsx";
}
else
{
excelApp.ActiveWorkbook.SaveCopyAs(dirNeNovo + BrojFakture + " " + DatumDvo + ".xlsx");
open = dirNeNovo + BrojFakture + " " + DatumDvo + ".xlsx";
}
}
//normalna faktura,postojeća relacija
else
{
//stvori oba direktorija,PLAČENO i NEPLAČENO
dirNePlaceno.Create();
dirPlaceno.Create();
if (Placeno == true)
{
if (dirPlaceno.Exists)
{
excelApp.ActiveWorkbook.SaveCopyAs(dir + BrojFakture + " " + DatumDvo + ".xlsx");
open = dir + BrojFakture + " " + DatumDvo + ".xlsx";
}
//Ako taj direktorij ne postoji->napravi ga
else
{
excelApp.ActiveWorkbook.SaveCopyAs(dir + BrojFakture + " " + DatumDvo + ".xlsx");
open = dir + BrojFakture + " " + DatumDvo + ".xlsx";
}
}
else
{
if (dirNePlaceno.Exists)
{
excelApp.ActiveWorkbook.SaveCopyAs(dirNe + BrojFakture + " " + DatumDvo + ".xlsx");
open = dirNe + BrojFakture + " " + DatumDvo + ".xlsx";
}
//Ako taj direktorij ne postoji->napravi ga
else
{
excelApp.ActiveWorkbook.SaveCopyAs(dirNe + BrojFakture + " " + DatumDvo + ".xlsx");
open = dirNe + BrojFakture + " " + DatumDvo + ".xlsx";
}
}
}
//Otvaranje spremljene fakture
SpremljenaFaktura = open.Replace("\\", "\\\\");
SpremanjeZaSync();
Zatvaranje();
System.Diagnostics.Process.Start(SpremljenaFaktura);
File.AppendAllText(Application.StartupPath + "\\Fakture\\data", Environment.NewLine + BrojFakture);
}
