private void ExportarDataGridViewExcel(DataGridView grd)
{
SaveFileDialog fichero = new SaveFileDialog();
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo =
(Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
//Recorremos el DataGridView rellenando la hoja de trabajo
for (int i = 0; i < grd.Rows.Count ; i++)
{
for (int j = 0; j < grd.Columns.Count; j++)
{
hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString();
}
}
libros_trabajo.SaveAs(fichero.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
}
}
private void dataGridViewExportAsExcel(DataGridView dgv, string FileFullPath)
{
string strExamPath = FileFullPath.Substring(0, FileFullPath.LastIndexOf('\\'));
if (!System.IO.Directory.Exists(strExamPath))
{
MessageBox.Show(FileFullPath, "目录错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.EnableEvents = false;
excelApplication.Application.DisplayAlerts = false;
excelApplication.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
excelApplication.Visible = false;
int nRowIndex = 0;
int nColumnIndex = 0;
object[,] strArr = new object[gvDataRecord.RowCount + 1, gvDataRecord.ColumnCount];
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
strArr[nRowIndex, nColumnIndex] = dgvc.HeaderText;
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
foreach (DataGridViewRow dgvr in dgv.Rows)
{
foreach (DataGridViewCell dgvcell in dgvr.Cells)
{
strArr[nRowIndex, nColumnIndex] = dgvcell.Value.ToString();
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
}
string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(dgv.ColumnCount, dgv.RowCount + 1);
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1",strExcelMaxColumnIndex);
myRange.get_Resize(dgv.RowCount + 1, dgv.ColumnCount);
try
{
myRange.Value2 = strArr;
myRange.Columns.AutoFit();
myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("文件成功保存到了" + FileFullPath, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
excelApplication.Quit();
killexcel(excelApplication);
GC.Collect();
}
/// <summary>
/// 导出Excel表格文件
/// </summary>
public static void ExportExcelFile(DataTable ExcelTable)
{
try
{
string SaveExcelName = string.Empty;//保存的Excel文件名称
SaveFileDialog SFDialog = new SaveFileDialog();
SFDialog.DefaultExt = "xlsx";
SFDialog.Filter = "Excel文件(*.xlsx;*.xls)|*.xlsx;*.xls";
SFDialog.ShowDialog();
SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
if (SaveExcelName.IndexOf(":") < 0) return ;
Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序
object missing = System.Reflection.Missing.Value;
if (XlsApp == null)
{
MessageBoxEx.Show("无法创建Excel表格文件，您的电脑可能未安装Excel软件！","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return ;
}
else
{
Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
int TotalCount = ExcelTable.Rows.Count;
int rowRead = 0;//读取行数
float PercentRead = 0;//导入百分比
//写入字段名
for (int i = 0; i < ExcelTable.Columns.Count;i++ )
{
WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
Ran=(Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1,i+1];//列名称写入单元格
Ran.Interior.ColorIndex = 15;
Ran.Font.Bold = true;//标题加粗
}
ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
ProgressBarMsg.StepMaxValue = TotalCount;//获取进度条最大范围
ProgBarMsg.ShowDialog();//显示进度条
string ProgressPercent = string.Empty;//进度百分比
//写字段值
for (int j = 0; j < ExcelTable.Rows.Count; j++)
{
for (int k = 0; k < ExcelTable.Columns.Count; k++)
{
WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
}
rowRead++;
PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
ProgressPercent = PercentRead.ToString("0.00") + "%";
ProgressBarMsg.ProgressValue = ProgressPercent;//获取进度百分比
ProgressBarMsg.CurrentValue = rowRead;//获取当前进度值
Application.DoEvents();//处理当前在消息队列中所有windows消息
}
WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing,missing);
Ran = WkSheet.get_Range(WkSheet.Cells[2, 1],WkSheet.Cells[ExcelTable.Rows.Count + 2,ExcelTable.Columns.Count]);//给工作表指定区域
//设置Excel表格边框样式
Ran.BorderAround2(missing,Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,missing,missing);
Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度
if (ExcelTable.Columns.Count > 1)
{//设置垂直表格颜色索引
Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
}
//关闭表格对像，并退出应用程序域
WkBk.Close(missing,missing,missing);
XlsApp.Quit();
ProgBarMsg.Close();//关闭进度条
}
}catch(Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
public void Print(YellowstonePathology.Business.Search.ReportSearchList caseList, string description, DateTime printDate)
{
Microsoft.Office.Interop.Excel.Application xlApp;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(@"\\CFileServer\documents\ReportTemplates\MolecularTesting\CaseList.xlt");
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
ws.Cells[3, 1] = "Batch: " + description + " - " + printDate.ToShortDateString();
int rowPosition = 6;
for (int i = caseList.Count - 1; i > -1; i--)
{
ws.Cells[rowPosition, 1] = caseList[i].ReportNo;
ws.Cells[rowPosition, 2] = caseList[i].PanelSetName;
ws.Cells[rowPosition, 3] = caseList[i].PatientName;
ws.Cells[rowPosition, 4] = caseList[i].PhysicianName + " - " + caseList[i].ClientName;
ws.Cells[rowPosition, 5] = caseList[i].OrderedBy;
rowPosition++;
}
Object oMissing = Type.Missing;
Object oFalse = false;
ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(oFalse, oMissing, oMissing);
xlApp.Quit();
}
public void ConvertCsvToExcel_MicrosoftOfficeInteropExcel()
{
StringBuilder content = new StringBuilder();
content.AppendLine("param1\tparam2\tstatus");
content.AppendLine("0.5\t10\tpassed");
content.AppendLine("10\t20\tfail");
using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx"))
{
Clipboard.SetText(content.ToString());
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();
// for excel visibility
//xlexcel.Visible = true;
// Creating a new workbook
xlWorkBook = xlexcel.Workbooks.Add(misValue);
// Putting Sheet 1 as the sheet you want to put the data within
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
// creating the range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.Paste(CR, false);
xlWorkSheet.SaveAs(xlsxFile.FileName);
xlexcel.Quit();
Console.WriteLine("Created file {0}", xlsxFile.FileName);
}
}
public static void OpenExcelDocs2(string filename, double[] content)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //引用Excel工作簿
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ; //引用Excel工作页面
excel.Visible = false;
sheet.Cells[24, 3] = content[1];
sheet.Cells[25, 3] = content[0];
book.Save();
book.Close(Type.Missing, Type.Missing, Type.Missing);
excel.Quit(); //应用程序推出，但是进程还在运行
IntPtr t = new IntPtr(excel.Hwnd); //杀死进程的好方法，很有效
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
//sheet = null;
//book = null;
//excel = null; //不能杀死进程
//System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); //可以释放对象，但是不能杀死进程
//System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
public void insertBTN(String query, String path)
{
sqlCon.ConnectionString = conn;
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter(query, sqlCon);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.Application ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(path + "\\Results.xlsx");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
public List<string> GetExcelSheets(string excelFileName)
{
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
true,
false,
0,
true,
false,
false);
Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
// get the first and only worksheet from the collection of worksheets
List<string> sheetNames = new List<string>();
Regex regSheetName = new Regex("Sheet\\d+");
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) {
if (!regSheetName.IsMatch(sheet.Name)) {
sheetNames.Add(sheet.Name);
}
Marshal.ReleaseComObject(sheet);
}
excelFileObject.Quit();
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workBookObject);
Marshal.ReleaseComObject(excelFileObject);
return sheetNames;
}
/// <summary>
/// Закрыть приложение
/// </summary>
public void CloseApplication()
{
_applicationWord?.Quit();
_applicationExcel?.Application.Quit();
_applicationExcel?.Quit();
_applicationWord = null;
_applicationExcel = null;
ExcelInstance.KillAllPreviousProcess();
}
public string exportData(String query)
{
string conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = conn;
sqlConn.Open();
SqlDataAdapter da = new SqlDataAdapter(query, sqlConn);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.Application ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
}
}
string path = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)).FullName;
if (Environment.OSVersion.Version.Major >= 6)
{
path = Directory.GetParent(path).ToString();
}
//This path belongs to the computer where the application is running.
//I have to find a way to get the path from the client side...
try
{
ExcelApp.ActiveWorkbook.SaveAs(path + "\\Desktop\\Results.xlsx");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
return "The file has been generated successfully and it is ready on your Desktop.";
}
catch (Exception e)
{
return "Some error has occured. Message from Server: " + e.Message;
}
}
/*
* Modify by ManhTV3 on 27/04/2012
* Kết xuất file excel báo cáo tiến độ chuẩn hóa
* */
public static void Prc_Bcao_Chuan_Hoa(string p_sourcePath,
string p_destinPath)
{
Microsoft.Office.Interop.Excel.Application _excelApp;
_excelApp = new Microsoft.Office.Interop.Excel.Application();
// Mở file mẫu excel
Microsoft.Office.Interop.Excel.Workbook workBook =
_excelApp.Workbooks.Open(p_sourcePath, //Filename
Type.Missing, //UpdateLinks
Type.Missing, //ReadOnly
Type.Missing, //Format
Type.Missing, //Password
Type.Missing, //WriteResPassword
Type.Missing, //IgnoreReadOnlyRecommended
Type.Missing, //Origin
Type.Missing, //Delimiter
Type.Missing, //Editable
Type.Missing, //Notify
Type.Missing, //Converter
Type.Missing, //AddToMru
Type.Missing, //Local
Type.Missing); //CorruptLoad
// Lấy dữ liệu
using (CLS_DBASE.ORA _ora = new CLS_DBASE.ORA(GlobalVar.gl_connTKTQ))
{
try
{
_ora.TransStart();
// Kết xuất báo cáo tiến độ chuẩn hóa
string _sql = "SELECT * FROM vw_bc_ch;";
DataTable _dt = _ora.TransExecute_DataTable(_sql);
if (_dt.Rows.Count > 0)
CLS_EXCEL.Prc_Add_Sheets(workBook, "BaoCaoChuanHoa", _dt);
_dt.Clear();
workBook.SaveAs(p_destinPath,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//_ora.TransCommit();
}
finally
{
// Đóng file mẫu excel
workBook.Close(false, p_sourcePath, null);
_excelApp.Quit();
CLS_EXCEL.Prc_releaseObject(workBook);
CLS_EXCEL.Prc_releaseObject(_excelApp);
}
}
}
public static string ExpExcel(string path)
{
ReturnDoc obj_ReturnDoc = new ReturnDoc();
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
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[1, 1] = "管道名称";
// 数据查询
OleDbDataReader rst = null;
string ret = CommonQuery.qryRst("select * from PIPELINE", ref rst);
Int16 i = 2;
DicCache dic = DicCache.getInstance();
if (ret.Equals("0"))
{
while (rst.Read())
{
worksheet.Cells[i, 1] = rst["DATA1"].ToString();
i++;
}
rst.Close();
}
string sPath = path;
string filename = "海底管道数据导出.xls";
workbook.Saved = true;
workbook.SaveCopyAs(sPath + filename);
xlApp.Quit();
obj_ReturnDoc.addErrorResult(Common.RT_SUCCESS);
obj_ReturnDoc.setFuncErrorInfo(filename);
}
catch (Exception e)
{
obj_ReturnDoc.addErrorResult(Common.RT_FUNCERROR);
obj_ReturnDoc.setFuncErrorInfo(e.Message);
}
return obj_ReturnDoc.getXml();
}
public void ExportExcel(string fileName, DataGridView myDGV)
{
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 < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
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);
}
public static void toXLSX(string filePath, string destination)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// this does not throw exception if file doesnt exist
File.Delete(destination);
wb.SaveAs(destination, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.Missing, Type.Missing);
wb.Close(false, Type.Missing, Type.Missing);
app.Quit();
}
public static FileInfo Export(ExcelPdfExportSettings exportSettings)
{
if (exportSettings == null) throw new ArgumentNullException("exportSettings");
if (exportSettings.OutputPdfPath.IsNullOrBlank()) throw new ArgumentException("OutputPdfPath is required.");
if (File.Exists(exportSettings.OutputPdfPath))
{
File.Delete(exportSettings.OutputPdfPath);
}
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application() { ScreenUpdating = false, DisplayAlerts = false }; ;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
try
{
// Create new instance of Excel and open Workbook
excelWorkbook = excelApplication.Workbooks.Open(exportSettings.WorkbookPath);
if (excelWorkbook == null)
{
throw new ApplicationException(String.Format("Specified Workbook '{0}' could not be found or may be corrupt.", exportSettings.WorkbookPath));
}
// Get Active sheet and Range to export to Pdf
var sheets = excelWorkbook.Sheets.OfType<Microsoft.Office.Interop.Excel.Worksheet>();
Microsoft.Office.Interop.Excel.Worksheet worksheet = sheets.FirstOrDefault(s => s.Name.Equals(exportSettings.WorksheetName, StringComparison.OrdinalIgnoreCase));
if (worksheet == null)
{
throw new ApplicationException(String.Format("Specified Workbook Sheet '{0}' not found.", exportSettings.WorksheetName));
}
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(exportSettings.WorksheetRange);
range.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, exportSettings.OutputPdfPath);
return new FileInfo(exportSettings.OutputPdfPath);
}
catch (Exception e)
{
throw e;
}
finally
{
if (excelWorkbook != null)
{ excelWorkbook.Close(); }
excelApplication.Quit();
excelApplication = null;
excelWorkbook = null;
}
}
/// <summary>
/// 根据
/// </summary>
/// <param name="contractFilePath"></param>
/// <returns></returns>
public static IEnumerable<Contract> GetContracts(string contractFilePath)
{
var app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = app.Workbooks.Open(contractFilePath, 0,
false, 5, System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
false, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, true, false,
System.Reflection.Missing.Value, false, false, false);
//
//app.Visible = true;
var result = ContractCollector.GetContracts(app);
app.Quit();
return result;
}
public void inp(Form1 f)
{
string str, filename="";
int rCnt;
int cCnt;
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "Excel (*.XLS;*.XLSX)|*.XLS;*.XLSX";
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
System.IO.StreamReader sr = new
System.IO.StreamReader(openFileDialog1.FileName);
filename = openFileDialog1.FileName;
sr.Close();
f.Toggle();
}
// System.Data.DataTable tb = new System.Data.DataTable();
// string filename = openFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook Book;
Microsoft.Office.Interop.Excel.Worksheet Sheet;
Microsoft.Office.Interop.Excel.Range Range;
Book = ExcelApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Sheet = (Microsoft.Office.Interop.Excel.Worksheet)Book.Worksheets.get_Item(1);
Range = Sheet.UsedRange;
for (rCnt = 1; rCnt <= Range.Rows.Count; rCnt++)
{
f.Tabl.Rows.Add(1);
for (cCnt = 1; cCnt <= 13; cCnt++)
{
str = (string)(Range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
f.Tabl.Rows[rCnt - 1].Cells[cCnt - 1].Value = str;
}
}
Book.Close(true, null, null);
ExcelApp.Quit();
releaseObject(Sheet);
releaseObject(Book);
releaseObject(ExcelApp);
}
public bool CreateExcel(List<Po> pos)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (Po po in pos)
{
xlWorkSheet.Cells[po.Row, po.Column] = po.Value;
}
xlWorkBook.SaveAs("C:\\newcat2", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
int test = 0;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
return true;
}
private void btnExportarExcel_Click(object sender, EventArgs e)
{
try
{
saveFileDialog1.InitialDirectory = "C:";
saveFileDialog1.Title = "Save as Excel File";
saveFileDialog1.FileName = "";
saveFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*xlsx";
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 20;
for (int i = 1; i < dtgItensOr.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dtgItensOr.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dtgItensOr.RowCount - 1; i++)
{
for (int j = 0; j < dtgItensOr.ColumnCount; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dtgItensOr.Rows[i].Cells[j].Value.ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private static void ConvertExcel(string fileName, string sourceFile)
{
Microsoft.Office.Interop.Excel.Workbook excelDoc = null;
Microsoft.Office.Interop.Excel.Application excel = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application
{
Visible = false,
DisplayAlerts = false
};
excelDoc = excel.Workbooks.Open(sourceFile, ReadOnly: true, Delimiter: ";", Format: 6, Origin: Microsoft.Office.Interop.Excel.XlPlatform.xlWindows);
foreach (Microsoft.Office.Interop.Excel.Worksheet currentSheet in excelDoc.Worksheets)
{
currentSheet.Columns.AutoFit();
}
excelDoc.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault);
excel.DisplayAlerts = true;
excelDoc.Close();
excel.Quit();
}
catch (Exception ex)
{
try
{
excel.DisplayAlerts = true;
excelDoc?.Close();
excel?.Quit();
}
catch { }
throw;
}
}
public static void ExportToExcel(DataGridView dg, string titulo)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = titulo;
int cellRowIndex = 1;
int cellColumnIndex = 1;
worksheet.Columns.NumberFormat = "@";
//Loop through each row and read value from each column.
for (int i = -1; i < dg.Rows.Count; i++)
{
for (int j = 0; j < dg.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dg.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dg.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Exportación realizada");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Alter.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D2", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("H2", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 变更机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("D4", oMissing);
m_objRange.Value = this.ownerName.Text;
//共同所有的机动车变更所有人
m_objRange = m_objSheet.get_Range("D5", oMissing);
m_objRange.Value = this.owner.Text;
// 住所在车辆管理所辖区内迁移
m_objRange = m_objSheet.get_Range("D6", oMissing);
m_objRange.Value = this.newAddress.Text;
// 邮寄地址
m_objRange = m_objSheet.get_Range("D7", oMissing);
m_objRange.Value = " " + this.postAddress.Text;
// 邮政编码
m_objRange = m_objSheet.get_Range("D8", oMissing);
m_objRange.Value = " " + this.postcode.Text;
// 电子信箱
m_objRange = m_objSheet.get_Range("D9", oMissing);
m_objRange.Value = " " + this.email.Text;
// 固定电话
m_objRange = m_objSheet.get_Range("H8", oMissing);
m_objRange.Value = " " + this.phone.Text;
// 移动电话
m_objRange = m_objSheet.get_Range("H9", oMissing);
m_objRange.Value = " " + this.mobile.Text;
// 省（自治县、直辖市)
m_objRange = m_objSheet.get_Range("D10", oMissing);
m_objRange.Value = " " + this.province.Text;
// 车辆管理所
m_objRange = m_objSheet.get_Range("H10", oMissing);
m_objRange.Value = this.department.Text;
// 变更后的信息
m_objRange = m_objSheet.get_Range("D15", oMissing);
m_objRange.Value = this.information.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("C21", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人邮寄地址
m_objRange = m_objSheet.get_Range("C22", oMissing);
m_objRange.Value = this.agentAddress.Text;
// 代理人邮政编码
m_objRange = m_objSheet.get_Range("C23", oMissing);
m_objRange.Value = this.agentPostcode.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("F23", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 代理人电子信箱
m_objRange = m_objSheet.get_Range("C24", oMissing);
m_objRange.Value = this.agentEmail.Text;
// 经办人电话
m_objRange = m_objSheet.get_Range("C25", oMissing);
m_objRange.Value = this.handlerName.Text;
// 经办人联系电话
m_objRange = m_objSheet.get_Range("F25", oMissing);
m_objRange.Value = this.handlerPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("H20", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";
m_objRange = m_objSheet.get_Range("H25", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " "; ;
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
public void exportarAExcel()
{
try
{
SaveFileDialog fichero = new SaveFileDialog();
fichero.Filter = "Excel (*.xls)|*.xls";
fichero.FileName = "Listado de ventas - " + DateTime.Now.ToString("dd-MM-yyyy");
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo =
(Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
if (dataLista.Rows.Count > 0)
{
//le paso el formato adecuado para los valores decimales pasando desde la fila 2 hasta datalista.rows.count+1 osea hasta el ultimo elemento
hoja_trabajo.Range[hoja_trabajo.Cells[2, 6], hoja_trabajo.Cells[dataLista.Rows.Count + 1, 5]].NumberFormat = "0,00";
}
//Recorremos el DataGridView rellenando la hoja de trabajo
for (int i = 0; i < dataLista.Columns.Count; i++)
{
hoja_trabajo.Cells[1, i + 1] = dataLista.Columns[i].Name;
}
for (int i = 0; i < dataLista.Rows.Count; i++)
{
hoja_trabajo.Cells[i + 2, 1] = dataLista.Rows[i].Cells["codigo"].Value.ToString();
hoja_trabajo.Cells[i + 2, 2] = dataLista.Rows[i].Cells["razon_social"].Value.ToString();
hoja_trabajo.Cells[i + 2, 3] = dataLista.Rows[i].Cells["fecha"].Value.ToString();
hoja_trabajo.Cells[i + 2, 4] = dataLista.Rows[i].Cells["tipo_comprobante"].Value.ToString();
hoja_trabajo.Cells[i + 2, 5] = dataLista.Rows[i].Cells["total"].Value;
hoja_trabajo.Cells[i + 2, 6] = dataLista.Rows[i].Cells["estado"].Value.ToString();
hoja_trabajo.Cells[i + 2, 9] = dataLista.Rows[i].Cells["cuit"].Value.ToString();
hoja_trabajo.Cells[i + 2, 10] = dataLista.Rows[i].Cells["Nrocomprobante"].Value.ToString();
hoja_trabajo.Cells[i + 2, 11] = dataLista.Rows[i].Cells["Letra"].Value.ToString();
hoja_trabajo.Cells[i + 2, 12] = dataLista.Rows[i].Cells["Neto21"].Value.ToString();
hoja_trabajo.Cells[i + 2, 13] = dataLista.Rows[i].Cells["Totaliva21"].Value.ToString();
hoja_trabajo.Cells[i + 2, 14] = dataLista.Rows[i].Cells["Total_Neto105"].Value.ToString();
hoja_trabajo.Cells[i + 2, 15] = dataLista.Rows[i].Cells["Totaliva105"].Value.ToString();
hoja_trabajo.Cells[i + 2, 16] = dataLista.Rows[i].Cells["CAE"].Value.ToString();
hoja_trabajo.Cells[i + 2, 17] = dataLista.Rows[i].Cells["CAE_Fechavencimiento"].Value.ToString();
}
//ajustar el tamaño de las celdas deacuerdo al tamaño de las columnas agregadas
hoja_trabajo.Cells.Columns.AutoFit();
//guardo el archivo con la ruta del archivo
libros_trabajo.SaveAs(fichero.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
if (MessageBox.Show("Desea abrir el Excel ?", "Abrir Excel"
, MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
Process.Start(fichero.FileName);
}
}
}
catch (Exception ex)
{
UtilityFrm.mensajeError("Error: " + ex.Message);
}
}
static void Main(string[] args)
{
//add reference to Microsoft Office Interop Excell dll
string excelFileName = "Planners.xlsx";
string soureFilePath = AppDomain.CurrentDomain.BaseDirectory + excelFileName;
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + excelFileName))
{
Microsoft.Office.Interop.Excel.Application xlApplication = new Microsoft.Office.Interop.Excel.Application();
// xlApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbook workbook = xlApplication.Workbooks.Open(soureFilePath);
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in workbook.Worksheets)
{
worksheet.Activate();
//workbook.SaveAs(AppDomain.CurrentDomain.BaseDirectory + worksheet.Name + ".txt", Microsoft.Office.Interop.Excel.XlFileFormat.xlCurrentPlatformText);
workbook.SaveAs(AppDomain.CurrentDomain.BaseDirectory + "Planners.txt", Microsoft.Office.Interop.Excel.XlFileFormat.xlCurrentPlatformText);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
}//End foreach worksheet
//Close and quit workbook and xlApplication
workbook.Close();
xlApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApplication);
workbook = null;
xlApplication = null;
//Instantiate list from DataValues.cs
List <DataValues> dataValues = new List <DataValues>();
//File name
string fileName = "Planners.txt";
//Check if 'Planners.txt' exists
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + fileName))
{
//Generate list from reading file
List <string> lines = File.ReadAllLines(AppDomain.CurrentDomain.BaseDirectory + fileName).ToList();
//Loop through each line and set variables
foreach (string line in lines)
{
string[] values = line.Split('\t'); //Tab delimited
char[] charsToTrim = { '"' }; //Trim double quotes
//Initialize variables
string vendorPartner = values[1];
string contactFirstName;
string contactLastName;
string email;
string advertisingFund;
string baseMembership;
string proFixedCircular;
string proCustomizableCircular;
string proBOM;
string farmFixedCircular;
string farmCustomCircular;
string farmVOM;
string gardenMasterNationalCircular;
string gardenMasterVOM;
string proMonthlyBuys;
string protradeAds;
string proAchieversClub;
string totalBudget = values[26];
//string notes = values[27];
string notes = "";
//Logic for setting variables if data produces null or empty values
if (values[9] == "")
{
email = "NO_DATA";
}
else
{
email = values[9];
}
if (values[1] == "")
{
vendorPartner = "NO_DATA";
email = "NO_DATA";
}
else
{
vendorPartner = values[1];
}
if (totalBudget == "0")
{
email = "NO_DATA";
totalBudget = "NO_DATA";
}
if (values[7] == "")
{
contactFirstName = "NO_DATA";
email = "NO_DATA";
}
else
{
contactFirstName = values[7];
}
if (values[8] == "")
{
contactLastName = "NO_DATA";
email = "NO_DATA";
}
else
{
contactLastName = values[8];
}
if (values[10] == "")
{
advertisingFund = "0";
}
else
{
advertisingFund = values[10];
}
if (values[12] == "")
{
baseMembership = "0";
}
else
{
baseMembership = values[12];
}
if (values[13] == "")
{
proFixedCircular = "0";
}
else
{
proFixedCircular = values[13];
}
if (values[14] == "")
{
proCustomizableCircular = "0";
}
else
{
proCustomizableCircular = values[14];
}
if (values[15] == "")
{
proBOM = "0";
}
else
{
proBOM = values[15];
}
if (values[19] == "")
{
farmFixedCircular = "0";
}
else
{
farmFixedCircular = values[19];
}
if (values[20] == "")
{
farmCustomCircular = "0";
}
else
{
farmCustomCircular = values[20];
}
if (values[21] == "")
{
farmVOM = "0";
}
else
{
farmVOM = values[21];
}
if (values[22] == "")
{
gardenMasterNationalCircular = "0";
}
else
{
gardenMasterNationalCircular = values[22];
}
if (values[23] == "")
{
gardenMasterVOM = "0";
}
else
{
gardenMasterVOM = values[23];
}
if (values[11] == "")
{
proMonthlyBuys = "0";
}
else
{
proMonthlyBuys = values[11];
}
if (values[17] == "")
{
protradeAds = "0";
}
else
{
protradeAds = values[17];
}
if (values[16] == "")
{
proAchieversClub = "0";
}
else
{
proAchieversClub = values[16];
}
//Trim double quotes and commas from data
string trimmedAdvertisingFund = advertisingFund.Trim(charsToTrim).Replace(",", "");
string trimmedBaseMembership = baseMembership.Trim(charsToTrim).Replace(",", "");
string trimmedProFixedCircular = proFixedCircular.Trim(charsToTrim).Replace(",", "");
string trimmedProCustomizableCircular = proCustomizableCircular.Trim(charsToTrim).Replace(",", "");
string trimmedProBOM = proBOM.Trim(charsToTrim).Replace(",", "");
string trimmedFarmFixedCircular = farmFixedCircular.Trim(charsToTrim).Replace(",", "");
string trimmedFarmCustomCircular = farmCustomCircular.Trim(charsToTrim).Replace(",", "");
string trimmedFarmVOM = farmVOM.Trim(charsToTrim).Replace(",", "");
string trimmedGardenMasterNationalCircular = gardenMasterNationalCircular.Trim(charsToTrim).Replace(",", "");
string trimmedGardenMasterVOM = gardenMasterVOM.Trim(charsToTrim).Replace(",", "");
string trimmedProMonthlyBuys = proMonthlyBuys.Trim(charsToTrim).Replace(",", "");
string trimmedProTradeAds = protradeAds.Trim(charsToTrim).Replace(",", "");
string trimmedProAchieversClub = proAchieversClub.Trim(charsToTrim).Replace(",", "");
string trimmedNotes = notes.Trim(charsToTrim).Replace(",", "");
string trimmedTotalBudget = totalBudget.Trim(charsToTrim).Replace(",", "");
string trimmedVendorPartner = vendorPartner.Trim(charsToTrim).Replace(",", "");
string trimmedEmail = email.Trim(charsToTrim).Replace(",", "");
string trimmedContactFirstName = contactFirstName.Trim(charsToTrim).Replace(",", "");
string trimmedContactLastName = contactLastName.Trim(charsToTrim).Replace(",", "");
//Logic to bypass header
if (
values[0] != "ContactDisplayName" &&
trimmedAdvertisingFund != "AdvertisingFund" &&
trimmedBaseMembership != "BaseMembershipProgram" &&
trimmedProFixedCircular != "PROHardwareFixedCircular" &&
trimmedProCustomizableCircular != "PROHardwareCustomizableCircular" &&
trimmedProBOM != "PROHardwareBargainoftheMonth" &&
trimmedFarmFixedCircular != "FARMMARTFixedCircular" &&
trimmedFarmCustomCircular != "FARMMARTCustomizableCircular" &&
trimmedFarmVOM != "FARMMARTValueoftheMonth" &&
trimmedGardenMasterNationalCircular != "GardenMasterNationalCircular" &&
trimmedGardenMasterVOM != "GardenMasterValueoftheMonth" &&
trimmedProMonthlyBuys != "PROMonthlyBuys" &&
trimmedProTradeAds != "PROHardwareTradeAd" &&
trimmedProAchieversClub != "PROHardwareAchieversClub"
)
{
//Convert string variables to int for addition of data values
//Variables for Base Membership
int trimmedAdvertisingFundInt = int.Parse(trimmedAdvertisingFund);
int trimmedBaseMembershipInt = int.Parse(trimmedBaseMembership);
int finalBaseMembershipInt = trimmedAdvertisingFundInt + trimmedBaseMembershipInt;
string finalBaseMembership = finalBaseMembershipInt.ToString();
//Variables for PRO Print Circulars
int trimmedProFixedCircularInt = int.Parse(trimmedProFixedCircular);
int trimmedProCustomizableCircularInt = int.Parse(trimmedProCustomizableCircular);
int trimmedProBOMInt = int.Parse(trimmedProBOM);
int trimmedFarmFixedCircularInt = int.Parse(trimmedFarmFixedCircular);
int trimmedFarmCustomCircularInt = int.Parse(trimmedFarmCustomCircular);
int trimmedFarmVOMInt = int.Parse(trimmedFarmVOM);
int trimmedGardenMasterNationalCircularInt = int.Parse(trimmedGardenMasterNationalCircular);
int trimmedGardenMasterVOMInt = int.Parse(trimmedGardenMasterVOM);
int finalPrintCircularInt =
trimmedProFixedCircularInt +
trimmedProCustomizableCircularInt +
trimmedProBOMInt +
trimmedFarmFixedCircularInt +
trimmedFarmCustomCircularInt +
trimmedFarmVOMInt +
trimmedGardenMasterNationalCircularInt +
trimmedGardenMasterVOMInt
;
string finalPrintCircular = finalPrintCircularInt.ToString();
//Variables for Monthly Buys
int trimmedProMonthlyBuysInt = int.Parse(trimmedProMonthlyBuys);
string finalProMonthlyBuys = trimmedProMonthlyBuysInt.ToString();
//Variables for Trade Advertisements
int trimmedProTradeAdsInt = int.Parse(trimmedProTradeAds);
string finalProtradeAds = trimmedProTradeAdsInt.ToString();
//Variables for Achievers Club
int trimmedProAchieversClubInt = int.Parse(trimmedProAchieversClub);
string finalProAchieversClub = trimmedProAchieversClubInt.ToString();
//Get the total
int trimmedTotalInt = finalBaseMembershipInt + finalPrintCircularInt + trimmedProMonthlyBuysInt + trimmedProTradeAdsInt + trimmedProAchieversClubInt;
string finalTrimmedTotal = trimmedTotalInt.ToString();
if (finalTrimmedTotal == "0")
{
finalTrimmedTotal = "NO_DATA";
}
//Initialize DataValues list
DataValues newDataValues = new DataValues();
//Get data for DataValues list
newDataValues.Email = trimmedEmail;
newDataValues.VendorPartner = trimmedVendorPartner;
newDataValues.BaseMembership = finalBaseMembership;
newDataValues.PrintCircular = finalPrintCircular;
newDataValues.ProMonthlyBuys = finalProMonthlyBuys;
newDataValues.TradeAdvertisments = finalProtradeAds;
newDataValues.AchieversClubSponsorship = finalProAchieversClub;
newDataValues.TotalPromotionalBudget = finalTrimmedTotal;
newDataValues.TotalPromotionalBudgetSmall = finalTrimmedTotal;
newDataValues.Notes = trimmedNotes;
newDataValues.ContactName = trimmedContactFirstName + " " + trimmedContactLastName;
//Set data for DataValues list
dataValues.Add(newDataValues);
}
}
//Instantiate new list for csv file
List <string> output = new List <string>();
//Instantiate new list for errors
List <string> errors = new List <string>();
//Set error file status
bool errorFileStatus = false;
//Initialize header for error file
errors.Add("Name,Email,VendorPartner,BaseMembership,PrintCircular,ProMonthlyBuys,TradeAdvertisments,AchieversClubSponsorship,TotalPromotionalBudget,Notes,TotalPromotionalBudgetSmall,ContactName");
//Initialize header for csv
output.Add("Sender::Name,Sender::Email,Sender::VendorPartner,Sender::BaseMembership,Sender::PrintCircular,Sender::ProMonthlyBuys,Sender::TradeAdvertisments,Sender::AchieversClubSponsorship,Sender::TotalPromotionalBudget,Sender::Notes,Sender::TotalPromotionalBudgetSmall,Sender::ContactName");
//Loop and add data for each line of new csv file
foreach (var dataValue in dataValues)
{ //If file has missing data, remove from CSV and create error file and set error file status to true.
if (dataValue.ContactName == "NO_DATA NO_DATA" || dataValue.Email == "NO_DATA" || dataValue.TotalPromotionalBudget == "NO_DATA" || dataValue.TotalPromotionalBudgetSmall == "NO_DATA")
{
output.Remove($"{ dataValue.ContactName},{ dataValue.Email},{ dataValue.VendorPartner},{ dataValue.BaseMembership},{ dataValue.PrintCircular},{ dataValue.ProMonthlyBuys},{ dataValue.TradeAdvertisments},{ dataValue.AchieversClubSponsorship},{ dataValue.TotalPromotionalBudget},{ dataValue.Notes},{ dataValue.TotalPromotionalBudgetSmall},{dataValue.ContactName}");
errors.Add($"{ dataValue.ContactName},{ dataValue.Email},{ dataValue.VendorPartner},{ dataValue.BaseMembership},{ dataValue.PrintCircular},{ dataValue.ProMonthlyBuys},{ dataValue.TradeAdvertisments},{ dataValue.AchieversClubSponsorship},{ dataValue.TotalPromotionalBudget},{ dataValue.Notes},{ dataValue.TotalPromotionalBudgetSmall},{dataValue.ContactName}");
errorFileStatus = true;
}
else
{ //If file has no errors, create CSV
output.Add($"{ dataValue.ContactName},{ dataValue.Email},{ dataValue.VendorPartner},{ dataValue.BaseMembership},{ dataValue.PrintCircular},{ dataValue.ProMonthlyBuys},{ dataValue.TradeAdvertisments},{ dataValue.AchieversClubSponsorship},{ dataValue.TotalPromotionalBudget},{ dataValue.Notes},{ dataValue.TotalPromotionalBudgetSmall},{dataValue.ContactName}");
}
}
//Create unique file name using current date time down to the minute
DateTime currentTime = DateTime.Now;
string now = currentTime.ToString("MM_dd_yyyy_HHmmss");
string docusignFileName = "Docusign_Upload";
string dotCsv = ".csv";
string docusignErrorFileName = "Docusign_Errors";
//If error file status is true, write error file and write cleaned CSV file.
if (errorFileStatus == true)
{
File.WriteAllLines(AppDomain.CurrentDomain.BaseDirectory + docusignErrorFileName + "_" + now + dotCsv, errors);
File.WriteAllLines(AppDomain.CurrentDomain.BaseDirectory + docusignFileName + "_" + now + dotCsv, output);
Console.WriteLine("Docusign CSV File created successfully! ");
Console.WriteLine();
Console.WriteLine("Your formatted Docusign CSV file is in this location:");
Console.WriteLine(Directory.GetCurrentDirectory());
Console.WriteLine();
Console.WriteLine("***Errors detected*** \r\n See Docusign_Error file for details.");
Console.WriteLine();
//File.Delete(fileName);
Console.WriteLine("You are now being directed to Docusign.com");
File.Delete(fileName);
System.Diagnostics.Process.Start("https://account.docusign.com/");
Thread.Sleep(3000);
System.Environment.Exit(0);
//Console.ReadLine();
}
else
{ //If no errors, write CSV file.
File.WriteAllLines(AppDomain.CurrentDomain.BaseDirectory + docusignFileName + "_" + now + dotCsv, output);
Console.WriteLine("Docusign CSV File created successfully! ");
Console.WriteLine();
Console.WriteLine("Your formatted Docusign CSV file is in this location:");
Console.WriteLine(Directory.GetCurrentDirectory());
Console.WriteLine();
//File.Delete(fileName);
Console.WriteLine("You are now being directed to Docusign.com");
File.Delete(fileName);
System.Diagnostics.Process.Start("https://account.docusign.com/");
Thread.Sleep(3000);
System.Environment.Exit(0);
//Console.ReadLine();
}
}//End if for fileName
else
{
//If Planners.txt does not exist, raise error.
Console.WriteLine("Excel file 'Planners.txt' does not exist in the current directory!");
Console.ReadLine();
}
}
else
{ //If Planners.txt does not exist, raise error.
Console.WriteLine("Excel file 'Planners.xlsx' does not exist in the current directory!");
Console.ReadLine();
}
//End old docusignplanners code/////////////////////////////////
} //End Main()
private static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl)
{
//System.Data.DataTable dt = new System.Data.DataTable();
if (dt == null)
{
return;
}
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel.Series oSeries;
//Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel._Chart oChart;
//String sMsg;
//int iNumQtrs;
GC.Collect();//系统的垃圾回收
//string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
//Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);
if (ep == null)
{
MsgBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
ep.Visible = true;
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
ws.UsedRange.Select();
ws.UsedRange.Copy(System.Type.Missing);
// wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[rowIndex, colIndex++] = col.ColumnName;
}
for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
}
oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
int rcount = dt.Rows.Count;
int ccount = dt.Columns.Count;
oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true,
charttitle, Missing.Value, Missing.Value, Missing.Value);
// oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);
//string str = String.Empty;
//for (int I = 1; I < 15; I++)
//{
// str += I.ToString() + "\t";
//}
//try { oSeries.XValues = str; }
//catch { }
// oSeries.HasDataLabels = true;
// string charttitle ,bool tableflag ,bool bl)
if (tableflag == true)
{
oChart.HasDataTable = true;
}
else
{
oChart.HasDataTable = false;
}
oChart.PlotVisibleOnly = false;
//
Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//axis.HasTitle = true;
//axis.AxisTitle.Text = "Sales Figures";
// axis.HasMinorGridlines=true;
Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//ax.HasTitle = true;
//ax.AxisTitle.Text = "Sales Figures";
ax.HasMajorGridlines = true;
// string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
// ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
wb.Saved = true;
wb.SaveCopyAs(filename);
}
catch (Exception ex)
{
MsgBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
ep.Quit();
GC.Collect();//强行销毁
}
//http://csharp.net-informations.com/excel/csharp-read-excel.htm
//http://csharp.net-informations.com/excel/csharp-create-excel.htm
// Just take the predicted class + its probability and save it in a new file
private void btnSingleFile_Click(object sender, EventArgs e)
{
// int[] rowsToBeProcessed = new int[] { 3,29, 34, 47, 59, 71, 98, 109, 128, 129, 143, 158, 174, 195, 212, 239, 245, 251, 260, 271, 284, 294, 302, 333, 341, 347, 399, 402, 444,473, 478, 496, 502, 526, 535, 544, 555, 571, 581, 585, 601, 627, 642,660, 676, 681, 700, 763,791, 811,945 };
int noOfSheets = 101;// 31
int[] rowsToBeProcessed = new int[] { };
// process user input entered rows
if (txtInstanceNo.Text != string.Empty)
{
rowsToBeProcessed = Array.ConvertAll(txtInstanceNo.Text.Split(','), s => int.Parse(s));
}
else
{
// process explicitly added row numbers
rowsToBeProcessed = new int[] { 682 };
}
foreach (int row in rowsToBeProcessed)
{
string writeFileLocation = txtSingleFileWriteLocation.Text + "\\" + row + ".xls";
// Reading Excel Sheet
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook xlReadWorkBook = xlApp.Workbooks.Open(txtSingleFileReadLocation.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet xlReadWorkSheet = null;
//Writing Excel Sheet
Microsoft.Office.Interop.Excel.Workbook xlWriteWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWriteWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWriteWorkBook = xlApp.Workbooks.Add(misValue);
xlWriteWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWriteWorkBook.Worksheets.get_Item(1);
xlWriteWorkSheet.Cells[1, 1] = "Class0";
xlWriteWorkSheet.Cells[1, 2] = "Class1";
xlWriteWorkSheet.Cells[1, 3] = "Class2";
xlWriteWorkSheet.Cells[1, 4] = "Class3";
xlWriteWorkSheet.Cells[1, 5] = "Class4";
xlWriteWorkSheet.Cells[1, 6] = "Class5";
xlWriteWorkSheet.Cells[1, 7] = "Class6";
xlWriteWorkSheet.Cells[1, 8] = "Class7";
xlWriteWorkSheet.Cells[1, 9] = "Class8";
xlWriteWorkSheet.Cells[1, 10] = "Class9";
int writeRowCount = 2;
string predictedClass;
string probabilityOfPredictedClass;
int rw = 0;
int cl = 0;
Microsoft.Office.Interop.Excel.Range range;
int rowToBeProcessed = row;// = Convert.ToInt16(txtInstanceNo.Text);
// count the nubmer of times specific class was predicted
int class0Count = 0;
int class1Count = 0;
int class2Count = 0;
int class3Count = 0;
int class4Count = 0;
int class5Count = 0;
int class6Count = 0;
int class7Count = 0;
int class8Count = 0;
int class9Count = 0;
//for (int i = 2; i <= 101; i++)
for (int i = 2; i <= noOfSheets; i++)
{
xlReadWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlReadWorkBook.Worksheets.get_Item(i);
range = xlReadWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
predictedClass = (string)(range.Cells[rowToBeProcessed, 3] as Microsoft.Office.Interop.Excel.Range).Value2;
probabilityOfPredictedClass = (string)(range.Cells[rowToBeProcessed, 5] as Microsoft.Office.Interop.Excel.Range).Value2;
//Write in Excel Sheet, this library has start index from 1 (so we have to add 1)
xlWriteWorkSheet.Cells[writeRowCount, Convert.ToInt16(predictedClass) + 1] = probabilityOfPredictedClass;
writeRowCount = writeRowCount + 1;
#region
if (predictedClass == "0")
{
class0Count = class0Count + 1;
}
else if (predictedClass == "1")
{
class1Count = class1Count + 1;
}
else if (predictedClass == "2")
{
class2Count = class2Count + 1;
}
else if (predictedClass == "3")
{
class3Count = class3Count + 1;
}
else if (predictedClass == "4")
{
class4Count = class4Count + 1;
}
else if (predictedClass == "5")
{
class5Count = class5Count + 1;
}
else if (predictedClass == "6")
{
class6Count = class6Count + 1;
}
else if (predictedClass == "7")
{
class7Count = class7Count + 1;
}
else if (predictedClass == "8")
{
class8Count = class8Count + 1;
}
else
{
class9Count = class9Count + 1;
}
#endregion
}
// Write the count (number of times the corresponsing class has been predicted) for each class
xlWriteWorkSheet.Cells[103, 1] = class0Count;
xlWriteWorkSheet.Cells[103, 2] = class1Count;
xlWriteWorkSheet.Cells[103, 3] = class2Count;
xlWriteWorkSheet.Cells[103, 4] = class3Count;
xlWriteWorkSheet.Cells[103, 5] = class4Count;
xlWriteWorkSheet.Cells[103, 6] = class5Count;
xlWriteWorkSheet.Cells[103, 7] = class6Count;
xlWriteWorkSheet.Cells[103, 8] = class7Count;
xlWriteWorkSheet.Cells[103, 9] = class8Count;
xlWriteWorkSheet.Cells[103, 10] = class9Count;
//xlWriteWorkBook.SaveAs(@"C:\Users\faqeerrehman\MSU\Research\CancerPrediction\ScientificSWTesting\Pilot1\P1B2Tests\Results\PerClassOutputs\20Epochs\ExcelProcesser\ProcessedResults\MR52\MRDecision\Class9\" + row + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
//xlWriteWorkBook.SaveAs(@"C:\Users\faqeerrehman\MSU\Research\CancerPrediction\ScientificSWTesting\Pilot1\P1B2Tests\Results\PerClassOutputs\20Epochs\ExcelProcesser\ProcessedResults_Clem\Origional\Origional_20.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWriteWorkBook.SaveAs(writeFileLocation, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWriteWorkBook.Close(true, misValue, misValue);
xlReadWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlReadWorkSheet);
Marshal.ReleaseComObject(xlReadWorkBook);
Marshal.ReleaseComObject(xlWriteWorkSheet);
Marshal.ReleaseComObject(xlWriteWorkBook);
Marshal.ReleaseComObject(xlApp);
}
MessageBox.Show("Process Completed Successfully.");
}
//打印功能
public int print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\18 QB-PA-PP-03-R02A 产品热合强度检验记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
my = printValue(my, wb);
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
//log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒 打印文档\n");
dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
}
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
my = null;
}
return(pageCount);
}
}
public int print(bool b)
{
int label_打印成功 = 1;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
string dir = System.IO.Directory.GetCurrentDirectory();
dir += "./../../xls/CSBag/SOP-MFG-110-R01A 清场记录.xlsx";
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my);
//"生产指令-步骤序号- 表序号 /&P"
int sheetnum;
SqlDataAdapter da = new SqlDataAdapter("select ID from 清场记录" + " where 生产指令ID=" + ID.ToString(), mySystem.Parameter.conn);
DataTable dt = new DataTable("temp");
da.Fill(dt);
List <Int32> sheetList = new List <Int32>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
}
sheetnum = sheetList.IndexOf(Convert.ToInt32(dtOuter.Rows[0]["ID"])) + 1;
my.PageSetup.RightFooter = CODE + "-" + sheetnum.ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{
label_打印成功 = 0;
}
if (1 == label_打印成功)
{
string str角色;
if (_userState == 0)
{
str角色 = "操作员";
}
else if (_userState == 1)
{
str角色 = "审核员";
}
else
{
str角色 = "管理员";
}
string log = "\n=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + str角色 + ":" + mySystem.Parameter.userName + " 完成打印\n";
dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
}
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
return(pageCount);
}
}
/// 导出为Excel格式文件
/// </summary>
/// <param name="counttable">统计数据</param>
/// <param name="dt">作为数据源的DataTable</param>
/// <param name="saveFile">带路径的保存文件名</param>
/// <param name="title">一个Excel sheet的标题</param>
void exportExcel(DataTable counttable, DataTable dt, string saveFile, string title, string senderdoctor, string recieverdoctor, DateTime handovertime)
{
Microsoft.Office.Interop.Excel.Application rptExcel = new Microsoft.Office.Interop.Excel.Application();
if (rptExcel == null)
{
MessageBox.Show("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel", "系统提示");
return;
}
int rowCount = dt.Rows.Count; //行数
int columnCount = dt.Columns.Count; //列数
float percent = 0; //导出进度
//this.Cursor = Cursors.WaitCursor;
//保存文化环境
System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbook workbook = rptExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
worksheet.Name = "报表";//一个sheet的名称
//rptExcel.Visible = true;//打开导出的Excel文件
//填充统计数据
int countrows = 1;
int countcols = counttable.Rows.Count * 2;
object[,] objcountdata = new object[countrows, countcols];
for (int i = 0; i < countcols; i++)
{
int j = i / 2;
int k = i % 2;
objcountdata[0, i] = counttable.Rows[j][k];
}
Microsoft.Office.Interop.Excel.Range countrange = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]);
countrange.Value2 = objcountdata;
worksheet.Cells[3, 1] = title;//表标题
//填充列标题
for (int i = 0; i < columnCount; i++)
{
worksheet.Cells[4, i + 1] = dt.Columns[i].ColumnName;
}
//创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
object[,] objData = new object[rowCount, columnCount];
//填充内容到对象数组
for (int r = 0; r < rowCount; r++)
{
for (int col = 0; col < columnCount; col++)
{
objData[r, col] = dt.Rows[r][col].ToString();
}
percent = ((float)(r + 1) * 100) / rowCount;
//this.panelProgress.Visible = true;//显示进度条
//this.lblPercents.Text=percent.ToString("n") + "%";
//this.progressBar1.Value = Convert.ToInt32(percent);
System.Windows.Forms.Application.DoEvents();
}
//将对象数组的值赋给Excel对象
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowCount + 4, columnCount]);
//range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.NumberFormat = "@";//设置数字文本格式
range.Value2 = objData;
string handinfo = senderdoctor + " " + recieverdoctor + " " + handovertime.ToString("yyyy-MM-dd HH:mm");
worksheet.Cells[5 + rowCount, 1] = handinfo;
//设置格式
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]).RowHeight = 38;
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]).Font.Bold = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Name = "黑体";
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).MergeCells = true;//合并单元格
worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify;
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).RowHeight = 38;
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).RowHeight = 38;
worksheet.get_Range(worksheet.Cells[5 + rowCount, 1], worksheet.Cells[5 + rowCount, columnCount]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, columnCount]).Font.Size = 16;
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, countcols]).Borders.LineStyle = 1; //设置边框
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowCount + 5, columnCount]).Borders.LineStyle = 1; //设置边框
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 5, columnCount]).Columns.AutoFit(); //设置单元格宽度为自适应
//恢复文化环境
System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
try
{
//rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里，如果不用SaveFileDialog就可用这种方法
workbook.Saved = true;
workbook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
//this.Cursor = Cursors.Default;
//this.panelProgress.Visible = false;//隐藏进度条
MessageBox.Show("导出成功！");
}
catch (Exception ex)
{
MessageBox.Show("导出文件出错，文件可能正被打开，具体原因：" + ex.Message, "出错信息");
}
finally
{
dt.Dispose();
rptExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(rptExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
GC.Collect();
KillAllExcel();
}
}
public bool ExportarTodo(DataGridView grd)
{
try
{
bool okFile = false;
SaveFileDialog fichero = new SaveFileDialog();
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo =
(Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
int ColumnIndex = 0;
foreach (DataGridViewColumn col in grd.Columns)
{
ColumnIndex++;
hoja_trabajo.Cells[1, ColumnIndex] = col.HeaderText;
}
string LetraColumna = string.Empty;
switch (ColumnIndex)
{
case 1:
LetraColumna = "A";
break;
case 2:
LetraColumna = "B";
break;
case 3:
LetraColumna = "C";
break;
case 4:
LetraColumna = "D";
break;
case 5:
LetraColumna = "E";
break;
case 6:
LetraColumna = "F";
break;
case 7:
LetraColumna = "G";
break;
case 8:
LetraColumna = "H";
break;
case 9:
LetraColumna = "I";
break;
case 10:
LetraColumna = "J";
break;
case 11:
LetraColumna = "K";
break;
case 12:
LetraColumna = "L";
break;
case 13:
LetraColumna = "M";
break;
case 14:
LetraColumna = "N";
break;
case 15:
LetraColumna = "O";
break;
case 16:
LetraColumna = "Q";
break;
case 17:
LetraColumna = "P";
break;
case 18:
LetraColumna = "R";
break;
case 19:
LetraColumna = "S";
break;
case 20:
LetraColumna = "T";
break;
case 21:
LetraColumna = "U";
break;
case 22:
LetraColumna = "V";
break;
case 23:
LetraColumna = "W";
break;
}
Microsoft.Office.Interop.Excel.Range rango = hoja_trabajo.get_Range("A1", LetraColumna + "1");
rango.Font.Bold = true;
rango.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rango.Interior.ColorIndex = 15;
int rowIndex = 1;
foreach (DataGridViewRow row in grd.Rows)
{
int Column = 0;
rowIndex++;
for (int j = 0; j < row.Cells.Count; j++)
{
if (grd.Columns[Column].DefaultCellStyle.Format == "C" || grd.Columns[Column].DefaultCellStyle.Format == "C2")
{
hoja_trabajo.Cells[rowIndex, Column + 1].NumberFormat = "$#,##0.00";
}
else if (grd.Columns[Column].DefaultCellStyle.Format == "P0" || grd.Columns[Column].DefaultCellStyle.Format == "P2")
{
hoja_trabajo.Cells[rowIndex, j + 1].NumberFormat = "0.00%";
}
else
{
hoja_trabajo.Cells[rowIndex, Column + 1].NumberFormat = "@";
}
if (row.Cells[j].Style.BackColor.Name == "0")
{
hoja_trabajo.Cells[rowIndex, Column + 1].Interior.Color = Color.White;
}
else
{
hoja_trabajo.Cells[rowIndex, Column + 1].Interior.Color = row.Cells[j].Style.BackColor;//ColorTranslator.ToOle(row.Cells[rowIndex - 1].Style.BackColor);
}
if (row.Cells[j].Style.BackColor == Color.Red)
{
hoja_trabajo.Cells[rowIndex, Column + 1].Font.Color = Color.White;
}
hoja_trabajo.Cells[rowIndex, Column + 1] = row.Cells[j].Value;
Column++;
}
}
rango.RowHeight = 20;
hoja_trabajo.Columns.AutoFit();
libros_trabajo.SaveAs(fichero.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
okFile = true;
return(okFile);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Ocurrió un error inesperado: " + ex.Message, "HalcoNET", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
public bool TableToExcel(string strfilename)
{
try
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook book = app.Workbooks.Open(strfilename, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet wrksheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets["DeSo"];
int h = 1;
wrksheet.Cells[2, 10] = "* Thời gian:" + timeFisrt.Text + "/" + dtp_FirstDay.Value.Day + "/" + dtp_FirstDay.Value.Month + "/" + dtp_FirstDay.Value.Year + " đến " + timeEnd.Text + "/" + dtp_EndDay.Value.Day + "/" + dtp_EndDay.Value.Month + "/" + dtp_EndDay.Value.Year;
for (int i = 0; i < dataGridView1.RowCount; i++)
{
wrksheet.Cells[h + 2, 1] = h;
wrksheet.Cells[h + 2, 2] = dataGridView1.Rows[i].Cells[0].Value + ""; //username
wrksheet.Cells[h + 2, 3] = dataGridView1.Rows[i].Cells[1].Value + ""; //fullname
wrksheet.Cells[h + 2, 4] = dataGridView1.Rows[i].Cells[2].Value + ""; //tong
wrksheet.Cells[h + 2, 5] = dataGridView1.Rows[i].Cells[3].Value + ""; //phieudung
wrksheet.Cells[h + 2, 6] = dataGridView1.Rows[i].Cells[4].Value + ""; //phieusai
wrksheet.Cells[h + 2, 7] = dataGridView1.Rows[i].Cells[5].Value + ""; //thoigian
wrksheet.Cells[h + 2, 8] = dataGridView1.Rows[i].Cells[6].Value + ""; //hieusuat
h++;
}
Microsoft.Office.Interop.Excel.Worksheet wrksheetjp = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets["DeJP"];
int jp = 1;
wrksheetjp.Cells[2, 10] = "* Thời gian:" + timeFisrt.Text + "/" + dtp_FirstDay.Value.Day + "/" + dtp_FirstDay.Value.Month + "/" + dtp_FirstDay.Value.Year + " đến " + timeEnd.Text + "/" + dtp_EndDay.Value.Day + "/" + dtp_EndDay.Value.Month + "/" + dtp_EndDay.Value.Year;
for (int i = 0; i < dataGridView2.RowCount; i++)
{
wrksheetjp.Cells[jp + 2, 1] = jp;
wrksheetjp.Cells[jp + 2, 2] = dataGridView2.Rows[i].Cells[0].Value + ""; //username
wrksheetjp.Cells[jp + 2, 3] = dataGridView2.Rows[i].Cells[1].Value + ""; //fullname
wrksheetjp.Cells[jp + 2, 4] = dataGridView2.Rows[i].Cells[2].Value + ""; //tong
wrksheetjp.Cells[jp + 2, 5] = dataGridView2.Rows[i].Cells[3].Value + ""; //phieudung
wrksheetjp.Cells[jp + 2, 6] = dataGridView2.Rows[i].Cells[4].Value + ""; //phieusai
wrksheetjp.Cells[jp + 2, 7] = dataGridView2.Rows[i].Cells[5].Value + ""; //thoigian
wrksheetjp.Cells[jp + 2, 8] = dataGridView2.Rows[i].Cells[6].Value + ""; //hieusuat
jp++;
}
//copyAlltoClipboard();
//Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)wrksheet.Cells[3, 1];
//CR.Select();
//wrksheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
string savePath;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = @"Save Excel Files";
saveFileDialog1.Filter = @"Excel files (*.xlsx)|*.xlsx";
saveFileDialog1.FileName = "NangSuat_BaoCaoLuong2018_" + dtp_FirstDay.Value.Day + "-" + dtp_EndDay.Value.Day;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
book.SaveCopyAs(saveFileDialog1.FileName);
book.Saved = true;
savePath = Path.GetDirectoryName(saveFileDialog1.FileName);
app.Quit();
}
else
{
MessageBox.Show(@"Error exporting excel!");
return(false);
}
if (savePath != null)
{
Process.Start(savePath);
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
public static string multiSheetExcel(DataSet ds)
{
DataTable table100 = ds.Tables[0];
DataTable table50first = ds.Tables[1];
DataTable table50second = ds.Tables[2];
DataTable table50full = ds.Tables[3];
DataTable table5050 = ds.Tables[4];
DataTable tableCn = ds.Tables[5];
DataTable tableRmb = ds.Tables[6];
string filename = "";
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
for (int i = sheets.Count; i > 1; i--)
{
((Microsoft.Office.Interop.Excel.Worksheet)sheets[i]).Delete();
}
for (int i = 1; i <= 6; i++)
{
workbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
}
Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[1];
sheet1.Name = "100% Normal";
for (int i = 0; i < table100.Columns.Count; i++)
{
sheet1.Cells[1, i + 1] = table100.Columns[i].ColumnName;
}
for (int i = 0; i < table100.Rows.Count; i++)
{
for (int j = 0; j < table100.Columns.Count; j++)
{
sheet1.Cells[i + 2, j + 1] = table100.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[2];
sheet2.Name = "50% MPA 1st 50%";
for (int i = 0; i < table50first.Columns.Count; i++)
{
sheet2.Cells[1, i + 1] = table50first.Columns[i].ColumnName;
}
for (int i = 0; i < table50first.Rows.Count; i++)
{
for (int j = 0; j < table50first.Columns.Count; j++)
{
sheet2.Cells[i + 2, j + 1] = table50first.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet3 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[3];
sheet3.Name = "50% MPA 2nd 50%";
for (int i = 0; i < table50second.Columns.Count; i++)
{
sheet3.Cells[1, i + 1] = table50second.Columns[i].ColumnName;
}
for (int i = 0; i < table50second.Rows.Count; i++)
{
for (int j = 0; j < table50second.Columns.Count; j++)
{
sheet3.Cells[i + 2, j + 1] = table50second.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[4];
//Microsoft.Office.Interop.Excel.Worksheet sheet4 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets.Add(After: excelApp.Sheets[excelApp.Sheets.Count]);
sheet4.Name = "50% MPA 100% 2pcs";
for (int i = 0; i < table50full.Columns.Count; i++)
{
sheet4.Cells[1, i + 1] = table50full.Columns[i].ColumnName;
}
for (int i = 0; i < table50full.Rows.Count; i++)
{
for (int j = 0; j < table50full.Columns.Count; j++)
{
sheet4.Cells[i + 2, j + 1] = table50full.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet5 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[5];
sheet5.Name = "50% MPA 100% 1+1 pcs";
for (int i = 0; i < table5050.Columns.Count; i++)
{
sheet5.Cells[1, i + 1] = table5050.Columns[i].ColumnName;
}
for (int i = 0; i < table5050.Rows.Count; i++)
{
for (int j = 0; j < table5050.Columns.Count; j++)
{
sheet5.Cells[i + 2, j + 1] = table5050.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet6 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[6];
sheet6.Name = "OEM=CN";
for (int i = 0; i < tableCn.Columns.Count; i++)
{
sheet6.Cells[1, i + 1] = tableCn.Columns[i].ColumnName;
}
for (int i = 0; i < tableCn.Rows.Count; i++)
{
for (int j = 0; j < tableCn.Columns.Count; j++)
{
sheet6.Cells[i + 2, j + 1] = tableCn.Rows[i][j].ToString();
}
}
Microsoft.Office.Interop.Excel.Worksheet sheet7 = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Sheets[7];
sheet7.Name = "RMB";
for (int i = 0; i < tableRmb.Columns.Count; i++)
{
sheet7.Cells[1, i + 1] = tableRmb.Columns[i].ColumnName;
}
for (int i = 0; i < tableRmb.Rows.Count; i++)
{
for (int j = 0; j < tableRmb.Columns.Count; j++)
{
sheet7.Cells[i + 2, j + 1] = tableRmb.Rows[i][j].ToString();
}
}
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "xls",
Filter = "EXCEL Files (*.xls)|*.xls",
FilterIndex = 1,
FileName = DateTime.Today.ToString("yyyyMMdd") + "_ReceivedPO"// + year + month + day,
};
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
filename = sfd.FileName;
excelApp.ActiveWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//excelApp.ActiveWorkbook.Saved = true;
//excelApp.AlertBeforeOverwriting = false;
excelApp.Quit();
MessageBox.Show("Record saved to " + filename);
}
catch (COMException ex)
{
MessageBox.Show(ex.Message + ex.StackTrace);
Debug.WriteLine(ex.Message + ex.StackTrace);
}
}
return(filename);
}
private void ExportExcels(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
//saveDialog.ShowDialog();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
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对象，可能您的PC未安装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 < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("数据已保存到文件： " + fileName, "保存成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("已取消保存文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public string ExportExcel(string SQL, string[] StrCloumns, string saveFileName)
{
try
{
DataTable Table0 = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(SQL, TempConn.GetConnStr());
sda.Fill(Table0);
DataSet ds = new DataSet();
ds.Tables.Add(Table0);
ChangeColumnName(ref ds, StrCloumns);
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);
return("");
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
MessageBox.Show("导出成功，默认保存在：我的电脑/文档");
return("成功保存到Excel");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
private void SaveIntervalExcel_Click(object sender, RoutedEventArgs e)
{
if (intervalControl.Open)
{
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Документ Excel(*.xlsx)|*.xlsx";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
string filename = sfd.FileName;
int rows = intervalControl.IntervalGrid.Count();
double[] lBorder = intervalControl.IntervalGrid.Select(p => p.leftBorder).ToArray();
double[] rBorder = intervalControl.IntervalGrid.Select(p => p.rightBorder).ToArray();
double[] frequency = intervalControl.IntervalGrid.Select(p => p.frequency).ToArray();
double[] accumulatedFrequency = intervalControl.IntervalGrid.Select(p => p.accumulatedFrequency).ToArray();
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[1, 1] = "Левая граница";
ExcelWorkSheet.Cells[1, 2] = "Правая граница";
ExcelWorkSheet.Cells[1, 3] = "Частота";
ExcelWorkSheet.Cells[1, 4] = "Накопленная частота";
ExcelWorkSheet.Cells[1, 5] = "Расчеты";
ExcelWorkSheet.Cells[1, 1].Font.Bold = true;
ExcelWorkSheet.Cells[1, 2].Font.Bold = true;
ExcelWorkSheet.Cells[1, 3].Font.Bold = true;
ExcelWorkSheet.Cells[1, 4].Font.Bold = true;
ExcelWorkSheet.Cells[1, 5].Font.Bold = true;
for (int i = 0; i < rows; i++)
{
ExcelWorkSheet.Cells[i + 2, 1] = lBorder[i];
ExcelWorkSheet.Cells[i + 2, 2] = rBorder[i];
ExcelWorkSheet.Cells[i + 2, 3] = frequency[i];
ExcelWorkSheet.Cells[i + 2, 4] = accumulatedFrequency[i];
}
ExcelWorkSheet.Cells[2, 5] = intervalControl.tbAverageValue.Text;
ExcelWorkSheet.Cells[3, 5] = intervalControl.tbMode.Text;
ExcelWorkSheet.Cells[4, 5] = intervalControl.tbMedian.Text;
ExcelWorkSheet.Cells[5, 5] = intervalControl.tbRangeOfVariation.Text;
ExcelWorkSheet.Cells[6, 5] = intervalControl.tbMeanLinearDeviation.Text;
ExcelWorkSheet.Cells[7, 5] = intervalControl.tbDispersion.Text;
ExcelWorkSheet.Cells[8, 5] = intervalControl.tbStandardDeviation.Text;
ExcelWorkSheet.Cells[9, 5] = intervalControl.tbCoefficientVariation.Text;
ExcelWorkSheet.Cells[10, 5] = intervalControl.tbNormalCoefficientAsymmetry.Text;
ExcelWorkSheet.Cells[11, 5] = intervalControl.tbEstimationCoefficientAsymmetry.Text;
ExcelWorkSheet.Cells[12, 5] = intervalControl.tbDegreeAsymmetry.Text;
ExcelWorkSheet.Cells[13, 5] = intervalControl.tbMaterialityAsymmetry.Text;
ExcelWorkSheet.Cells[14, 5] = intervalControl.tbExcess.Text;
ExcelWorkSheet.Cells[15, 5] = intervalControl.tbExcessError.Text;
ExcelWorkSheet.Columns.AutoFit();
ExcelWorkBook.SaveAs(filename);
ExcelWorkBook.Close();
ExcelApp.Quit();
}
else
{
dialogError.IsOpen = true;
}
}
private void repIn_Click(object sender, EventArgs e)
{
DataRow row = layoutView1.GetDataRow(layoutView1.FocusedRowHandle);
PrintDocument thePrint = new PrintDocument();
PrintDialog print = new PrintDialog();
print.Document = thePrint;
thePrint.PrintPage += new PrintPageEventHandler(thePrint_PrintPage);
Path_new = FrameworkParams.TEMP_FOLDER + @"\" + row["TEN_FILE"].ToString();
byte[] a = row["NOI_DUNG"] as byte[];
if (a == null || a.Length == 0) return;
HelpByte.BytesToFile(a, Path_new);
try
{
pic = Image.FromFile(Path_new);
}
catch
{ pic = null; }
read = new System.IO.StreamReader(Path_new, System.Text.Encoding.Default, true);
#region print word file
if (Path.GetExtension(Path_new).ToLower().Equals(".doc")
|| Path.GetExtension(Path_new).ToLower().Equals(".docx"))
{
Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
object fileName = Path_new;
object nullobj = Missing.Value;
wordApp.Visible = true;
Microsoft.Office.Interop.Word.Document doc = wordApp.Documents.Open(
ref fileName, ref nullobj, ref nullobj, ref nullobj,
ref nullobj, ref nullobj, ref nullobj, ref nullobj,
ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj,
ref nullobj, ref nullobj, ref nullobj);
doc.PrintPreview();
}
#endregion
#region print excel file
else if (Path.GetExtension(Path_new).ToLower().Equals(".xls")
|| Path.GetExtension(Path_new).ToLower().Equals(".xlsx"))
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = true;
ExcelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook WBook = ExcelApp.Workbooks.Open(Path_new, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
object obj = true;
WBook.PrintPreview(obj);
WBook.Close(false, Missing.Value, Missing.Value);
ExcelApp.Quit();
}
#endregion
#region print .pdf file
else if (Path.GetExtension(Path_new).ToLower().Equals(".pdf"))
{
System.Diagnostics.Process objProcess = new System.Diagnostics.Process();
objProcess.StartInfo.FileName = Path_new; //file to print
objProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
objProcess.StartInfo.UseShellExecute = true;
objProcess.StartInfo.CreateNoWindow = false;
objProcess.StartInfo.ErrorDialog = false;
objProcess.StartInfo.Verb = "print";
objProcess.Start();
}
#endregion
#region print html file
else if (Path.GetExtension(Path_new).ToLower().Equals(".htm")
|| Path.GetExtension(Path_new).ToLower().Equals(".html"))
{
WebBrowser browser = new WebBrowser();
browser.DocumentText = read.ReadToEnd();
while (browser.ReadyState != WebBrowserReadyState.Complete)
{
Application.DoEvents();
}
browser.Parent = this;
browser.ShowPrintPreviewDialog();
browser.Dispose();
}
#endregion
else if (print.ShowDialog() == DialogResult.OK)
{
PrintPreviewDialog ppd = new PrintPreviewDialog();
ppd.Document = thePrint;
((Form)ppd).WindowState = FormWindowState.Maximized;
ppd.ShowDialog();
//thePrint.Print();
}
}
// Record the probability of predicted class & the expected class
private void btnRecordProbabilities_Click(object sender, EventArgs e)
{
int noOfSheets = 101;
//string writeFileLocation = txtSingleFileWriteLocation.Text;
// Reading Excel Sheet
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlReadWorkBook = xlApp.Workbooks.Open(txtSingleFileReadLocation.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet xlReadWorkSheet = null;
// Origional File
Microsoft.Office.Interop.Excel.Workbook xlReadWorkBookOrigionalFile = xlApp.Workbooks.Open(@"C:\Users\faqeerrehman\MSU\Research\CancerPrediction\ScientificSWTesting\Pilot1\P1B2Tests\Results\PerClassOutputs\20Epochs\Orgional\Origional.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet xlReadWorkSheetOrigionalFile = null;
//Writing Excel Sheet
Microsoft.Office.Interop.Excel.Workbook xlWriteWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWriteWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWriteWorkBook = xlApp.Workbooks.Add(misValue);
xlWriteWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWriteWorkBook.Worksheets.get_Item(1);
xlWriteWorkSheet.Cells[1, 1] = "OrigionalClass";
xlWriteWorkSheet.Cells[1, 2] = "PredictedClass";
xlWriteWorkSheet.Cells[1, 3] = "IsCorrectOutputPredicted";
xlWriteWorkSheet.Cells[1, 4] = "PredictedClassProbability";
xlWriteWorkSheet.Cells[1, 5] = "Expected(Origional)ClassProbability";
int writeRowCount = 2;
string origionalPredictedClass;
string currentMRPredictedClass;
string probabilityOfCurrentMRPredictedClass;
string probabilitiesOfCurrentMRClasses;
int rw = 0;
int cl = 0;
Microsoft.Office.Interop.Excel.Range range;
Microsoft.Office.Interop.Excel.Range rangeOrigionalFile;
double probabilityOfOrigionalPredictedClassInCurrentMR;
string[] predictedProbabilitesArrary;
int rowToBeProcessed = Convert.ToInt16(txtInstanceNo.Text);
for (int i = 2; i <= noOfSheets; i++)
{
xlReadWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlReadWorkBook.Worksheets.get_Item(i);
xlReadWorkSheetOrigionalFile = (Microsoft.Office.Interop.Excel.Worksheet)xlReadWorkBookOrigionalFile.Worksheets.get_Item(i); // Orgional File
rangeOrigionalFile = xlReadWorkSheetOrigionalFile.UsedRange;
range = xlReadWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
origionalPredictedClass = (string)(rangeOrigionalFile.Cells[rowToBeProcessed, 3] as Microsoft.Office.Interop.Excel.Range).Value2; // Origional File Predicted Class
currentMRPredictedClass = (string)(range.Cells[3, 3] as Microsoft.Office.Interop.Excel.Range).Value2;
probabilityOfCurrentMRPredictedClass = (string)(range.Cells[rowToBeProcessed, 5] as Microsoft.Office.Interop.Excel.Range).Value2;
probabilitiesOfCurrentMRClasses = (string)(range.Cells[rowToBeProcessed, 4] as Microsoft.Office.Interop.Excel.Range).Value2;
predictedProbabilitesArrary = probabilitiesOfCurrentMRClasses.Replace("\n", "").Replace("[", "").Replace("]", "").Split(' ');
predictedProbabilitesArrary = predictedProbabilitesArrary.Where(x => !string.IsNullOrEmpty(x)).ToArray();
//Write in Excel Sheet, this library has start index from 1 (so we have to add 1)
probabilityOfOrigionalPredictedClassInCurrentMR = double.Parse(predictedProbabilitesArrary[Convert.ToInt16(origionalPredictedClass)], CultureInfo.InvariantCulture); // Get probability against predicted class in orgional file
xlWriteWorkSheet.Cells[writeRowCount, 1] = origionalPredictedClass;
xlWriteWorkSheet.Cells[writeRowCount, 2] = currentMRPredictedClass;
xlWriteWorkSheet.Cells[writeRowCount, 3] = origionalPredictedClass == currentMRPredictedClass ? 1 : 0;
xlWriteWorkSheet.Cells[writeRowCount, 4] = probabilityOfCurrentMRPredictedClass;
xlWriteWorkSheet.Cells[writeRowCount, 5] = probabilityOfOrigionalPredictedClassInCurrentMR;
writeRowCount = writeRowCount + 1;
}
xlWriteWorkBook.Close(true, misValue, misValue);
xlReadWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlReadWorkSheet);
Marshal.ReleaseComObject(xlReadWorkBook);
Marshal.ReleaseComObject(xlReadWorkSheetOrigionalFile);
Marshal.ReleaseComObject(xlReadWorkBookOrigionalFile);
Marshal.ReleaseComObject(xlWriteWorkSheet);
Marshal.ReleaseComObject(xlWriteWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Process Completed Successfully.");
}
private void WriteToExcel(DataTable dtNewOnly, DataTable dtOldOnly, DataTable dtNewMatched, DataTable dtOldMatched,
DataTable dtNewPerfectMatch, DataTable dtOldPerfectMatch,
List<Column> allColumns, List<Column> ignoredColumns, List<int> matchingColumns,
string report, string portfolio, string fileName, List<List<int>> unmatchedRows,
double tolerance)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
List<int> ignoredColumnNumbers = ignoredColumns.Select(x => x.Id).ToList();
string reportName = Path.GetFileNameWithoutExtension(report);
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
bool fileExists = File.Exists(fileName);
if (fileExists)
{
wb = excel.Workbooks.Open(Filename: fileName);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
}
else
{
wb = excel.Workbooks.Add(missing);
ws = wb.Sheets.Add(After: (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[wb.Worksheets.Count]);
if (wb.Worksheets.Count > 1)
{
for (int i = wb.Worksheets.Count - 1; i > 0; i--)
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Delete();
}
}
}
WriteOverview(ws, fileExists, report, tolerance, ignoredColumns,
dtNewMatched.Rows.Count + dtNewPerfectMatch.Rows.Count,
dtNewMatched.Rows.Count, dtNewOnly.Rows.Count, dtOldOnly.Rows.Count);
ws = fileExists ? wb.Sheets[ws.Index + 1]
: wb.Sheets.Add(After: ws);
WriteDataTableToWorksheet(ws, dtOldOnly, "Old Only", reportName, portfolio, allColumns, matchingColumns,
ignoredColumnNumbers, System.Drawing.Color.FromArgb(0, 255, 0), fileExists);
ws = fileExists ? wb.Sheets[ws.Index + 1]
: wb.Sheets.Add(After: ws);
WriteDataTableToWorksheet(ws, dtNewOnly, "New Only", reportName, portfolio, allColumns, matchingColumns,
ignoredColumnNumbers, System.Drawing.Color.Aqua, fileExists);
ws = fileExists ? wb.Sheets[ws.Index + 1]
: wb.Sheets.Add(After: ws);
WriteDataTablesToWorksheet(ws, dtNewMatched, dtOldMatched, reportName, portfolio, allColumns, matchingColumns,
ignoredColumnNumbers, unmatchedRows, fileExists);
ws = fileExists ? wb.Sheets[ws.Index + 1]
: wb.Sheets.Add(After: ws);
WriteDataTablesToWorksheet(ws, dtNewPerfectMatch, dtOldPerfectMatch, reportName, portfolio, allColumns,
matchingColumns, ignoredColumnNumbers, null, fileExists);
((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.Sheets[1]).Select(missing);
if (fileExists)
{
wb.Save();
}
else
{
wb.SaveAs(
Filename: fileName,
FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
AccessMode: Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange
);
}
wb.Close();
excel.Quit();
}
catch (COMException ex)
{
if (wb != null) wb.Close(SaveChanges: false);
if (excel != null) excel.Quit();
throw new System.ArgumentException("Error accessing Microsoft Excel, make sure it is already installed!\n\n"
+ ex.ToString());
}
catch (Exception ex)
{
if (wb != null) wb.Close(SaveChanges: false);
if (excel != null) excel.Quit();
throw new System.ArgumentException("Error: " + ex.ToString());
}
finally
{
if (excel != null) excel.Quit();
}
}
// Hàm xử lý xuất file Excel sử dụng thư viện Microsoft
private void ExportToExcel(DataGridView dgView)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
// instantiating the excel application class
object misValue = System.Reflection.Missing.Value;
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
currentWorksheet.Columns.ColumnWidth = 18;
if (dgView.Rows.Count > 0)
{
currentWorksheet.Cells[4, 1] = "Mã Stockist";
currentWorksheet.Cells[4, 2] = "____________";
currentWorksheet.Cells[5, 1] = "Địa chỉ Stockist";
currentWorksheet.Cells[5, 2] = "____________";
currentWorksheet.Cells[6, 1] = "Ngày Tạo:";
currentWorksheet.Cells[6, 2] = txtNgayLap.Text;
Microsoft.Office.Interop.Excel.Range mm = currentWorksheet.get_Range("C1", "G1");
mm.MergeCells = true;
mm.Font.Bold = true;
mm.Font.Size = 20;
mm.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
mm.Cells[1, 1] = "ĐƠN ĐẶT HÀNG STOCKIST";
//currentWorksheet.Cells[1, 7] = "ĐƠN ĐẶT HÀNG STOCKIST";
mm = currentWorksheet.get_Range("D3", "G6");
mm.MergeCells = true;
mm.WrapText = true;
mm.Cells[Type.Missing, Type.Missing] = "Công ty TNHH Synergy Việt Nam; Số 27 - tổ 41, Phường Trung Hoà; Quận Cầu Giấy, Hà Nội, Việt Nam; Tel: 84-4 35562535 - Fax: 84-4 35562356";
currentWorksheet.Cells[2, 6] = "VIET NAM";
//currentWorksheet.Cells[3, 7] = "Công ty TNHH Synergy Việt Nam";
//currentWorksheet.Cells[4, 7] = "Số 27 - tổ 41, Phường Trung Hoà";
//currentWorksheet.Cells[5, 7] = "Quận Cầu Giấy, Hà Nội, Việt Nam";
//currentWorksheet.Cells[6, 7] = "Tel: 84-4 35562535 - Fax: 84-4 35562356";
int i = 1;
foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
{
// Excel work sheet indexing starts with 1
currentWorksheet.Cells[7, i] = dgviewColumn.HeaderText.ToString().ToUpper();
++i;
}
Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A7", "G7");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0x000000;
int rowIndex = 0;
for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
{
DataGridViewRow dgRow = dgView.Rows[rowIndex];
for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
{
currentWorksheet.Cells[rowIndex + 8, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
}
}
Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A8", "G" + (rowIndex + 1).ToString());
fullTextRange.WrapText = true; //Tự động xuống dòng khi chữ quá dài
fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //Canh lề trái cho chữ
for (int row = 0; row < 3; row++)
{
string cell1, cell2;
cell1 = "A" + (rowIndex + 8 + row).ToString();
cell2 = "F" + (rowIndex + 8 + row).ToString();
Microsoft.Office.Interop.Excel.Range MergeRowRange = currentWorksheet.get_Range(cell1, cell2);
MergeRowRange.MergeCells = true;
MergeRowRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
currentWorksheet.Cells[rowIndex + 8, 1] = "Tổng tiền trước chiết khấu:";
currentWorksheet.Cells[rowIndex + 8, 7] = CalcMoney(dgView);
currentWorksheet.Cells[rowIndex + 9, 1] = "Chiết khấu:";
currentWorksheet.Cells[rowIndex + 9, 7] = string.Format("{0:#,0.##}", ChietKhau);
currentWorksheet.Cells[rowIndex + 10, 1] = "Tổng tiền thanh toán:";
currentWorksheet.Cells[rowIndex + 10, 7] = string.Format("{0:#,0.##}", CalcMoney(dgView) * (1.0 - (ChietKhau / 10)));
Microsoft.Office.Interop.Excel.Range AllRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 10).ToString());
AllRange.Columns.AutoFit();
AllRange = currentWorksheet.get_Range("A7", "G" + (rowIndex + 10).ToString());
AllRange.Borders.Color = 0x000000;
try
{
currentWorksheet.Shapes.AddPicture(@"D:\Subject\2013-HKI-Nhap Mon Cong Nghe Phan Mem\DoAn\SVN\Database\logo.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 140, 40);
currentWorksheet.Shapes.AddPicture(@"D:\Subject\2013-HKI-Nhap Mon Cong Nghe Phan Mem\DoAn\SVN\Database\flag.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 380, 27, 23, 15);
}
catch
{
MessageBox.Show("Không tìm thấy tập tin ảnh");
}
}
else
{
MessageBox.Show("Không có dữ liệu để xuất", "Xuất file", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
//string timeStamp = DateTime.Now.ToString("s");
//timeStamp = timeStamp.Replace(':', '-');
//timeStamp = timeStamp.Replace("T", "__");
//currentWorksheet.Cells[1, 1] = timeStamp;
//currentWorksheet.Cells[1, 2] = "No error occured";
}
using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
{
exportSaveFileDialog.Title = "Select Excel File";
exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";
if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
{
string fullFileName = exportSaveFileDialog.FileName;
// currentWorkbook.SaveCopyAs(fullFileName);
// indicating that we already saved the workbook, otherwise call to Quit() will pop up
// the save file dialogue box
currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
currentWorkbook.Saved = true;
MessageBox.Show("Xuất file thành công", "Xuất file Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
string[] symbol = new string[5] { "A", "B", "C", "D", "E" };
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.ShowDialog();
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(openFileDialog1.FileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
for (int i = 1; i < 201; i++)
{
for (int j = 1; j < 6; j++)
{
Microsoft.Office.Interop.Excel.Range range = ObjWorkSheet.get_Range(symbol[j-1] + i.ToString(), symbol[j - 1] + i.ToString());
education[i - 1, j - 1] = Double.Parse(range.Text.ToString());
}
}
ObjExcel.Quit();
}
private void expExportToExcel_Expanded(object sender, RoutedEventArgs e)
{
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
expExportToExcel.IsExpanded = false;
worksheet = workbook.ActiveSheet;
worksheet.Name = "OpenOrders";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = TheFindInvoicedProductionProjectsDataSet.FindInvoicedProductionProjects.Rows.Count;
intColumnNumberOfRecords = TheFindInvoicedProductionProjectsDataSet.FindInvoicedProductionProjects.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindInvoicedProductionProjectsDataSet.FindInvoicedProductionProjects.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindInvoicedProductionProjectsDataSet.FindInvoicedProductionProjects.Rows[intRowCounter][intColumnCounter].ToString();
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
catch (System.Exception ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // Invoiced Project Reports // Export To Excel " + ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
/// <summary>
/// 读取指定人员某时段的拜访汇总报表数据,如果是部门管理，获取所有人的employeeID包含子部门
/// </summary>
/// <param name="employeeId">人员ID</param>
/// <returns></returns>
public string DownloadAllReportDate(string employeeId, string startDate, string endDate, string downloadType = "1")
{
string result = "", sql = "", date1 = "", date2 = "", fileName = "", savePath = "";
result = "<GetData>" +
"<Result>False</Result>" +
"<Description></Description>" +
"<DataRows></DataRows>" +
"</GetData>";
WorkShip workShip = new WorkShip();
employeeId = workShip.GetAllMemberIDsByLeaderID(employeeId).Replace("|", "','");
//sql = $" Select t3.FName As EmployeeName, t1.FEmployeeID As EmployeeID,t2.FName As InstitutionName , sum(Case FScheduleID When '4484030a-28d1-4e5e-ba72-6655f1cb2898' Then 1 Else 0 End) AS UnplanedCallCount, Sum(1) AS CallCount,SUM(ISNULL(DATEDIFF(mi, t1.FStartTime, t1.FEndTime), 0)) AS TimeSpan From CallActivity t1 Left Join t_Items t2 On t1.FInstitutionID = t2.FID Left Join t_Items t3 On t1.FEmployeeID = t3.FID Where FDate between '{startDate}' and '{endDate}' and FEmployeeID In('{employeeIds}') Group by t3.FName,t2.FName,t1.FEmployeeID Order by CallCount Desc,TimeSpan Desc,FEmployeeID desc";
if (downloadType == "1")
{
sql = $@"Select Isnull(t4.FName,'') As 姓名
,FSubject as 主题,t2.FName as 拜访机构,FClientID as 客户 ,FConcept as 传递理念,t6.FName as 产品名称,FActivity as 总结 , ISNULL(t1.FType, '') as 拜访类型 , (Left(CONVERT(varchar(100), t1.FStartTime, 120), 16)) As 日期 From[CallActivity] t1 Left Join t_Items t2
On t1.FInstitutionID = t2.FID Left Join t_Items t4 On t1.FEmployeeID = t4.FID left join t_Items t5 on t1.FInstitutionID = t5.FName left join t_Items t6 on t1.FProductID = t6.FID where t1.FEmployeeID in ('{employeeId}') and FDate between '{startDate}' and '{endDate}' Order by t4.FName Desc";
}
else if (downloadType == "2")
{
sql = $@"Select [FDept_2] as 销售总部
,[FDept_3] as 大区
,[FDept_4] as 省区
,ISNULL([FDept_5],'') as 地区,Isnull(t2.FName,'') As 姓名,t1.FSignInAddress 签到地址, FSignInTime 签到时间,FSignOutTime 签退时间,FRemark 备注,[FDistance] as 签到签退距离
,[FType] as 类型
,[FWeek] as 周
,[FMonth] as 月份
From [yaodaibao].[dbo].[RouteData_His] t1 Left Join t_Items t2
On t1.FInstitutionID = t2.FID and t1.FEmployeeID = t2.FID
and t1.FInstitutionID = t2.FName where t1.FEmployeeID in ('{employeeId}') and FSignInTime between '{startDate}' and '{endDate}' Order by t2.FName Desc" ;
}
SQLServerHelper runner = new SQLServerHelper();
//sql = string.Format(sql, date1, date2, employeeId);
DataTable dt = runner.ExecuteSql(sql);
//如果没有数据返回错误
if (dt.Rows.Count == 0)
{
result = @"{""GetData"":{""Result"":""False"",""DataRow"":{""FileURL"":""无数据可供下载.""}}}";
return(result);
}
fileName = Guid.NewGuid().ToString().Replace("-", "");
try
{
//移除列
// dt.Columns.Remove("日期");
var excel = new Microsoft.Office.Interop.Excel.Application
{
DisplayAlerts = false
};
//生成一个新的工作薄
var excelworkBook = excel.Workbooks.Add(Type.Missing);
var excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
//获得表的行，列数目
int row_num = dt.Rows.Count;
int column_num = dt.Columns.Count;
//生成一个二维数组
object[,] dataArry = new object[row_num, column_num];
object[,] headArry = new object[1, column_num];
//把表中的数据放到数组中
for (int i = 0; i < row_num; i++)
{
for (int j = 0; j < column_num; j++)
{
dataArry[i, j] = dt.Rows[i][j].ToString();
}
}
for (int j = 0; j < column_num; j++)
{
headArry[0, j] = dt.Columns[j].ColumnName.ToString();
}
excel.Range[excel.Cells[1, 1], excel.Cells[1, column_num]].Value = headArry;
//把数组中的数据放到Excel中
excel.Range[excel.Cells[2, 1], excel.Cells[row_num + 1, column_num]].Value = dataArry;
string path = System.Configuration.ConfigurationManager.AppSettings["Path"];
string fullpath = System.Web.HttpContext.Current.Server.MapPath(path);
savePath = fullpath + "\\" + fileName + ".xlsx";
excelworkBook.SaveAs(savePath);
excelworkBook.Close();
excel.Quit();
}
catch (Exception err)
{
result = $@"{{""GetData"":{{ ""Result"":""False"",""Description"":""{ err.Message}"",""DataRow"":"""" }} }}";
return(result);
}
string url = "http://ydb.tenrypharm.com:6060/Files/" + fileName + ".xlsx";
result = $@"{{""GetData"":{{ ""Result"":""True"",""DataRow"":{{""FileURL"":""{url}""}} }} }}";
return(result);
}
private void ReportMonthExpectedExecution()
{
SqlCommand comm = new SqlCommand("SELECT PFMName, PartnerType, FinPositionEPL, PlanSumm, FactSummOnDate, PlanSummOnDate, ExpectedMonthExec, AbsDeviation, RelativeDeviation FROM udf_get_MonthExpectedExecution(@date)", _connection);
comm.CommandType = CommandType.Text;
comm.Parameters.Add("@date", SqlDbType.Date).Direction = ParameterDirection.Input;
comm.Parameters["@date"].Value = dateTimePicker1.Value.Date;
try
{
if (_connection.State != System.Data.ConnectionState.Open)
_connection.Open();
using (SqlDataReader reader = comm.ExecuteReader())
{
//if (ExcelApp == null)
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Workbooks.Add(TemplatePath + "MonthExpectedExecution.xlt");
Microsoft.Office.Interop.Excel.Worksheet ReportWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Worksheets["First"];
Dictionary<string, string> labelDict = new Dictionary<string, string>();
labelDict.Add("#header", "Ожидаемое исполнение месяца на " + dateTimePicker1.Value.ToShortDateString());
labelDict.Add("#planmonth", "План поступлений/платежей на " + dateTimePicker1.Value.ToString("MM.yyyy"));
labelDict.Add("#factondate", "Факт " + "01." + dateTimePicker1.Value.ToString("MM") + " - " + dateTimePicker1.Value.ToString("dd.MM"));
labelDict.Add("#planondate", "План " + dateTimePicker1.Value.AddDays(1).ToString("dd.MM") + " - " + DateTime.DaysInMonth(dateTimePicker1.Value.Year, dateTimePicker1.Value.Month) + "." + dateTimePicker1.Value.ToString("MM"));
string error;
if (!ReportExcelUtil.ExportData2Report(ReportWorksheet, reader, labelDict, out error))
{
ExcelApp.Quit();
MessageBox.Show(error, "Ошибка");
}
else
{
ExcelApp.Visible = true;
this.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static string DataTableToExcel(DataTable dt)
{
string directoryName = Path.Combine(Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.FullName, "ExcelFiles");
if (!Directory.Exists(directoryName))
{
Directory.CreateDirectory(directoryName);
}
string fileName = string.Format(@"{0}\{1}", directoryName, DateTime.Now.ToString("ddMMyyyyHHmmss") + ".xlsx");
if (!File.Exists(fileName))
{
File.Create(fileName);
}
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
excel = new Microsoft.Office.Interop.Excel.Application();
excelworkBook = excel.Workbooks.Add(Type.Missing);
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Test work sheet";
// loop through each row and add values to our sheet
int rowcount = 1;
foreach (DataRow datarow in dt.Rows)
{
rowcount += 1;
for (int i = 1; i <= dt.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 2)
{
excelSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dt.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
//Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
//border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//border.Weight = 2d;
// excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dt.Columns.Count]];
// FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(fileName);
excelworkBook.Close();
excel.Quit();
return(fileName);
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>2
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
//excelSheet.Cells[1, 1] = ReporType;
//excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 1;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 2)
{
excelSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
// excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 2)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
// FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
//FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
//System.Windows.Forms.DialogResult DGres = System.Windows.Forms.MessageBox.Show("Excel generated succesfully." + Environment.NewLine + "Do you want us to open the application? ", "Excel Report", System.Windows.Forms.MessageBoxButtons.YesNo);
//if (DGres == System.Windows.Forms.DialogResult.Yes)
//{
// Process.Start(@saveAsLocation);
//}
return true;
}
catch (Exception ex)
{
MessageBox.Show("Excel Report generation failed : " + ex.Message.ToString());
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
private void btn_Export_Click(object sender, EventArgs e)
{
string checkStr = "0"; string PathName = "";
try
{
if (dataGridVieweachdoctorappoinmt.Rows.Count != 0)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls";
saveFileDialog1.FileName = "Appointment Report Of Doctor(" + DateTime.Now.ToString("dd-MM-yy h.mm.ss tt") + ").xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
PathName = saveFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 20;
int count = dataGridVieweachdoctorappoinmt.Columns.Count;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, count]].Merge();
if (comboBoxdoctor.SelectedIndex == 0)
{
ExcelApp.Cells[1, 1] = "APPOINTMENT REPORT OF All DOCTOR";
}
else if (comboBoxdoctor.SelectedIndex > 0)
{
ExcelApp.Cells[1, 1] = "APPOINTMENT REPORT OF Dr." + drctid + "";
}
ExcelApp.Cells[1, 1].HorizontalAlignment = HorizontalAlignment.Center;
ExcelApp.Cells[1, 1].Font.Size = 12;
ExcelApp.Cells[1, 1].Interior.Color = Color.FromArgb(153, 204, 255);
ExcelApp.Columns.ColumnWidth = 20;
ExcelApp.Cells[2, 1] = "From Date";
ExcelApp.Cells[2, 1].Font.Size = 10;
ExcelApp.Cells[3, 1] = "To Date";
ExcelApp.Cells[3, 1].Font.Size = 10;
ExcelApp.Cells[2, 2] = dateTimePickerappointeachdoctor1.Value.ToString("dd-MM-yyyy");
ExcelApp.Cells[2, 2].Font.Size = 10;
ExcelApp.Cells[3, 2] = dateTimePickerappointeachdoctor2.Value.ToString("dd-MM-yyyy");
ExcelApp.Cells[3, 2].Font.Size = 10;
ExcelApp.Cells[4, 1] = "Running Date";
ExcelApp.Cells[4, 1].Font.Size = 10;
ExcelApp.Cells[4, 2] = DateTime.Now.ToString("dd-MM-yyyy");
ExcelApp.Cells[4, 2].Font.Size = 10;
ExcelApp.Cells[3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ExcelApp.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
for (int i = 1; i < dataGridVieweachdoctorappoinmt.Columns.Count + 1; i++)
{
ExcelApp.Cells[5, i] = dataGridVieweachdoctorappoinmt.Columns[i - 1].HeaderText;
ExcelApp.Cells[5, i].ColumnWidth = 25;
ExcelApp.Cells[5, i].EntireRow.Font.Bold = true;
ExcelApp.Cells[5, i].Interior.Color = Color.FromArgb(0, 102, 204);
ExcelApp.Cells[5, i].Font.Size = 10;
ExcelApp.Cells[5, i].Font.Name = "Arial";
ExcelApp.Cells[5, i].Font.Color = Color.FromArgb(255, 255, 255);
ExcelApp.Cells[5, i].Interior.Color = Color.FromArgb(0, 102, 204);
}
for (int i = 0; i <= dataGridVieweachdoctorappoinmt.Rows.Count; i++)
{
try
{
for (int j = 0; j < dataGridVieweachdoctorappoinmt.Columns.Count; j++)
{
ExcelApp.Cells[i + 6, j + 1] = dataGridVieweachdoctorappoinmt.Rows[i].Cells[j].Value.ToString();
ExcelApp.Cells[i + 6, j + 1].BorderAround(true);
ExcelApp.Cells[i + 6, j + 1].Borders.Color = Color.FromArgb(0, 102, 204);
ExcelApp.Cells[i + 6, j + 1].Font.Size = 8;
}
}
catch
{
}
}
ExcelApp.ActiveWorkbook.SaveAs(PathName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
checkStr = "1";
MessageBox.Show("Successfully Exported to Excel", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("No records found, Please change the date and try again!..", "No Records Found ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error !..", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static void exportexcel(System.Data.DataTable dt, string filename, string charttitle, bool tableflag, bool bl)
{
//System.Data.DataTable dt = new System.Data.DataTable();
if (dt == null) return;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel.Series oSeries;
//Microsoft.Office.Interop.Excel.Range oResizeRange;
Microsoft.Office.Interop.Excel._Chart oChart;
//String sMsg;
//int iNumQtrs;
GC.Collect();//ϵͳ����������
//string filename = @"C:\Documents and Settings\tongxl\����\nnn.xls";
//Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);
Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(true);
if (ep == null)
{
MsgBox.Show("������Excel���������Ļ���δ��װExcel");
return;
}
ep.Visible = true;
Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
ws.UsedRange.Select();
ws.UsedRange.Copy(System.Type.Missing);
// wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);
int rowIndex = 1;
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[rowIndex, colIndex++] = col.ColumnName;
}
for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
{
DataRow row = dt.Rows[drvIndex];
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
colIndex++;
}
}
oWB = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
int rcount = dt.Rows.Count;
int ccount = dt.Columns.Count;
oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[rcount + 2, ccount + 2]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
Microsoft.Office.Interop.Excel.XlRowCol.xlRows, 1, true, true,
charttitle, Missing.Value, Missing.Value, Missing.Value);
// oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);
//string str = String.Empty;
//for (int I = 1; I < 15; I++)
//{
// str += I.ToString() + "\t";
//}
//try { oSeries.XValues = str; }
//catch { }
// oSeries.HasDataLabels = true;
// string charttitle ,bool tableflag ,bool bl)
if (tableflag == true)
oChart.HasDataTable = true;
else
oChart.HasDataTable = false;
oChart.PlotVisibleOnly = false;
//
Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//axis.HasTitle = true;
//axis.AxisTitle.Text = "Sales Figures";
// axis.HasMinorGridlines=true;
Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
//ax.HasTitle = true;
//ax.AxisTitle.Text = "Sales Figures";
ax.HasMajorGridlines = true;
// string filename = @"C:\Documents and Settings\tongxl\����\ccsb.xls";
// ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
wb.Saved = true;
wb.SaveCopyAs(filename);
}
catch (Exception ex)
{
MsgBox.Show("�����ļ�ʱ����,�ļ�����������\n" + ex.Message);
}
ep.Quit();
GC.Collect();//ǿ������
}
/// <summary>
/// 转换excel 成PDF文档
/// </summary>
/// <param name="_lstrInputFile">原文件路径</param>
/// <param name="_lstrOutFile">pdf文件输出路径</param>
/// <returns>true 成功</returns>
public bool ConverterToPdf(string _lstrInputFile, string _lstrOutFile)
{
if (!File.Exists(_lstrInputFile))
{
MessageBox.Show("源文件不存在", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
if (string.IsNullOrEmpty(_lstrOutFile) || _lstrOutFile.IndexOf("\\") < 0)
{
MessageBox.Show("请指定目标文件", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
if (!Directory.Exists(_lstrOutFile.Substring(0, _lstrOutFile.LastIndexOf("\\") + 1)))
{
MessageBox.Show("保存目录不存在", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
if (File.Exists(_lstrOutFile) && MessageBox.Show("文件【" + _lstrOutFile.Substring(_lstrOutFile.LastIndexOf("\\") + 1) + "】已存在，是否覆盖？", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != System.Windows.Forms.DialogResult.Yes)
{
return(false);
}
_lstrOutFile = _lstrOutFile.Substring(0, _lstrOutFile.LastIndexOf("."));
Microsoft.Office.Interop.Excel.Application lobjExcelApp = null;
Microsoft.Office.Interop.Excel.Workbooks lobjExcelWorkBooks = null;
Microsoft.Office.Interop.Excel.Workbook lobjExcelWorkBook = null;
string lstrTemp = string.Empty;
object lobjMissing = System.Reflection.Missing.Value;
try
{
lobjExcelApp = new Microsoft.Office.Interop.Excel.Application();
lobjExcelApp.Visible = true;
lobjExcelWorkBooks = lobjExcelApp.Workbooks;
lobjExcelWorkBook = lobjExcelWorkBooks.Open(_lstrInputFile, true, true, lobjMissing, lobjMissing, lobjMissing, true,
lobjMissing, lobjMissing, lobjMissing, lobjMissing, lobjMissing, false, lobjMissing, lobjMissing);
//Microsoft.Office.Interop.Excel 12.0.0.0之后才有这函数
//注释掉好像没问题 lstrTemp = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xls" + (lobjExcelWorkBook.HasVBProject ? 'm' : 'x');
//lstrTemp = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xls";
//注释掉好像没问题lobjExcelWorkBook.SaveAs(lstrTemp, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel4Workbook, Type.Missing, Type.Missing, Type.Missing, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
//注释掉好像没问题 false, Type.Missing, Type.Missing, Type.Missing);
//输出为PDF 第一个选项指定转出为PDF,还可以指定为XPS格式
lobjExcelWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, _lstrOutFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, Type.Missing, false, Type.Missing, Type.Missing, false, Type.Missing);
lobjExcelWorkBooks.Close();
lobjExcelApp.Quit();
}
catch (Exception ex)
{
//其他日志操作；
return(false);
}
finally
{
try
{
if (lobjExcelWorkBook != null)
{
lobjExcelWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(lobjExcelWorkBook);
lobjExcelWorkBook = null;
}
if (lobjExcelWorkBooks != null)
{
lobjExcelWorkBooks.Close();
Marshal.ReleaseComObject(lobjExcelWorkBooks);
lobjExcelWorkBooks = null;
}
if (lobjExcelApp != null)
{
lobjExcelApp.Quit();
Marshal.ReleaseComObject(lobjExcelApp);
lobjExcelApp = null;
}
}
catch (Exception e)
{
}
//主动激活垃圾回收器，主要是避免超大批量转文档时，内存占用过多，而垃圾回收器并不是时刻都在运行！
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(true);
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Register.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D11", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("H11", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 品牌型号
m_objRange = m_objSheet.get_Range("D12", oMissing);
m_objRange.Value = this.brand.Text;
//车辆识别代号
m_objRange = m_objSheet.get_Range("H12", oMissing);
m_objRange.Value = this.vin.Text;
// 机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("E3", oMissing);
m_objRange.Value = this.ownerName.Text;
// 机动车所有人邮寄地址
m_objRange = m_objSheet.get_Range("E4", oMissing);
m_objRange.Value = this.ownerAddress.Text;
// 机动车所有人邮政编码
m_objRange = m_objSheet.get_Range("I3", oMissing);
m_objRange.Value = this.ownerPostcode.Text;
// 机动车所有人固定电话
m_objRange = m_objSheet.get_Range("I5", oMissing);
m_objRange.Value = this.ownerPhone.Text;
// 机动车所有人移动电话
m_objRange = m_objSheet.get_Range("E5", oMissing);
m_objRange.Value = this.ownerMobile.Text;
// 省（自治县、直辖市)
m_objRange = m_objSheet.get_Range("D10", oMissing);
m_objRange.Value = " " + this.province.Text + " " + this.department.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("E6", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("H6", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("F18", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day ;
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
private void ExportToExcel()
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "AISIN";
int cellRowIndex = 2;
int cellColumnIndex = 1;
worksheet.Cells[1, 1] = "ID";
worksheet.Cells[1, 2] = "PLAKA";
worksheet.Cells[1, 3] = "GİRİŞ SAATİ";
worksheet.Cells[1, 4] = "GİRİŞ TARİHİ";
worksheet.Cells[1, 5] = "GİRİŞ KM";
worksheet.Cells[1, 6] = "GELDİĞİ YER";
worksheet.Cells[1, 7] = "ÇIKIŞ SAATİ";
worksheet.Cells[1, 8] = "ÇIKIŞ TARİHİ";
worksheet.Cells[1, 9] = "ÇIKIŞ KM";
worksheet.Cells[1, 10] = "GİTTİĞİ YER";
worksheet.Cells[1, 11] = "ŞOFÖR ADI";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (j == 2)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 5);
}
else if (j == 3)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 10);
}
else if (j == 6)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 5);
}
else if (j == 7)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString().Substring(0, 10);
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel dosyaları (*.xlsx)|*.xlsx|Tüm Dosyalar (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Veriler başarıyla Excele aktarıldı.");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
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 = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
int s = 0;
for (int r = 0; r < myDGV.Rows.Count; r++)
{
if (Convert.ToBoolean(myDGV.Rows[r].Cells[0].Value))
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
s++;
}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n");
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
public string ExportCallReport(string xmlString)
{
string result = "", sql = "", date1 = "", date2 = "", employeeIds = "", employeeId = "", savePath = "", fileName = "";
result = "<GetData>" +
"<Result>False</Result>" +
"<Description></Description>" +
"<DataRows></DataRows>" +
"</GetData>";
date1 = DateTime.Now.ToString("yyyy-MM") + "-01";
date2 = DateTime.Now.ToString("yyyy-MM-dd");
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode node = doc.SelectSingleNode("GetData/BeginDate");
if (node != null && node.InnerText.Trim().Length > 0)
{
date1 = node.InnerText.Trim();
}
node = doc.SelectSingleNode("GetData/EndDate");
if (node != null && node.InnerText.Trim().Length > 0)
{
date2 = node.InnerText.Trim();
}
node = doc.SelectSingleNode("GetData/EmployeeID");//若为团队负责人，要读取其及直接下属的数据
if (node != null && node.InnerText.Trim().Length > 0)
{
employeeId = node.InnerText.Trim();
//WorkShip ws = new WorkShip();
//employeeIds = ws.GetAllMemberIDsByLeaderID(employeeId);
}
node = doc.SelectSingleNode("GetData/EmployeeIDList");
if (node != null && node.InnerText.Trim().Length > 0)
{
employeeIds = node.InnerText.Trim();
if (employeeIds == "99")//查询其所有下属
{
WorkShip ws = new WorkShip();
employeeIds = ws.GetAllMemberIDsByLeaderID(employeeId);
}
employeeIds = employeeIds.Replace("|", "','");
}
if (employeeIds.Length == 0)
{
employeeIds = employeeId;
}
sql = @"select Fdate as 日期, [FEmployeeName] as 姓名,
[RouteCount] as 签到次数,
[OKRouteCount] as 有效签到次数,
[CallCount] as 拜访次数,
[UnPlanedCallCount] as 非计划拜访次数
from [dbo].[Route_Call_View] Where FDate between '{0}' and '{1}' and FEmployeeID In('{2}') order by Fdate desc ,[RouteCount] desc";
SQLServerHelper runner = new SQLServerHelper();
sql = string.Format(sql, date1, date2, employeeIds);
DataTable dt = runner.ExecuteSql(sql);
//如果没有数据返回错误
if (dt.Rows.Count == 0)
{
result = @"<GetData>" +
"<Result>False</Result>" +
"<DataRow><FileURL>" + "无数据可供下载." + "</FileURL></DataRow></GetData>";
return(result);
}
fileName = Guid.NewGuid().ToString().Replace("-", "");
try
{
//移除列
dt.Columns.Remove("日期");
var excel = new Microsoft.Office.Interop.Excel.Application
{
DisplayAlerts = false
};
//生成一个新的工资薄
var excelworkBook = excel.Workbooks.Add(Type.Missing);
var excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
//获得表的行，列数目
int row_num = dt.Rows.Count;
int column_num = dt.Columns.Count;
//生成一个二维数组
object[,] dataArry = new object[row_num, column_num];
object[,] headArry = new object[1, column_num];
//把表中的数据放到数组中
for (int i = 0; i < row_num; i++)
{
for (int j = 0; j < column_num; j++)
{
dataArry[i, j] = dt.Rows[i][j].ToString();
}
}
for (int j = 0; j < column_num; j++)
{
headArry[0, j] = dt.Columns[j].ColumnName.ToString();
}
excel.Range[excel.Cells[1, 1], excel.Cells[1, column_num]].Value = headArry;
//把数组中的数据放到Excel中
excel.Range[excel.Cells[2, 1], excel.Cells[row_num + 1, column_num]].Value = dataArry;
string path = System.Configuration.ConfigurationManager.AppSettings["Path"];
string fullpath = System.Web.HttpContext.Current.Server.MapPath(path);
savePath = fullpath + "\\" + fileName + ".xlsx";
excelworkBook.SaveAs(savePath);
excelworkBook.Close();
excel.Quit();
}
catch (Exception err)
{
result = "" + "<GetData>" +
"<Result>False</Result>" +
"<Description>" + err.Message + "</Description></GetData>";
return(result);
}
string url = "http://ydb.tenrypharm.com:6060/Files/" + fileName + ".xlsx";
result = @"<GetData>" +
"<Result>True</Result>" +
"<DataRow><FileURL>" + url + "</FileURL></DataRow></GetData>";
return(result);
}
/// <summary>
/// 将DataGridView中的数据导入到Excel中
/// </summary>
/// <param name="datagridview">DataGridView</param>
/// <param name="SheetName">Excel sheet title</param>
public static void DataGridView2Excel(System.Windows.Forms.DataGridView datagridview, string SheetName)
{
//string val;//add by cs 20120531
int iRows = 0;
int iCols = 0;
int iTrueCols = 0;
string filename = "";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet ws = null;
if (wb.Worksheets.Count > 0)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
}
else
{
wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
}
// Add by cs Strat 2010-12-14
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl2007 files(*.xlsx)|*.xlsx";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Data To Excel!"; //Modify by cs 2010-12-14
//saveFileDialog.ShowDialog(); // Mark by cs 2010-12-14
// Add by cs End 2010-12-14
saveFileDialog.CreatePrompt = false;
// saveFileDialog.OverwritePrompt = false;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
filename = saveFileDialog.FileName.ToString();
if (ws != null)
{
if (SheetName.Trim() != "")
{
ws.Name = SheetName;
}
iRows = datagridview.Rows.Count; //加上列头行
iTrueCols = datagridview.Columns.Count; //包含隐藏的列，一共有多少列
//求列数，省略Visible = false的列
for (int i = 0; i < datagridview.Columns.Count; i++)
{
if (datagridview.Columns[i].Visible)
iCols++;
}
//string[,] dimArray = new string[iRows + 1, iCols]; // 需要修改string[iRows + 1, iCols]为string[iRows, iCols]
string[,] dimArray = new string[iRows + 1, iCols];
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (datagridview.Columns[j].Visible)
{
dimArray[0, k] = datagridview.Columns[j].HeaderText;
k++;
}
}
// for (int i = 0; i < iRows; i++) 修改前
for (int i = 0; i < iRows; i++) // 修改后
{
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (datagridview.Columns[j].Visible) //mark by cs 20110121
{
if (datagridview.Rows[i].Cells[j].Value == null)
{
dimArray[i + 1, k] = "Null";
}
else
{
dimArray[i + 1, k] = datagridview.Rows[i].Cells[j].Value.ToString();
}
k++;
}
}
}
/* 修改前
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Value2 = dimArray;
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Font.Size = 10.0;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).RowHeight = 14.25;
* */
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Value2 = dimArray;
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).Font.Size = 10.0;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).RowHeight = 14.25;
for (int j = 0, k = 0; j < iTrueCols; j++)
{
//省略Visible = false的列
if (datagridview.Columns[j].Visible)
{
ws.get_Range(ws.Cells[1, k + 1], ws.Cells[1, k + 1]).ColumnWidth =
(datagridview.Columns[j].Width / 8.4) > 255 ? 255 : (datagridview.Columns[j].Width / 8.4);
//ws.Columns.c = datagridview.Columns[j].Width;
k++;
}
}
}
// app.Visible = true;//Modify by cs 20101214
//app.Visible = false;
//app.Save(filename);//Add by cs 2010-12-14 保存
//app.ActiveWorkbook.Name = filename;
//app.ActiveWorkbook.Save();
wb.Close(true, filename, System.Reflection.Missing.Value);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
wb.Close(false, filename, System.Reflection.Missing.Value);
}
finally
{
// wb.Close(true, AFileName, ObjMissing);
app.Quit();
app = null;
GC.Collect();
}
}
//else if (saveFileDialog.FileName.IndexOf(":") < 0) //被点了"取消"
else
{
wb.Close(false, filename, System.Reflection.Missing.Value);
app.Quit();
app = null;
GC.Collect();
return;
}
}
public string GerarFichas(string mes, string ano, List <System.Data.DataTable> data, List <string[]> colunas, String fileName)
{
string ret = "Relatório gerado com sucesso!";
try
{
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
if (data[6].Rows.Count > 0)
{
#region Ficha6F
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha6F";
for (int i = 1; i <= colunas[6].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[6][i - 1];
ws.Cells[5, i] = "(" + i + ")";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00";
for (int i = 0; i < data[6].Rows.Count; i++)
{
string data_formatada = data[6].Rows[i]["DT_DOC"].ToString();
ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
ws.Cells[i + 6, 2] = data[6].Rows[i]["TIPO"];
ws.Cells[i + 6, 3] = data[6].Rows[i]["SER"];
ws.Cells[i + 6, 4] = data[6].Rows[i]["NUM_DOC"];
ws.Cells[i + 6, 5] = data[6].Rows[i]["COD_PART"];
ws.Cells[i + 6, 6] = data[6].Rows[i]["NUM_DECL_DESP_EXP"];
ws.Cells[i + 6, 7] = data[6].Rows[i]["VL_DOC"];
ws.Cells[i + 6, 8] = data[6].Rows[i]["VL_BC_ICMS"];
ws.Cells[i + 6, 9] = data[6].Rows[i]["VL_ICMS"];
ws.Cells[i + 6, 10] = data[6].Rows[i]["PCT_CRED_OUT"];
ws.Cells[i + 6, 11] = data[6].Rows[i]["VL_CRED_OUT"];
}
for (int i = 1; i <= colunas[6].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Demonstrativo das Operações Não Geradoras de Crédito Acumulado";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
if (data[5].Rows.Count > 0)
{
#region Ficha6E
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha6E";
for (int i = 1; i <= colunas[5].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[5][i - 1];
if (i == 11)
{
ws.Cells[5, i] = "(" + i + ") = (9) * (10) / 100";
}
else if (i == 14)
{
ws.Cells[5, i] = "(" + i + ") = (11) + (13)";
}
else
{
ws.Cells[5, i] = "(" + i + ")";
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 14]).EntireColumn.NumberFormat = "#,##0.00";
for (int i = 0; i < data[5].Rows.Count; i++)
{
string data_formatada = data[5].Rows[i]["DT_DOC"].ToString();
ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
ws.Cells[i + 6, 2] = data[5].Rows[i]["TIPO"];
ws.Cells[i + 6, 3] = data[5].Rows[i]["SER"];
ws.Cells[i + 6, 4] = data[5].Rows[i]["NUM_DOC"];
ws.Cells[i + 6, 5] = data[5].Rows[i]["COD_PART"];
ws.Cells[i + 6, 6] = data[5].Rows[i]["COD_ENQUADRAMENTO"];
ws.Cells[i + 6, 7] = data[5].Rows[i]["VL_DOC"];
ws.Cells[i + 6, 8] = data[5].Rows[i]["IVA"];
ws.Cells[i + 6, 9] = data[5].Rows[i]["CUSTO_EST"];
ws.Cells[i + 6, 10] = data[5].Rows[i]["PMC"];
ws.Cells[i + 6, 11] = data[5].Rows[i]["CRED_EST_IMP"];
ws.Cells[i + 6, 12] = data[5].Rows[i]["PCT_CRED_OUT"];
ws.Cells[i + 6, 13] = data[5].Rows[i]["VL_CRED_OUT"];
ws.Cells[i + 6, 14] = data[5].Rows[i]["VL_CRED_ACUM_GER"];
}
for (int i = 1; i <= colunas[5].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso III - Operações sem Pagamento de Imposto - Demais Casos";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
if (data[4].Rows.Count > 0)
{
#region Ficha6D
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha6D";
for (int i = 1; i <= colunas[4].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[4][i - 1];
if (i == 12)
{
ws.Cells[5, i] = "(" + i + ") = (10) * (11)";
}
else if (i == 16)
{
ws.Cells[5, i] = "(" + i + ") = (14) + (15)";
}
else
{
ws.Cells[5, i] = "(" + i + ")";
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 10]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 12]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 15]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00";
for (int i = 0; i < data[4].Rows.Count; i++)
{
string data_formatada = data[4].Rows[i]["DT_DOC"].ToString();
ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
ws.Cells[i + 6, 2] = data[4].Rows[i]["TIPO"];
ws.Cells[i + 6, 3] = data[4].Rows[i]["SER"];
ws.Cells[i + 6, 4] = data[4].Rows[i]["NUM_DOC"];
ws.Cells[i + 6, 5] = data[4].Rows[i]["COD_PART"];
ws.Cells[i + 6, 6] = data[4].Rows[i]["COD_ENQUADRAMENTO"];
ws.Cells[i + 6, 7] = (Convert.ToBoolean(data[4].Rows[i]["COMPROV_OP"])) ? "Sim" : "Não";
ws.Cells[i + 6, 8] = data[4].Rows[i]["VL_DOC"];
ws.Cells[i + 6, 9] = data[4].Rows[i]["IVA"];
ws.Cells[i + 6, 10] = data[4].Rows[i]["CUSTO_EST"];
ws.Cells[i + 6, 11] = data[4].Rows[i]["PMC"];
ws.Cells[i + 6, 12] = data[4].Rows[i]["CRED_EST_IMP"];
ws.Cells[i + 6, 13] = data[4].Rows[i]["PCT_CRED_OUT"];
ws.Cells[i + 6, 14] = data[4].Rows[i]["VL_CRED_OUT"];
ws.Cells[i + 6, 15] = data[4].Rows[i]["VL_ICMS_COMPR"];
ws.Cells[i + 6, 16] = data[4].Rows[i]["VL_CRED_ACUM_GER"];
}
for (int i = 1; i <= colunas[4].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso III - Operações sem Pagamento de Imposto - Zona Franca de Manaus";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
if (data[3].Rows.Count > 0)
{
#region Ficha6B
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha6B";
for (int i = 1; i <= colunas[3].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[3][i - 1];
if (i == 13)
{
ws.Cells[5, i] = "(" + i + ") = (11) * (12)";
}
else if (i == 16)
{
ws.Cells[5, i] = "(" + i + ") = (13) + (15)";
}
else if (i == 17)
{
ws.Cells[5, i] = "(" + i + ") = (16) - (9)";
}
else
{
ws.Cells[5, i] = "(" + i + ")";
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00";
for (int i = 0; i < data[3].Rows.Count; i++)
{
string data_formatada = data[3].Rows[i]["DT_DOC"].ToString();
ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
ws.Cells[i + 6, 2] = data[3].Rows[i]["TIPO"];
ws.Cells[i + 6, 3] = data[3].Rows[i]["SER"];
ws.Cells[i + 6, 4] = data[3].Rows[i]["NUM_DOC"];
ws.Cells[i + 6, 5] = data[3].Rows[i]["COD_PART"];
ws.Cells[i + 6, 6] = data[3].Rows[i]["COD_ENQUADRAMENTO"];
ws.Cells[i + 6, 7] = data[3].Rows[i]["VL_DOC"];
ws.Cells[i + 6, 8] = data[3].Rows[i]["VL_BC_ICMS"];
ws.Cells[i + 6, 9] = data[3].Rows[i]["VL_ICMS"];
ws.Cells[i + 6, 10] = data[3].Rows[i]["IVA"];
ws.Cells[i + 6, 11] = data[3].Rows[i]["CUSTO_EST"];
ws.Cells[i + 6, 12] = data[3].Rows[i]["PMC"];
ws.Cells[i + 6, 13] = data[3].Rows[i]["CRED_EST_IMP"];
ws.Cells[i + 6, 14] = data[3].Rows[i]["PCT_CRED_OUT"];
ws.Cells[i + 6, 15] = data[3].Rows[i]["VL_CRED_OUT"];
ws.Cells[i + 6, 16] = data[3].Rows[i]["VL_TOTAL_ICMS"];
ws.Cells[i + 6, 17] = data[3].Rows[i]["VL_CRED_ACUM_GER"];
}
for (int i = 1; i <= colunas[3].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso II - Operações com Redução de Base de Cálculo";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
if (data[2].Rows.Count > 0)
{
#region Ficha6A
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha6A";
for (int i = 1; i <= colunas[2].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[2][i - 1];
if (i == 13)
{
ws.Cells[5, i] = "(" + i + ") = (11) * (12)";
}
else if (i == 16)
{
ws.Cells[5, i] = "(" + i + ") = (13) + (15)";
}
else if (i == 17)
{
ws.Cells[5, i] = "(" + i + ") = (16) - (9)";
}
else
{
ws.Cells[5, i] = "(" + i + ")";
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 7]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 8]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 9]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 11]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 13]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 16]).EntireColumn.NumberFormat = "#,##0.00";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 17]).EntireColumn.NumberFormat = "#,##0.00";
for (int i = 0; i < data[2].Rows.Count; i++)
{
string data_formatada = data[2].Rows[i]["DT_DOC"].ToString();
ws.Cells[i + 6, 1] = (data_formatada == "") ? "" : data_formatada.Substring(0, 2) + "/" + data_formatada.Substring(2, 2) + "/" + data_formatada.Substring(4, 4);
ws.Cells[i + 6, 2] = data[2].Rows[i]["TIPO"];
ws.Cells[i + 6, 3] = data[2].Rows[i]["SER"];
ws.Cells[i + 6, 4] = data[2].Rows[i]["NUM_DOC"];
ws.Cells[i + 6, 5] = data[2].Rows[i]["COD_PART"];
ws.Cells[i + 6, 6] = data[2].Rows[i]["COD_ENQUADRAMENTO"];
ws.Cells[i + 6, 7] = data[2].Rows[i]["VL_DOC"];
ws.Cells[i + 6, 8] = data[2].Rows[i]["VL_BC_ICMS"];
ws.Cells[i + 6, 9] = data[2].Rows[i]["VL_ICMS"];
ws.Cells[i + 6, 10] = data[2].Rows[i]["IVA"];
ws.Cells[i + 6, 11] = data[2].Rows[i]["CUSTO_EST"];
ws.Cells[i + 6, 12] = data[2].Rows[i]["PMC"];
ws.Cells[i + 6, 13] = data[2].Rows[i]["CRED_EST_IMP"];
ws.Cells[i + 6, 14] = data[2].Rows[i]["PCT_CRED_OUT"];
ws.Cells[i + 6, 15] = data[2].Rows[i]["VL_CRED_OUT"];
ws.Cells[i + 6, 16] = data[2].Rows[i]["VL_TOTAL_ICMS"];
ws.Cells[i + 6, 17] = data[2].Rows[i]["VL_CRED_ACUM_GER"];
}
for (int i = 1; i <= colunas[2].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Demonstrativo das Operações Geradoras de Crédito Acumulado do ICMS Artigo 71, Inciso I - Operações com Aplicação de Alíquotas Diversificadas";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
if (data[1].Rows.Count > 0)
{
#region Ficha5D
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha5D";
for (int i = 1; i <= colunas[1].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[1][i - 1];
ws.Cells[5, i] = "(" + i + ")";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
for (int i = 0; i < data[1].Rows.Count; i++)
{
ws.Cells[i + 6, 1] = data[1].Rows[i]["COD_ENQUADRAMENTO"];
ws.Cells[i + 6, 2] = data[1].Rows[i]["COD_HIP_GER"];
ws.Cells[i + 6, 3] = data[1].Rows[i]["ANEXO"];
ws.Cells[i + 6, 4] = data[1].Rows[i]["ARTIGO"];
ws.Cells[i + 6, 5] = data[1].Rows[i]["INCISO"];
ws.Cells[i + 6, 6] = data[1].Rows[i]["ALINEA"];
ws.Cells[i + 6, 7] = data[1].Rows[i]["PARAGRAFO"];
ws.Cells[i + 6, 8] = data[1].Rows[i]["ITEM"];
ws.Cells[i + 6, 9] = data[1].Rows[i]["LETRA"];
ws.Cells[i + 6, 10] = data[1].Rows[i]["OBS"];
}
for (int i = 1; i <= colunas[1].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Enquadramento Legal da Operação/Prestação Geradora do Crédito Acumulado do ICMS";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
if (data[0].Rows.Count > 0)
{
#region Ficha5C
sheets = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
ws.Name = "Ficha5C";
for (int i = 1; i <= colunas[0].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.NumberFormat = "@";
ws.Cells[4, i] = colunas[0][i - 1];
ws.Cells[5, i] = "(" + i + ")";
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[5, i]).BorderAround2();
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).Font.Color = System.Drawing.Color.DarkOrange;
}
for (int i = 0; i < data[0].Rows.Count; i++)
{
ws.Cells[i + 6, 1] = data[0].Rows[i]["COD_PART"];
ws.Cells[i + 6, 2] = data[0].Rows[i]["NOME"];
ws.Cells[i + 6, 3] = data[0].Rows[i]["COD_PAIS"];
ws.Cells[i + 6, 4] = data[0].Rows[i]["CNPJ"];
ws.Cells[i + 6, 5] = data[0].Rows[i]["IE"];
ws.Cells[i + 6, 6] = data[0].Rows[i]["END"];
ws.Cells[i + 6, 7] = data[0].Rows[i]["NUM"];
ws.Cells[i + 6, 8] = data[0].Rows[i]["COMPL"];
ws.Cells[i + 6, 9] = data[0].Rows[i]["BAIRRO"];
ws.Cells[i + 6, 10] = data[0].Rows[i]["MUN"];
ws.Cells[i + 6, 11] = data[0].Rows[i]["CEP"];
ws.Cells[i + 6, 12] = data[0].Rows[i]["UF"];
}
for (int i = 1; i <= colunas[0].Length; i++)
{
((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, i]).EntireColumn.AutoFit();
}
ws.Cells[1, 1] = "Cadastro de Participantes de Operações e Prestações";
ws.Cells[2, 1] = "PERÍODO: " + mes + "/" + ano;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)ws.Cells[2, 1]).Font.Bold = true;
#endregion
}
wb.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
xla.Quit();
}
catch (Exception ex)
{
ret = "Erro ao gerar relatório";
}
return(ret);
}
private void VerifyEmptyTabs(string fileName, int oldOnlyCount, int newOnlyCount, int diffCount, int matchCount)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open(Filename: fileName);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
excel.DisplayAlerts = false;
if (matchCount == 0) wb.Worksheets[5].Delete();
if (diffCount == 0) wb.Worksheets[4].Delete();
if (newOnlyCount == 0) wb.Worksheets[3].Delete();
if (oldOnlyCount == 0) wb.Worksheets[2].Delete();
excel.DisplayAlerts = true;
((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.Sheets[1]).Select(missing);
wb.Save();
wb.Close();
excel.Quit();
}
catch (COMException ex)
{
wb.Close(SaveChanges: false);
excel.Quit();
throw new System.ArgumentException("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
wb.Close(SaveChanges: false);
excel.Quit();
throw new System.ArgumentException("Error: " + ex.ToString());
}
finally
{
excel.Quit();
}
}
private void btnOpen_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.DefaultExt = ".xlsx";
openfile.Filter = "(.xlsx)|*.xlsx";
//openfile.ShowDialog();
var browsefile = openfile.ShowDialog();
if (browsefile == true)
{
txtFilePath.Text = openfile.FileName;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
//Static File From Base Path...........
//Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//Dynamic File Using Uploader...........
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;
string strCellData = "";
double douCellData;
int rowCnt = 0;
int colCnt = 0;
DataTable dt = new DataTable();
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
dt.Columns.Add(strColumn, typeof(string));
}
for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
{
string strData = "";
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
try
{
strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += strCellData + "|";
}
catch (Exception ex)
{
douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += douCellData.ToString() + "|";
}
}
strData = strData.Remove(strData.Length - 1, 1);
dt.Rows.Add(strData.Split('|'));
}
dtGrid.ItemsSource = dt.DefaultView;
excelBook.Close(true, null, null);
excelApp.Quit();
}
}
/// <summary>
/// Метод считывания файла Excel в tab.
/// </summary>
/// <param name="f">Главная форма программы</param>
public void Inp(Form1 f)
{
string str, filename = string.Empty;
int rowCount;
int collumnCount;
this.tab.Clear();
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "Excel (*.XLS;*.XLSX)|*.XLS;*.XLSX";
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
System.IO.StreamReader sr = new
System.IO.StreamReader(openFileDialog1.FileName);
filename = openFileDialog1.FileName;
sr.Close();
f.Toggle();
}
else
{
return;
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook book;
Microsoft.Office.Interop.Excel.Worksheet sheet;
Microsoft.Office.Interop.Excel.Range range;
book = excelApp.Workbooks.Open(filename, 0, true, 5, string.Empty, string.Empty, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.get_Item(1);
range = sheet.UsedRange;
for (rowCount = 1; rowCount <= range.Rows.Count; rowCount++)
{
this.tab.Add(new List<string>());
for (collumnCount = 1; collumnCount <= range.Columns.Count; collumnCount++)
{
str = (string)(range.Cells[rowCount, collumnCount] as Microsoft.Office.Interop.Excel.Range).Text;
this.tab[rowCount - 1].Add(str.Trim());
}
}
book.Close(true, null, null);
excelApp.Quit();
this.ReleaseObject(sheet);
this.ReleaseObject(book);
this.ReleaseObject(excelApp);
int x = 0, y = 0;
f.Tabl.RowCount = this.tab.Count;
foreach (List<string> i in this.tab)
{
f.Tabl.ColumnCount = i.Count;
foreach (string u in i)
{
f.Tabl.Rows[x].Cells[y].Value = u;
y++;
}
y = 0;
x++;
}
}
//法四
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
// DataTable dataTable = dataSet.Tables[0];
// int rowNumber = dataTable.Rows.Count;
// int columnNumber = dataTable.Columns.Count;
// String stringBuffer = "";
// if (rowNumber == 0)
// {
// MessageBox.Show("没有任何数据可以导入到Excel文件！");
// return false;
// }
// //建立Excel对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// excel.Application.Workbooks.Add(true);
// excel.Visible = isShowExcle;//是否打开该Excel文件
// //填充数据
// for (int i = 0; i < rowNumber; i++)
// {
// for (int j = 0; j < columnNumber; j++)
// {
// stringBuffer += dataTable.Rows[i].ItemArray[j].ToString();
// if (j < columnNumber - 1)
// {
// stringBuffer += "\t";
// }
// }
// stringBuffer += "\n";
// }
// Clipboard.Clear();
// Clipboard.SetDataObject(stringBuffer);
// ((Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1]).Select();
// ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet).Paste(Missing.Value, Missing.Value);
// Clipboard.Clear();
// return true;
//}
//public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
//{
// DataTable dataTable = dataSet.Tables[0];
// int rowNumber = dataTable.Rows.Count;
// int columnNumber = dataTable.Columns.Count;
// if (rowNumber == 0)
// {
// MessageBox.Show("没有任何数据可以导入到Excel文件！");
// return false;
// }
// //建立Excel对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook workBook = excel.Application.Workbooks.Add(true);
// excel.Visible = false;//是否打开该Excel文件
// //填充数据
// for (int i = 0; i < rowNumber; i++)
// {
// for (int j = 0; j < columnNumber; j++)
// {
// excel.Cells[i + 1, j + 1] = dataTable.Rows[i].ItemArray[j];
// }
// }
// //string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
// workBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// try
// {
// workBook.Saved = true;
// excel.UserControl = false;
// //excelapp.Quit();
// }
// catch (Exception exception)
// {
// MessageBox.Show(exception.Message);
// }
// finally
// {
// workBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
// excel.Quit();
// }
// if (isShowExcle)
// {
// System.Diagnostics.Process.Start(fileName);
// }
// return true;
//}
//public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
//{
// DataTable dataTable = dataSet.Tables[0];
// int rowNumber = dataTable.Rows.Count;//不包括字段名
// int columnNumber = dataTable.Columns.Count;
// int colIndex = 0;
// if (rowNumber == 0)
// {
// MessageBox.Show("没有任何数据可以导入到Excel文件！");
// return false;
// }
// //建立Excel对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// //excel.Application.Workbooks.Add(true);
// Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
// excel.Visible = isShowExcle;
// //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
// worksheet.Name = "挠度数据";
// Microsoft.Office.Interop.Excel.Range range;
// //生成字段名称
// foreach (DataColumn col in dataTable.Columns)
// {
// colIndex++;
// excel.Cells[1, colIndex] = col.ColumnName;
// }
// object[,] objData = new object[rowNumber, columnNumber];
// for (int r = 0; r < rowNumber; r++)
// {
// for (int c = 0; c < columnNumber; c++)
// {
// objData[r, c] = dataTable.Rows[r][c];
// }
// //Application.DoEvents();
// }
// // 写入Excel
// range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
// //range.NumberFormat = "@";//设置单元格为文本格式
// range.Value2 = objData;
// worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
// //string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
// workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// try
// {
// workbook.Saved = true;
// excel.UserControl = false;
// //excelapp.Quit();
// }
// catch (Exception exception)
// {
// MessageBox.Show(exception.Message);
// }
// finally
// {
// workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
// excel.Quit();
// }
// //if (isShowExcle)
// //{
// // System.Diagnostics.Process.Start(fileName);
// //}
// return true;
//}
/// <summary>
/// 将数据集中的数据保存到EXCEL文件
/// </summary>
/// <param name="dataSet">输入数据集</param>
/// <param name="fileName">保存EXCEL文件的绝对路径名</param>
/// <param name="isShowExcle">是否打开EXCEL文件</param>
/// <returns></returns>
public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
{
DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;
if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件！");
return(false);
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = false;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}
// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
//range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
//string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
workbook.Saved = true;
excel.UserControl = false;
//excelapp.Quit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
excel.Quit();
}
if (isShowExcle)
{
System.Diagnostics.Process.Start(fileName);
}
return(true);
}
public void TestCleanToWithExcelApp()
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
AppCountMonitor monitor = new AppCountMonitor("excel");
string testDoc = TestUtils.TestFileUtils.MakeRootPathAbsolute(@"Projects\Workshare.API\Workshare.API.Tests\TestDocs\test.xlsx");
using (TempFile tf = new TempFile(testDoc))
{
using (TempFile dest = new TempFile())
{
File.Delete(dest.FilePath);
string initialHash = tf.MD5Sum;
IOfficeCleaner c = new OfficeCleaner();
c.CleanFileTo(tf.FilePath, dest.FilePath, app);
Assert.IsTrue(File.Exists(dest.FilePath), "We expected the dest file to be created");
string newHash = dest.MD5Sum;
Assert.AreNotEqual(initialHash, newHash, "We expected the Cleanion to change the file contents");
}
}
Assert.IsFalse(monitor.SeenMoreInstances(), "Additional instances of Excel were created during the test run - that means it didn't use the provided instance");
}
finally
{
object oFalse = false;
app.Quit();
Marshal.ReleaseComObject(app);
}
}
public DataSet ImportExcel(string filePath)
{
DataSet ds = null;
DataTable dt = null;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel.Range range = null;
object missing = System.Reflection.Missing.Value;
try
{
if (excel == null)
{
return(null);
}
//打开 Excel 文件
workbook = excel.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//获取所有的 sheet 表
sheets = workbook.Worksheets;
ds = new DataSet();
for (int i = 1; i <= sheets.Count; i++)
{
//获取第一个表
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
int rowIndex = 1; //起始行为 1
int colIndex = 1; //起始列为 1
DataColumn dc;
dt = new DataTable();
dt.TableName = "table" + i.ToString();
//读取列名
for (int j = 0; j < colCount; j++)
{
range = worksheet.Cells[rowIndex, colIndex + j];
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
//添加列
dt.Columns.Add(dc);
}
//读取行数据
for (int k = 1; k < rowCount; k++)
{
DataRow dr = dt.NewRow();
for (int l = 0; l < colCount; l++)
{
range = worksheet.Cells[rowIndex + k, colIndex + l];
//使用 range.Value.ToString(); 或 range.Value2.ToString(); 或 range.Text.ToString(); 都可以获取单元格的值
dr[l] = range.Text.ToString();
}
dt.Rows.Add(dr.ItemArray);
}
ds.Tables.Add(dt);
}
}
catch (Exception ex)
{
throw;
}
finally
{
workbook.Close();
//关闭退出
excel.Quit();
//释放 COM 对象
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
}
return(ds);
}
