/// <summary>
/// New excel file from the giving path
/// </summary>
/// <param name="strPath">strPath</param>
public void create(string strPath)
{
if(File.Exists(strPath))
{
File.Delete(strPath);
}
object Nothing = Missing.Value;
object format = Excel.XlFileFormat.xlWorkbookNormal;
xBook = excelMain.Workbooks.Add(Nothing);
xSheet = (Excel.Worksheet)xBook.Sheets[1];
//MessageBox.Show(strPath);
xBook.SaveAs(strPath,Nothing,Nothing,Nothing,Nothing,Nothing,
Excel.XlSaveAsAccessMode.xlExclusive,Nothing,Nothing,Nothing,Nothing);
}
static void Main(string[] args)
{
String text = "";
Excel.Application excelApp = new Excel.Application();
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\\Users\\Admin\\Desktop\\xmltest\\123456789-344.xlsx", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
Excel.Range range = (excelWorksheet.Cells[i, 13] as Excel.Range);
string cellValue = range.Value.ToString();
Excel.Range range2 = (excelWorksheet.Cells[i, 18] as Excel.Range);
string cellValue2 = range2.Value.ToString();
text = text +
"<Article>\n\t" +
"<Journal>\n\t" +
"<PublisherName>" + cellValue + "</PublisherName>\n\t" +
"<JournalTitle>" + cellValue2 + "</JournalTitle>\n\t" +
"<PISSN></PISSN>\n\t" +
"<EISSN></EISSN>\n\t" +
"<Volume></Volume>\n\t" +
"<Issue></Issue>\n\t" +
"<PartNumber></PartNumber>\n\t" +
"<IssueTopic></IssueTopic>\n\t" +
"<IssueLanguage></IssueLanguage>\n\t" +
"<Season></Season>\n\t" +
"<SpecialIssue>Y/N</SpecialIssue>\n\t" +
"<SupplementaryIssue>Y/N</SupplementaryIssue>\n\t" +
"<IssueOA>Y/N</IssueOA>\n\t" +
"<PubDate>\n\t" +
"<Year></Year>\n\t" +
"<Month></Month>\n\t" +
"<Day></Day>\n\t" +
"</PubDate>\n\t" +
"</Journal>\n\t" +
"<ArticleType></ArticleType>\n\t" +
"<ArticleTitle></ArticleTitle>\n\t" +
"<SubTitle></SubTitle>\n\t" +
"<ArticleLanguage></ArticleLanguage>\n\t" +
"<ArticleOA>Y/N</ArticleOA>\n\t" +
"<FirstPage></FirstPage>\n\t" +
"<LastPage></LastPage>\n\t" +
"<AuthorList>\n\t" +
"<Author>\n\t" +
"<FirstName></FirstName>\n\t" +
"<MiddleName></MiddleName>\n\t" +
"<LastName></LastName>\n\t" +
"<AuthorLanguage></AuthorLanguage>\n\t" +
"<Affiliation></Affiliation>\n\t" +
"<Country></Country>\n\t" +
"<Phone></Phone>\n\t" +
"<Fax></Fax>\n\t" +
"<AuthorEmails></AuthorEmails>\n\t" +
"<CorrespondingAuthor>Y/N</CorrespondingAuthor>\n\t" +
"</Author>\n\t" +
"</AuthorList>\n\t" +
"<DOI></DOI>\n\t" +
"<Abstract></Abstract>\n\t" +
"<AbstractLanguage></AbstractLanguage>\n\t" +
"<Keywords></Keywords>\n\t" +
"<Fulltext></Fulltext>\n\t" +
"<URLs>\n\t" +
"<abstract></abstract>\n\t" +
"<Fulltext>\n\t" +
"<pdf></pdf>\n\t" +
"</Fulltext>\n\t" +
"</URLs>\n\t" +
"<FulltextLanguage></FulltextLanguage>\n\t" +
"<References>\n\t" +
"<ReferencesarticleTitle></ReferencesarticleTitle>\n\t" +
"<ReferencesfirstPage></ReferencesfirstPage>\n\t" +
"<ReferenceslastPage></ReferenceslastPage>\n\t" +
"<authorList>\n\t" +
"<author>\n\t" +
"<ReferencesfirstName></ReferencesfirstName>\n\t" +
"<ReferencesmiddleName></ReferencesmiddleName>\n\t" +
"<ReferenceslastName></ReferenceslastName>\n\t" +
"<Referencesaffiliation></Referencesaffiliation>\n\t" +
"<Referencescountry></Referencescountry>\n\t" +
"</author>\n\t" +
"</authorList>\n\t" +
"</References>\n\t" +
"</Article>"
;
}
System.IO.File.WriteAllText(@"C:\\Users\\Admin\\Desktop\\xmltest\\www" + ".xml", text);
excelWorkbook.Close();
excelApp.Quit();
}
}
/// <summary>
/// 向文本框写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
public void SetTextBoxes(int sheetIndex, Hashtable ht)
{
if (ht.Count == 0) return;
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht)
{
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
}
}
}
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelHelper(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 隐藏指定名称的工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
public void HiddenWorkSheet(string sheetName)
{
try
{
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
sheet = workSheet;
}
if (sheet != null)
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
/// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex)
{
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
#region 利用Windwo粘贴板批量拷贝数据（在Web下面行不通）
/*sb = new StringBuilder();
for(int j=0;j<endRow-startRow;j++)
{
for(int k=0;k<colCount;k++)
{
sb.Append( dt.Rows[startRow + j][k].ToString() );
sb.Append("\t");
}
sb.Append("\n");
}
System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());
range = (Excel.Range)workSheet.Cells[top,left];
workSheet.Paste(range,false);*/
#endregion
}
}
/// <summary>
/// 复制列（在指定列右边复制指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
protected override void ImportData(Excel.Worksheet sheet, string[] strHeader)
{
DBDateTime dbDateTime = FormatHelper.GetNowDBDateTime(ApplicationService.DataProvider);
ImportDataEngine impEng = new ImportDataEngine(ApplicationService.DataProvider, this.configObjList, this.configMatchType, ApplicationService.LoginUserCode, dbDateTime);
impEng.ImportDataMapping = Properties.Settings.Default.DataMappingType;
impEng.UpdateRepeatData = Properties.Settings.Default.DataRepeat;
ApplicationService.DataProvider.BeginTransaction();
try
{
int iRow = 2;
int iImportedCount = 0;
while (true)
{
string[] strRow = ReadExcelRow(sheet, iRow, strHeader.Length);
if (strRow == null)
{
break;
}
// 导入产品
try
{
// 产品途程主资料
BenQGuru.eMES.MOModel.MOFacade moFacade = new BenQGuru.eMES.MOModel.MOFacade(ApplicationService.DataProvider);
BenQGuru.eMES.MOModel.ItemFacade itemFacade = new BenQGuru.eMES.MOModel.ItemFacade(ApplicationService.DataProvider);
BenQGuru.eMES.Domain.MOModel.Item2Route itemRoute = new BenQGuru.eMES.Domain.MOModel.Item2Route();
itemRoute.ItemCode = strRow[0].Trim().ToUpper();
itemRoute.RouteCode = strRow[1].Trim().ToUpper();
itemRoute.OrganizationID = int.Parse(strRow[2].Trim().ToUpper());
itemRoute.IsReference = "0";
itemRoute.MaintainUser = ApplicationService.LoginUserCode;
itemRoute.MaintainDate = dbDateTime.DBDate;
itemRoute.MaintainTime = dbDateTime.DBTime;
object ir = itemFacade.GetItem2Route(itemRoute.ItemCode, itemRoute.RouteCode, itemRoute.OrganizationID.ToString());
if (ir == null)
{
ApplicationService.DataProvider.Insert(itemRoute);
}
else
{
ApplicationService.DataProvider.Update(itemRoute);
}
// 产品默认途程
BenQGuru.eMES.Domain.MOModel.DefaultItem2Route defRoute = null;
BenQGuru.eMES.Domain.MOModel.DefaultItem2Route dr = moFacade.GetDefaultItem2Route(itemRoute.ItemCode) as BenQGuru.eMES.Domain.MOModel.DefaultItem2Route;
if (dr != null)
{
defRoute = dr;
}
else
{
defRoute = new BenQGuru.eMES.Domain.MOModel.DefaultItem2Route();
}
defRoute.ItemCode = itemRoute.ItemCode;
if (strRow[3].Trim().ToUpper() == "YES")
{
defRoute.RouteCode = itemRoute.RouteCode;
}
if (defRoute.RouteCode != null && defRoute.RouteCode != "")
{
defRoute.MDate = dbDateTime.DBDate;
defRoute.MTime = dbDateTime.DBTime;
if (dr == null)
{
ApplicationService.DataProvider.Insert(defRoute);
}
else
{
ApplicationService.DataProvider.Update(defRoute);
}
}
// 产品途程工序列表
//将原来的删除
string strSql = string.Format("delete tblitemroute2op where itemcode = '{0}' and routecode='{1}' and orgid = {2}", itemRoute.ItemCode, itemRoute.RouteCode, itemRoute.OrganizationID.ToString());
ApplicationService.DataProvider.CustomExecute(new SQLCondition(strSql));
strSql = "insert into tblitemroute2op (opid,routecode,opcode,opseq,opcontrol,idmergetype,idmergerule,muser,mdate,mtime,itemcode,orgid) ";
strSql += "select routecode||opcode||'{0}',routecode,opcode,opseq,opcontrol,'idmergetype_idmerge',1,muser,mdate,mtime,'{0}',{2} ";
strSql += "from tblroute2op where routecode='{1}' ";
strSql = string.Format(strSql, itemRoute.ItemCode, itemRoute.RouteCode, itemRoute.OrganizationID.ToString());
ApplicationService.DataProvider.CustomExecute(new SQLCondition(strSql));
iImportedCount++;
}
catch (Exception ex)
{
string strResult = ex.Message;
if (strResult == "$DATA_REPEAT_CANCEL") // 如果是重复数据，并设置终止
{
throw new Exception(strResult);
}
else if (strResult != "$Import_Unique_Data_Exist") // $Import_Unique_Data_Exist表示重复数据，设置忽略
{
if (Properties.Settings.Default.DataError == ImportDataEngine.OnErrorDeal.Cancel)
{
throw new Exception(strResult);
}
}
}
iRow++;
}
ApplicationService.DataProvider.CommitTransaction();
MessageBox.Show(UserControl.MutiLanguages.ParserMessage("$CycleImport_Success [" + iImportedCount.ToString() + "]"), this.MainForm.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
ApplicationService.DataProvider.RollbackTransaction();
MessageBox.Show(UserControl.MutiLanguages.ParserMessage(ex.Message), this.MainForm.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
/// <summary>
/// Read an Excel Sheet and Displays as it is Same
/// </summary>
/// <param name="objExcelSheet">Worksheet to be displayed</param>
/// <param name="lblErrText">If any Error Occurs that will be displayed</param>
/// <returns>Returns a Table Control that contains Worksheet Information</returns>
public Control ExcelSheetRead(Excel.Worksheet objExcelSheet, Label lblErrText)
{
int nMaxCol = ((Excel.Range)objExcelSheet.UsedRange).EntireColumn.Count;
int nMaxRow = ((Excel.Range)objExcelSheet.UsedRange).EntireRow.Count;
Table tblOutput = new Table();
TableRow TRow = null;
TableCell TCell = null;
string strSize = "";
int nSizeVal = 0;
bool bMergeCells = false;
int nMergeCellCount = 0;
int nWidth = 0;
if (objExcelSheet == null)
{
return((Control)tblOutput);
}
tblOutput.CellPadding = 0;
tblOutput.CellSpacing = 0;
tblOutput.GridLines = GridLines.Both;
try
{
for (int nRowIndex = 1; nRowIndex <= nMaxRow; nRowIndex++)
{
TRow = null;
TRow = new TableRow();
for (int nColIndex = 1; nColIndex <= nMaxCol; nColIndex++)
{
TCell = null;
TCell = new TableCell();
if (((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Value2 != null)
{
TCell.Text = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Text.ToString();
if (((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment != null)
{
TCell.ForeColor = System.Drawing.Color.Blue;
TCell.ToolTip = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment.Shape.AlternativeText;
}
else
{
TCell.ForeColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Color);
}
TCell.BorderWidth = 2;
TCell.Width = 140; //TCell.Width = 40;
//*
TCell.Font.Bold = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Bold;
TCell.Font.Italic = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Italic;
strSize = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Size.ToString();
nSizeVal = Convert.ToInt32(strSize);
TCell.Font.Size = FontUnit.Point(nSizeVal);
TCell.BackColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Interior.Color);
if ((bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeCells != false)
{
if (bMergeCells == false)
{
TCell.ColumnSpan = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount--;
bMergeCells = true;
}
else if (nMergeCellCount == 0)
{
TCell.ColumnSpan = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount = (int)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeArea.Columns.Count;
nMergeCellCount--;
}
}
else
{
bMergeCells = false;
}
TCell.HorizontalAlign = ExcelHAlign2DotNetHAlign(((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]));
TCell.VerticalAlign = ExcelVAlign2DotNetVAlign(((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]));
TCell.Height = Unit.Point(Decimal.ToInt32(Decimal.Parse((((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).RowHeight.ToString()))));
nWidth = Decimal.ToInt32(Decimal.Parse((((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).ColumnWidth.ToString())));
TCell.Width = Unit.Point(nWidth * nWidth);
//*/
}
else
{
if ((bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).MergeCells == false)
{
bMergeCells = false;
}
if (bMergeCells == true)
{
nMergeCellCount--;
continue;
}
TCell.Text = " ";
if (((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment != null)
{
TCell.ForeColor = System.Drawing.Color.Blue;
TCell.ToolTip = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Comment.Shape.AlternativeText;
}
else
{
TCell.ForeColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Color);
}
TCell.Font.Bold = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Bold;
TCell.Font.Italic = (bool)((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Italic;
strSize = ((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Font.Size.ToString();
nSizeVal = Convert.ToInt32(strSize);
TCell.Font.Size = FontUnit.Point(nSizeVal);
TCell.BackColor = ConvertExcelColor2DotNetColor(((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).Interior.Color);
TCell.Height = Unit.Point(Decimal.ToInt32(Decimal.Parse((((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).RowHeight.ToString()))));
nWidth = Decimal.ToInt32(Decimal.Parse((((Excel.Range)objExcelSheet.Cells[nRowIndex, nColIndex]).ColumnWidth.ToString())));
TCell.Width = Unit.Point(nWidth * nWidth);
}
//TCell.BorderStyle = BorderStyle.Solid;
//TCell.BorderWidth = Unit.Point(1);
//TCell.BorderColor = System.Drawing.Color.Gray;
TRow.Cells.Add(TCell);
}
tblOutput.Rows.Add(TRow);
}
}
catch (Exception ex)
{
lblErrText.Text = ex.ToString();
}
return((Control)tblOutput);
}
/// <summary>
/// Displaying a given Excel WorkSheet
/// </summary>
/// <param name="strFileName">The Filename to be selected</param>
/// <param name="strSheetRChartName">The Sheet or Chart Name to be Displayed</param>
/// <param name="bReadOnly">Specifies the File should be open in Read only mode,
/// If it is true then the File will be open ReadOnly</param>
/// <param name="lblErrorText">If any Error Occurs should be Displayed</param>
/// <param name="bIsChart">Specifies whether it is a Chart</param>
/// <returns>Returns Boolean Value the Method Succeded</returns>
public bool DisplayExcelSheet(string strFileName, string strSheetRChartName, bool bReadOnly, Label lblErrorText, bool bIsChart)
{
appOP.DisplayAlerts = false;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
Excel.Chart chart = null;
try
{
if (!bReadOnly)
{
// Write Mode Open
//workbook = appOP.Workbooks.Open(strFileName, 2, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
// For Optimal Opening
workbook = appOP.Workbooks.Open(strFileName, 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);
}
else
{
// Read Mode Open
//workbook = appOP.Workbooks.Open(strFileName, 2, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
// For Optimal Opening
workbook = appOP.Workbooks.Open(strFileName, 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);
}
// Reading of Excel File
if (bIsChart)
{
chart = (Excel.Chart)workbook.Charts[strSheetRChartName];
}
else
{
worksheet = (Excel.Worksheet)workbook.Sheets[strSheetRChartName];
}
// Reading the File Information Codes goes Here
if (bIsChart)
{
if (chart == null)
{
lblErrorText.Text = strSheetRChartName + " Chart is Not Available";
}
else
{
ExcelChartRead(chart, this.pnlBottPane);
}
}
else
{
if (worksheet == null)
{
lblErrorText.Text = strSheetRChartName + " Sheet is Available";
}
else
{
this.pnlBottPane.Controls.Add(ExcelSheetRead(worksheet, lblErrText));
}
}
if (!bReadOnly)
{
// Write Mode Close
workbook.Save();
workbook = null;
}
else
{
// Read Mode Close
workbook.Close(false, false, Type.Missing);
workbook = null;
}
}
catch (Exception expInterop)
{
lblErrText.Text = expInterop.ToString();
return(false);
}
finally
{
if (workbook != null)
{
if (!bReadOnly)
{
// Write Mode Close
workbook.Save();
workbook = null;
}
else
{
// Read Mode Close
workbook.Close(false, false, Type.Missing);
workbook = null;
}
}
appOP.DisplayAlerts = true;
}
return(true);
}
private void OutPut()
{
Excel.Application objApp = new Excel.ApplicationClass();
Excel.Workbooks objbooks = objApp.Workbooks;
Excel.Workbook objbook = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet objSheet = (Excel.Worksheet)objbook.Worksheets[1];//取得sheet1
Excel.Range range;
string filename = "";
try
{
//生成.xls文件完整路径名
filename = Server.MapPath("/RailExamBao/Excel/CompuerServerCount.xls");
if (File.Exists(filename.ToString()))
{
File.Delete(filename.ToString());
}
//将所得到的表的列名,赋值给单元格
objSheet.Cells[1, 1] = "站段名称";
objSheet.Cells[1, 2] = "服务器名称";
range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]);
range.Merge(0);
objSheet.Cells[1, 5] = "使用人次";
objSheet.Cells[1, 6] = "其他单位使用人次";
objSheet.Cells[1, 7] = "其他单位使用天数";
DataSet ds = (DataSet)ViewState["Grid"];
int i = 0;
//同样方法处理数据
foreach (DataRow dr in ds.Tables[0].Rows)
{
objSheet.Cells[2 + i, 1] = dr["Short_Name"].ToString();
objSheet.Cells[2 + i, 2] = dr["Computer_Server_Name"].ToString();
range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]);
range.Merge(0);
objSheet.Cells[2 + i, 5] = dr["使用人次"].ToString();
objSheet.Cells[2 + i, 6] = dr["其他单位使用人次"].ToString();
objSheet.Cells[2 + i, 7] = dr["其他单位使用天数"].ToString();
i++;
}
//不可见,即后台处理
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
}
//多sheet导出到excel
public int exportToExcel(DataTable dv, string fileName)
{
totalNum = dv.Rows.Count;
int sheetRows = 65535; //设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行
int sheetCount = (dv.Rows.Count - 1) / sheetRows + 1; //计算Sheet数
//必须加上垃圾回收机制
GC.Collect(); //垃圾回收
Excel.Application excel;
Excel.Workbook xBk;
Excel.Worksheet xSt = null;
excel = new Excel.Application();
xBk = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//定义循环中要使用的变量
int dvRowStart;
int dvRowEnd;
int rowIndex = 0;
int colIndex = 0;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//初始化Sheet中的变量
rowIndex = 1;
colIndex = 1;
//计算起始行
dvRowStart = sheetIndex * sheetRows;
dvRowEnd = dvRowStart + sheetRows - 1;
if (dvRowEnd > dv.Rows.Count - 1)
{
dvRowEnd = dv.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel.Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel.Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置Sheet的名称
xSt.Name = "Expdata";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//取得标题
foreach (DataColumn col in dv.Columns)
{
//设置标题格式
// xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置标题居中对齐
//xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;//设置标题为粗体
//填值，并进行下一列
excel.Cells[rowIndex, colIndex++] = col.ColumnName;
}
//取得表格中数量
int drvIndex;
for (drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
currentNum++;
DataRow row = dv.Rows[drvIndex];
//新起一行，当前单元格移至行首
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dv.Columns)
{
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
}
else if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
colIndex++;
}
}
//使用最佳宽度
//Excel.Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]);
//allDataWithTitleRange.Select();
//allDataWithTitleRange.Columns.AutoFit();
//allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框
}
//设置导出文件在服务器上的文件夹
//string exportDir="~/ExcelFile/";//注意:该文件夹您须事先在服务器上建好才行
//设置文件在服务器上的路径
//string absFileName = System.AppDomain.CurrentDomain.BaseDirectory + "tbAngle.xls";
xBk.SaveCopyAs(fileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
//返回写入服务器Excel文件的路径
return(1);
}
/// <summary>
/// 从excel文件获取数据表
/// </summary>
/// <param name="m_strFileName">excel文件名</param>
/// <param name="m_strMsg">异常信息</param>
/// <returns></returns>
public static DataTable m_mthShowValues(string m_strFileName, out string m_strMsg)
{
m_strMsg = string.Empty;
//最后一个单元格
string m_strArg = string.Empty;
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = null;
System.Data.DataTable m_dtResult = null;
object missing = System.Reflection.Missing.Value;
try
{
wb = excel.Workbooks.Open(m_strFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
excel.Visible = false;
Excel.Worksheet wbs = (Excel.Worksheet)wb.Worksheets.get_Item((object)1);
Excel.Range workingrangecells = null;
int m_intRowCount = 0;
for (int i = 1; i <= wbs.Rows.Count; i++)
{
workingrangecells = wbs.get_Range("A" + i.ToString(), System.Type.Missing);
if (workingrangecells.Value2 != null && workingrangecells.Value2.ToString() != string.Empty)
{
m_intRowCount++;
}
else
{
m_strArg = "H" + m_intRowCount.ToString();
break;
}
}
workingrangecells = wbs.get_Range(string.Format("A1:{0}", m_strArg), System.Type.Missing);
System.Array objArr = (System.Array)workingrangecells.Cells.Value2;
string[] strArr = ConvertToStringArray(objArr);
if (strArr == null || strArr.Length == 0)
{
return(null);
}
m_dtResult = new DataTable();
DataColumn dtColumn = null;
dtColumn = new DataColumn(strArr[0], typeof(string));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[1], typeof(string));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[2], typeof(string));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[3], typeof(string));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[4], typeof(string));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[5], typeof(double));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[6], typeof(double));
m_dtResult.Columns.Add(dtColumn);
dtColumn = new DataColumn(strArr[7], typeof(double));
m_dtResult.Columns.Add(dtColumn);
DataRow drTemp = null;
for (int i = 8; i < strArr.Length; i++)
{
drTemp = m_dtResult.NewRow();
drTemp[0] = strArr[i++];
drTemp[1] = strArr[i++];
drTemp[2] = strArr[i++];
drTemp[3] = strArr[i++];
drTemp[4] = strArr[i++];
drTemp[5] = Convert.ToDouble(strArr[i++]);
drTemp[6] = Convert.ToDouble(strArr[i++]);
drTemp[7] = Convert.ToDouble(strArr[i]);
m_dtResult.Rows.Add(drTemp);
}
stopExcel(excel);
}
catch (Exception ex)
{
stopExcel(excel);
m_dtResult = null;
m_strMsg = ex.Message.ToString();
}
return(m_dtResult);
}
private void ProcessSave()
{
try
{
String regName = CheckDuplicateName();
if (regName == "No duplicate name")
{
DateTime now = DateTime.Now;
regName = now.ToString("yyyyMMddHHmmss");
}
else
{
if (MessageBox.Show("There is already a query with the same name. Do you want to Replace?", "Name found", MessageBoxButtons.YesNo) == DialogResult.No)
{
return;
}
}
Excel.Range oneCell;
Excel.Worksheet hiddenWorksheet = (Excel.Worksheet)(AddinExpress.MSO.ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("BigFixExcelConnector");
// A17 is the name of the report
oneCell = hiddenWorksheet.get_Range("A17", "A17");
oneCell.Value = textBoxQueryName.Text.Trim();
(AddinExpress.MSO.ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = textBoxQueryName.Text.Trim();
// Name the sheet to be the same as the report name. Sheet name has to be less than 31 characters
String sheetName = "";
if (textBoxQueryName.Text.Trim().Length > 30)
{
sheetName = textBoxQueryName.Text.Trim().Substring(0, 28) + "..";
}
else
{
sheetName = textBoxQueryName.Text.Trim();
}
((Excel.Worksheet)(AddinExpress.MSO.ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet).Name = sheetName;
SetSettings("ExcelConnector\\SavedQueries\\" + regName, "Name", textBoxQueryName.Text.Trim());
for (int i = 1; i <= 19; i++)
{
// i == 1 is the Relevance Statement
// Since there are \n linefeeds that will mess up import and export, first turn them textual \n
/*
* if (i == 1)
* {
* oneCell = hiddenWorksheet.get_Range("A" + i.ToString(), "A" + i.ToString());
* oneCell.Value = oneCell.Value.ToString().Replace("\n", "\\n");
* }
* else
* {
* oneCell = hiddenWorksheet.get_Range("A" + i.ToString(), "A" + i.ToString());
* }
*/
oneCell = hiddenWorksheet.get_Range("A" + i.ToString(), "A" + i.ToString());
if (oneCell.Value == null)
{
SetSettings("ExcelConnector\\SavedQueries\\" + regName, "A" + i.ToString(), "");
}
else
{
if (i == 1)
{
SetSettings("ExcelConnector\\SavedQueries\\" + regName, "A" + i.ToString(), oneCell.Value.ToString().Replace("\n", "\\n"));
}
else
{
SetSettings("ExcelConnector\\SavedQueries\\" + regName, "A" + i.ToString(), oneCell.Value.ToString());
}
}
}
LoadSavedQueries();
// Highlight the row that the user just saved
for (int i = 0; i < dataGridViewSaveList.Rows.Count; i++)
{
if (dataGridViewSaveList.Rows[i].Cells[0].Value.ToString() == textBoxQueryName.Text.Trim())
{
dataGridViewSaveList.CurrentCell = dataGridViewSaveList.Rows[i].Cells[0];
}
}
labelStatus.ForeColor = System.Drawing.Color.DarkGreen;
labelStatus.Text = "Successfully saved";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error saving query definition to registry", MessageBoxButtons.OK, MessageBoxIcon.Error);
labelStatus.ForeColor = System.Drawing.Color.DarkRed;
labelStatus.Text = ex.Message;
}
}
/// <summary>
/// 插行（在指定WorkSheet指定行上面插入指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// 构造函数，新建一个工作簿
/// </summary>
public ExcelHelper()
{
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
app = new Excel.ApplicationClass();
//app.Visible = true; 不自动打开
afterTime = DateTime.Now;
//新建一个WorkBook
workBook = app.Workbooks.Add(Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
long rowNum = tmpDataTable.Rows.Count; //行数
int columnNum = tmpDataTable.Columns.Count; //列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = true; //不显示更改提示
m_xlApp.Visible = true; //false;//
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//worksheet.SetBackgroundPicture("d:\\26.jpg");
try
{
//查询条件
string rq = "日期:" + this.dateTimePicker1.Value.ToShortDateString() + " 到 " + this.dateTimePicker2.Value.ToShortDateString();
string ks = "";
string swhere = rq + ks;
int SumColCount = tmpDataTable.Columns.Count;
//for (int j = 0; j < tmpDataTable.Columns.Count; j++)
//{
// if (this.dataGridView1.Columns[j].Visible)
// {
// SumColCount = SumColCount + 1;
// m_xlApp.Cells[5, SumColCount] = "" + tmpDataTable.Columns[j].Caption;
// }
//}
//报表名称
string ss = TrasenFrame.Classes.Constant.HospitalName + "已打印检验单表";
m_xlApp.Cells[1, 1] = ss;
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Bold = true;
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Font.Size = 16;
//报表名称跨行居中
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).Select();
m_xlApp.get_Range(m_xlApp.Cells[1, 1], m_xlApp.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//报表条件
m_xlApp.Cells[3, 1] = swhere.Trim();
m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Font.Size = 10;
m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[3, SumColCount]).Select();
m_xlApp.get_Range(m_xlApp.Cells[3, 1], m_xlApp.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
if (rowNum > 65536) //单张Excel表格最大行数
{
long pageRows = 65535; //定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows); //导出数据生成的表单数
if (scount * pageRows < rowNum) //当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing); //添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption; //表头信息
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; //Sheet表最大化
range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[index + 5, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
range = worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[rowNum + 5, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
//workbook.Saved = true;
// workbook.SaveCopyAs(strFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (m_xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
//xlApp.Quit();
}
GC.Collect();
//EndReport();
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int columnIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void butexcel_Click(object sender, EventArgs e)
{
//try
//{
// #region 简单打印
// this.butexcel.Enabled = false;
// Excel.Application myExcel = new Excel.Application();
// myExcel.Application.Workbooks.Add(true);
// //查询条件
// string title = "";
// if (rdoall.Checked == true)
// title = title + "统计范围:门诊和住院";
// if (rdomz.Checked==true)
// title = title + "统计范围:门诊";
// if (rdozy.Checked == true)
// title = title + "统计范围:住院";
// string where1 = "";
// where1 =title+ " 药剂科室:" + cmbyjks.Text.Trim();
// where1 = where1 + " 日期:" + dtp1.Value.ToShortDateString() + " 到:" + dtp2.Value.ToShortDateString() + "";
// if (txtmb.Text.Trim()!="") where1=where1+" 统计模板:"+txtmb.Text.Trim();
// //写入行头
// DataTable tb = (DataTable)this.myDataGrid1.DataSource;
// int SumRowCount = tb.Rows.Count;
// int SumColCount = tb.Columns.Count;
// for (int j = 0; j < tb.Columns.Count; j++)
// {
// myExcel.Cells[5, 1 + j] = tb.Columns[j].ColumnName;
// }
// myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true;
// myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10;
// //逐行写入数据，
// for (int i = 0; i < tb.Rows.Count; i++)
// {
// for (int j = 0; j < tb.Columns.Count; j++)
// {
// myExcel.Cells[6 + i, 1 + j] = "" + tb.Rows[i][j].ToString();
// }
// }
// //设置报表表格为最适应宽度
// myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select();
// myExcel.get_Range(myExcel.Cells[6, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit();
// //加边框
// myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1;
// //报表名称
// myExcel.Cells[1, 1] = TrasenFrame.Classes.Constant.HospitalName + "住院汇总领药统计";
// myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true;
// myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16;
// //报表名称跨行居中
// myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select();
// myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
// //报表条件
// myExcel.Cells[3, 1] = where1.Trim();
// myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10;
// myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select();
// myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
// //最后一行为黄色
// myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19;
// //让Excel文件可见
// myExcel.Visible = true;
// this.butexcel.Enabled = true;
// #endregion
//}
//catch (System.Exception err)
//{
// this.butexcel.Enabled = true;
// MessageBox.Show(err.Message);
//}
try
{
DataView dv = (DataView)this.myDataGrid1.DataSource;
DataTable tb = dv.Table;
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string title = "";
if (rdoall.Checked == true)
{
title = title + "统计范围:门诊和住院";
}
if (rdomz.Checked == true)
{
title = title + "统计范围:门诊";
}
if (rdozy.Checked == true)
{
title = title + "统计范围:住院";
}
string where1 = "";
where1 = title + " 药剂科室:" + cmbyjks.Text.Trim();
where1 = where1 + " 日期:" + dtp1.Value.ToString() + " 到:" + dtp2.Value.ToString() + "";
if (txtmb.Text.Trim() != "")
{
where1 = where1 + " 统计模板:" + txtmb.Text.Trim();
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = "全院药品消耗情况统计";
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
objData[0, 0] = where1;
// 获取数据
//for (int i = 0; i <= tb.Rows.Count - 1; i++)
//{
// colIndex = 0;
// for (int j = 0; j <= tb.Columns.Count - 1; j++)
// {
// objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
// }
// Application.DoEvents();
//}
for (int i = 0; i <= dv.Table.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
objData[i + 2, colIndex++] = "" + dv[i][j].ToString();
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetName">WorkSheet名称</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell)
{
try
{
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
}
}
if (sheet != null)
{
for (int i = sheetCount; i >= 1; i--)
{
range1 = sheet.get_Range(startCell, endCell);
range2 = sheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
public void SelectSheet(object sheetname)
{
m_objSheet = (Excel.Worksheet)m_objBook.Worksheets[sheetname];
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, int mergeColumnIndex)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二维数组数据写入Excel
for (int i = sheetCount; i >= 1; i--)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
workSheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
//利用二维数组批量写入
int row = endRow - startRow;
string[,] ss = new string[row, colCount];
for (int j = 0; j < row; j++)
{
for (int k = 0; k < colCount; k++)
{
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}//end ArrayToExcel
private void bgW_luuexel_DoWork(object sender, DoWorkEventArgs e)
{
string filename = e.Argument.ToString();
System.Globalization.CultureInfo oldcul = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application exapp = new Excel.Application();
ThamsoKetqua kq = new ThamsoKetqua();
bgW_luuexel.ReportProgress(0);
try
{
exapp.Workbooks.Add(Missing.Value);
Excel.Workbook wkb = exapp.Workbooks.get_Item(1);
//wkb. = Excel.XlFileFormat.xlExcel9795;
Excel.Worksheet wks = (Excel.Worksheet)wkb.Worksheets.get_Item(1);
int offsetrow = 3;
//int offsetcol = 1;
int colnum = 2;
((Excel.Range)wks.Cells[offsetrow - 1, 1]).Value2 = "Stt";
string colname;
foreach (DataColumn dc in datasetDanhsachtontamung1.CHITIETTAMUNG.Columns)
{
if (dc.ColumnName == "maql" || dc.ColumnName == "mavaovien")
{
continue;
}
switch (dc.ColumnName)
{
case "mabn":
colname = "Mã BN";
break;
case "hoten":
colname = "Họ Tên";
break;
case "quyenso":
colname = "Quyển sổ";
break;
case "sobienlai":
colname = "Số biên lai";
break;
case "tiendong":
colname = "Tạm ứng";
break;
case "ngaydong":
colname = "Ngày đóng";
break;
case "hoantra":
colname = "Hoàn trả";
break;
case "ngaytra":
colname = "Ngày trả";
break;
case "done":
colname = "Hoàn tất";
break;
case "tongvienphi":
colname = "Viện phí";
break;
case "ngayravien":
colname = "Ngày ra viện";
break;
case "nguoithu":
colname = "Người thu";
break;
case "tenkp":
colname = "Khoa";
break;
default:
colname = dc.ColumnName;
break;
}
((Excel.Range)wks.Cells[offsetrow - 1, colnum]).Value2 = colname;
colnum++;
}
long numrow = 1;
foreach (DataRow dr in datasetDanhsachtontamung1.CHITIETTAMUNG.Rows)
{
colnum = 2;
foreach (DataColumn dc in datasetDanhsachtontamung1.CHITIETTAMUNG.Columns)
{
numrow = datasetDanhsachtontamung1.CHITIETTAMUNG.Rows.IndexOf(dr) + offsetrow;
((Excel.Range)wks.Cells[numrow, 1]).Value2 = numrow - offsetrow + 1;
if (dc.ColumnName == "maql" || dc.ColumnName == "mavaovien")
{
continue;
}
if (dr[dc].GetType() == typeof(DateTime))
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = ((DateTime)dr[dc]).ToString("dd/MM/yyyy");
}
else
if (dr[dc].GetType() == typeof(decimal) && ((decimal)dr[dc]) > 1000000000000)
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = "'" + dr[dc].ToString();
}
else
{
if (dc.ColumnName == "done")
{
if ((decimal)dr[dc] == 0)
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = "NO";
}
else
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = "YES";
}
}
else
{
((Excel.Range)wks.Cells[numrow, colnum]).Value2 = dr[dc];
}
}
colnum++;
}
int per = (int)((float)(numrow - offsetrow) / (float)datasetDanhsachtontamung1.CHITIETTAMUNG.Rows.Count * 90);
if (per % 5 == 0)
{
bgW_luuexel.ReportProgress(per);
}
}
((Excel.Range)wks.Cells[numrow + 2, 1]).Value2 = "Tổng tạm ứng:"; ((Excel.Range)wks.Cells[numrow + 2, 2]).Value2 = lb_tongcong.Text;
((Excel.Range)wks.Cells[numrow + 2, 4]).Value2 = "Tổng tồn:"; ((Excel.Range)wks.Cells[numrow + 2, 5]).Value2 = lb_tongtu.Text;
((Excel.Range)wks.Cells[numrow + 2, 7]).Value2 = "Tổng hoàn:"; ((Excel.Range)wks.Cells[numrow + 2, 8]).Value2 = lb_tonghoan.Text;
wkb.SaveAs(filename.Substring(0, filename.Length - 4), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
bgW_luuexel.ReportProgress(100);
exapp.Quit();
kq.err = true;
kq.filename = wkb.Path;
}
catch
{
kq.err = false;
}
finally
{
//if(exapp)
exapp.Quit();
Thread.CurrentThread.CurrentCulture = oldcul;
e.Result = kq;
}
}
/// <summary>
/// 将一个工作表拷贝到另一个工作表后面，并重命名
/// </summary>
/// <param name="srcSheetIndex">拷贝源工作表索引</param>
/// <param name="aimSheetIndex">参照位置工作表索引，新工作表拷贝在该工作表后面</param>
/// <param name="newSheetName"></param>
public void CopyWorkSheet(int srcSheetIndex, int aimSheetIndex, string newSheetName)
{
if (srcSheetIndex > this.WorkSheetCount || aimSheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
Excel.Worksheet srcSheet = (Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex);
Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
srcSheet.Copy(this.missing, aimSheet);
//重命名
workSheet = (Excel.Worksheet)aimSheet.Next; //获取新拷贝的工作表
workSheet.Name = newSheetName;
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void butexcel_Click(object sender, EventArgs e)
{
try
{
DataTable tb = null;
string ss = "";
tb = (DataTable)this.headerUnitView1.DataSource;
SystemCfg cfg2 = new SystemCfg(2);
string hospitalName = cfg2.Config;
ss = hospitalName + "药品费用统计";
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
string tjfs = "";
string tjks = "";
string tjlx = "";
tjfs = "统计方式:" + cmbtjfs.Text;
if (cmbtjfs.SelectedIndex != 1)
{
if (rdkd.Checked)
{
tjks = "统计科室：" + rdkd.Text;
}
else
{
tjks = "统计科室：" + rdgc.Text;
}
if (rbzy.Checked)
{
tjlx = "统计类型：" + rbzy.Text;
}
if (rbcy.Checked)
{
tjlx = "统计类型：" + rbcy.Text;
}
if (rbzs.Checked)
{
tjlx = "统计类型：" + rbzs.Text;
}
}
swhere = tjfs + " " + tjks + " " + tjlx + " " + " 记费日期从:" + dtpBjksj.Value.ToString() + " 到 " + dtpEjksj.Value.ToString();
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[3, 5], xlApp.Cells[3, colCount]);
range2.MergeCells = true;
range2.set_Value(Type.Missing, "药品费用");
range2.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 2, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (i >= 4)
{
objData[1, colIndex++] = "药品费用";
}
else
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
}
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[2, i] = tb.Columns[i].Caption;
if (i < 4)
{
Excel.Range range3 = xlSheet.get_Range(xlApp.Cells[3, i + 1], xlApp.Cells[4, i + 1]);
range3.MergeCells = true;
range3.HorizontalAlignment = Excel.Constants.xlCenter;
}
}
// 获取数据
objData[0, 0] = swhere;
string lastName1 = "";
string lastName2 = "";
int rowBegin = 5;
int rowEnd = 4;
int rowBegin1 = 5;
int rowEnd1 = 4;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
objData[i + 3, colIndex++] = "" + tb.Rows[i][j].ToString();
if (j == 0)
{
if (lastName1 != "" && !lastName1.Equals(tb.Rows[i][j].ToString()))
{
// Fun.DebugView(tb);
MergerCell(xlApp, xlSheet, rowBegin, rowEnd, 1);
rowBegin = rowEnd + 1;
rowEnd = rowEnd + 1;
lastName1 = tb.Rows[i][j].ToString();
}
else
{
rowEnd = rowEnd + 1;
}
}
if (j == 1)
{
if (lastName2 != "" && !lastName2.Equals(tb.Rows[i][j].ToString()))
{
MergerCell(xlApp, xlSheet, rowBegin1, rowEnd1, 2);
rowBegin1 = rowEnd1 + 1;
rowEnd1 = rowEnd1 + 1;
lastName2 = tb.Rows[i][j].ToString();
}
else
{
rowEnd1 = rowEnd1 + 1;
}
}
}
lastName1 = tb.Rows[i][0].ToString();
lastName2 = tb.Rows[i][1].ToString();
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]);
range.Value2 = objData;
//合并单元格
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
/// <summary>
/// 向指定文本框写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="textboxName">文本框名称</param>
/// <param name="text">要写入的文本</param>
public void SetTextBox(int sheetIndex, string textboxName, string text)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="rowBeginIndex">行开始</param>
/// <param name="rowEndIndex">行结束</param>
/// <param name="columnIndex"></param>
private void MergerCell(Excel.Application xlApp, Excel.Worksheet xlSheet, int rowBeginIndex, int rowEndIndex, int columnIndex)
{
Excel.Range range = xlSheet.get_Range(xlApp.Cells[rowBeginIndex, columnIndex], xlApp.Cells[rowEndIndex, columnIndex]);
range.MergeCells = true;
}
private void butexcel_Click(object sender, EventArgs e)
{
if (dataGridView1.DataSource == null)
{
return;
}
try
{
DataTable tb = null;
string ss = "";
string ksmc = "";
try
{
ksmc = TreeDept.Nodes[0].Nodes[0].Text;
}
catch { }
if (tabControl1.SelectedTab == tabPage1)
{
tb = (DataTable)this.dataGridView1.DataSource;
ss = "执行科室项目汇总表(" + ksmc + ")";
}
if (tabControl1.SelectedTab == tabPage2)
{
tb = (DataTable)this.dataGridView2.DataSource;
ss = "执行科室项目汇总表(" + ksmc + ")";
}
if (tabControl1.SelectedTab == tabPage3)
{
tb = (DataTable)this.dataGridView3.DataSource;
ss = "执行科室项目明细表(" + ksmc + ")";
}
if (tabControl1.SelectedTab == tabPage4)
{
tb = (DataTable)this.dataGridView4.DataSource;
ss = "执行科室项目汇总表(" + ksmc + ")";
}
if (tb == null || tb.Rows.Count == 0)
{
MessageBox.Show("没有数据");
return;
}
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
if (chksfrq.Checked == true)
{
swhere = " 记费日期从:" + dtp1.Value.ToString() + " 到 " + dtp2.Value.ToString();
}
if (chkqfrq.Checked == true)
{
swhere = " 确费日期从:" + dtpqrrq1.Value.ToString() + " 到 " + dtpqrrq2.Value.ToString();
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
//xlApp.ActiveCell.FormulaR1C1 = swhere;
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
//xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
//if (myDataGrid1.TableStyles[0].GridColumnStyles[j].Width>0)
//{
if (tb.Columns[j].Caption == "门诊号")
{
objData[i + 2, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
//}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
private void btndc_Click(object sender, EventArgs e)
{
if (this.dgvFpjl.Rows.Count == 0)
{
return;
}
DataTable tb = (DataTable)this.dgvFpjl.DataSource;
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= dgvFpjl.ColumnCount - 1; i++)
{
if (dgvFpjl.Columns[i].Visible == true && (dgvFpjl.Columns[i].Name != "COL_SELECTED" && dgvFpjl.Columns[i].Name != "COL_LZMC"))
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= dgvFpjl.ColumnCount - 1; i++)
{
if (dgvFpjl.Columns[i].Visible == true && (dgvFpjl.Columns[i].Name != "COL_SELECTED" && dgvFpjl.Columns[i].Name != "COL_LZMC"))
{
objData[0, colIndex++] = dgvFpjl.Columns[i].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= dgvFpjl.ColumnCount - 1; j++)
{
if (dgvFpjl.Columns[j].Visible == true && (dgvFpjl.Columns[j].Name != "COL_SELECTED" && dgvFpjl.Columns[j].Name != "COL_LZMC"))
{
objData[i + 1, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
/// <summary>
/// 插行（在指定行上面插入指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// Spread와 Chart의 내용을 엑셀로 Export함.시작위치는 내부에서 자동 계산.
/// </summary>
/// <param name="oSpread"> Spread컨트롤 명 </param>
/// <param name="oChartFx"> MSChart컨트롤 명 명 </param>
/// <param name="sFileTitle"> 화면 명 </param>
/// <param name="sHeadL"> 엑셀 머릿말(왼쪽) </param>
/// <param name="sHeadR"> 엑셀 머릿말(오른쪽) </param>
/// <param name="autofit">오토피트(자동너비계산)</param>
public void subMakeMsChartExcel(FpSpread oSpread, System.Windows.Forms.DataVisualization.Charting.Chart oMSChart,
string sFileTitle, string sHeadL, string sHeadR, bool bAutoFit)
{
DialogResult dlg;
bool IsMerge = true;
bool bResult = true;
int iSCol = 1;
int iSRow = 0;
int iMergeColSize = 0;
int iChartRow = 0;
int iTmp = 0;
int jTmp = 0;
try
{
if (oSpread.ActiveSheet.Rows.Count < 1)
{
MessageBox.Show("저장할 Data가 없습니다.", "Excel");
}
if (oSpread.ActiveSheet.Rows.Count > 600)
{
dlg = MessageBox.Show("데이타 건수가 많아 셀병합 작업시 속도가 느려집니다. 셀병합후 엑셀로 저장하시겠습니까?", "Excel Export", MessageBoxButtons.YesNo);
if (dlg == DialogResult.No)
{
IsMerge = false;
}
}
int iECol = 0;
int iERow = 0;
int iGridHeadCnt = 0;
if (sFileTitle == null)
{
sFileTitle = "";
}
if (sHeadL == null)
{
sHeadL = "";
}
if (sHeadR == null)
{
sHeadR = "";
}
xlApp = new Excel.Application();
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
xlSheet = (Excel.Worksheet)xlBook.ActiveSheet;
xlApp.Visible = false;
//xlApp.Visible = true;
xlApp.Cells.ClearContents();
xlApp.Cells.ClearFormats();
iGridHeadCnt = oSpread.ActiveSheet.ColumnHeaderRowCount;
DataTable dt = null;
String strSql = null;
strSql = " SELECT USER_DESC FROM RWEBUSRDEF WHERE USER_ID = '" + GlobalVariable.gsUserID + "' ";
dt = CmnFunction.oComm.GetFuncDataTable("DYNAMIC", strSql);
if (sHeadR == "")
{
sHeadR = "사 용 자 : " + dt.Rows[0][0].ToString() + " (" + GlobalVariable.gsUserID + ")";
}
else
{
sHeadR = sHeadR + "^사 용 자 : " + dt.Rows[0][0].ToString() + " (" + GlobalVariable.gsUserID + ")";
}
if (oMSChart == null)
{
//iSRow값을 내부에서 계산하게 수정.
if (sHeadL != "")
{
iTmp = sHeadL.Split('^').Length;
}
if (sHeadR != "")
{
jTmp = sHeadR.Split('^').Length;
}
iSRow = (iTmp > jTmp ? iTmp : jTmp) + 5 + 1;
//페이지 여백설정
subPageSetup(iSRow + iGridHeadCnt - 1, true);
}
else
{
//iSRow값을 내부에서 계산하게 수정.
if (sHeadL != "")
{
iTmp = sHeadL.Split('^').Length;
}
if (sHeadR != "")
{
jTmp = sHeadR.Split('^').Length;
}
// 머릿말 Row수 + 타이틀이 차지하는 Row수(5)
iChartRow = (iTmp > jTmp ? iTmp : jTmp) + 5 + 1;
//Chart복사
iSRow = CopyMSChart(oMSChart, iChartRow);
//페이지 여백설정
subPageSetup(iSRow + iGridHeadCnt - 1, false);
}
//Header항목이 2라인일 경우
iECol = CopySpView(oSpread, iGridHeadCnt, iSCol, iSRow, ref iMergeColSize); //Excel에서 마지막 Col의 위치값
ColSize = iECol;
iERow = oSpread.ActiveSheet.Rows.Count + iSRow + iGridHeadCnt - 1; //Excel에서 마지막 Row의 위치값
//Header의 라인 작성(Head부분의 색적용, Data라인 작성)
HeaderLine(iGridHeadCnt, iSCol, iSRow, iECol, iERow, iMergeColSize);
////각각의 머리글을 작성
setHeader(iSCol, iECol, sHeadL, sHeadR);
//파일의 타이틀을 작성
setTitle(iSCol, iECol, sFileTitle);
//Data부분 셀 Merge (600건 넘어가면 속도 작살임..)
if (IsMerge == true)
{
string[] tmpData = null;
int iRepeatRow = 0;
if (oSpread.ActiveSheet.Tag != null)
{
tmpData = oSpread.ActiveSheet.Tag.ToString().Split('^');
iRepeatRow = Convert.ToInt16(tmpData[1].ToString()) - 1;
}
DataMerge(iSCol, iSRow + iGridHeadCnt, iMrgECol, iERow, iMergeColSize, iRepeatRow);
}
xlSheet.get_Range(xlSheet.Cells[iSRow, iSCol], xlSheet.Cells[iSRow, iSCol]).Select();
oSpread.ActiveSheet.SetActiveCell(iSRow, iSCol);
if (bAutoFit == true)
{
//왼쪽 머릿말과 오른쪽 머리말 부분을 뺀 나머지만 AutoFit을 한다.
if (sHeadL == "" && sHeadR == "")
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 1], xlSheet.Cells[iSRow, iECol]).EntireColumn.AutoFit();
}
else if (sHeadL == "" && sHeadR != "")
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 1], xlSheet.Cells[iSRow, iECol - 1]).EntireColumn.AutoFit();
}
else if (sHeadL != "" && sHeadR == "")
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 2], xlSheet.Cells[iSRow, iECol]).EntireColumn.AutoFit();
}
else
{
xlSheet.get_Range(xlSheet.Cells[iSRow, 2], xlSheet.Cells[iSRow, iECol - 1]).EntireColumn.AutoFit();
}
}
bResult = true;
}
catch (Exception ex)
{
String errorMessage = "";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
CmnFunction.ShowMsgBox(errorMessage, "Error [" + ex.Source + "]", MessageBoxButtons.OK, 1);
bResult = false;
}
finally
{
// 사용자에게 저장 여부를 묻는다.
xlBook.Saved = false;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks);
if (bResult == false)
{
//Make Excel application close.
xlApp.DisplayAlerts = false;
//xlBook.Saved = true;
xlBooks.Close(); // ***** 이 함수를 호출 하지 않으면 작업프로세스에 EXCEL.EXE가 죽지 않고 계속 남아 있음 *****
xlApp.Quit();
}
else
{
// 모든 엑셀의 경고메시지가 나타나도록 한다.
//xlApp.DisplayAlerts = true;
//Make Excel visible and give the user control.
xlApp.Visible = true;
xlApp.UserControl = true;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();
System.Windows.Forms.Cursor.Current = Cursors.Default;
}
} //public void subMakeExcel(FpSpread oSpread, Chart oChartFx, string sFileTitle, string sHeadL, string sHeadR)
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
for (int n = 1; n <= this.WorkSheetCount; n++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// Create excel contains all dependent values for creating select list
/// </summary>
public void generateFormValDependList(string fileName)
{
NformRepository repo = NformRepository.Instance;
string excelPath = "keywordscripts/" + fileName + ".xlsx";
excelPath = System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(),
excelPath);
LxXlsOper opXls = new LxXlsOper();
// opXls.open("c:/test.xlsx");
opXls.create(excelPath);
Excel.Workbook xBook = opXls.xBook;
Excel.Worksheet xSheet = opXls.xSheet;
Excel.Worksheet xSheetActions = (Excel.Worksheet)xBook.Sheets[2];;
Type objType = repo.GetType();
int iFormRow = 1;
int iComponentRow = 1;
int iComponentCol = 2;
int iActionsCol = 1;
object obj = repo;
PropertyInfo pi = objType.GetProperty("NFormApp");
obj = pi.GetValue(repo, null);
objType = obj.GetType();
PropertyInfo[] piArrLev1 = objType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo piLev1 in piArrLev1)
{
if (piLev1.Name.CompareTo("UseCache") == 0)
{
continue;
}
object objLogicGroup = piLev1.GetValue(obj, null);
objType = objLogicGroup.GetType();
PropertyInfo[] piArrLev2 = objType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo piLev2 in piArrLev2)
{
if (piLev2.Name.Substring(0, 4).CompareTo("Form") != 0)
{
continue;
}
opXls.writeCell(iFormRow++, 1, piLev2.Name);
// MessageBox.Show(objLogicGroup.ToString());
object objWindows = piLev2.GetValue(objLogicGroup, null);
objType = objWindows.GetType();
PropertyInfo[] piArrComp = objType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo piCom in piArrComp)
{
object objComponetInfo = null;
if (piCom.Name == "Self" || piCom.Name == "SelfInfo" || piCom.Name == "BasePath" ||
piCom.Name == "ParentFolder" || piCom.Name == "AbsoluteBasePath" ||
piCom.Name == "SearchTimeout" || piCom.Name == "UseCache")
{
continue;
}
// if(piCom.Name.Length > 8)
// {
// if(piCom.Name.Substring(piCom.Name.Length-4,4) == "Info"
// && piCom.Name.Substring(piCom.Name.Length-8,4) != "Info")
// continue;
// } else if(piCom.Name.Length > 4 && piCom.Name.Substring(piCom.Name.Length-4,4) == "Info")
// {
// objComponetInfo = piCom.GetValue(objWindows,null);
// //MessageBox.Show(objComponetInfo.GetType().ToString());
//
// PropertyInfo rxPath = objComponetInfo.GetType().GetProperty("AbsolutePath");
// RxPath componentRxPath = (RxPath)rxPath.GetValue(objComponetInfo,null);
// string strComponentRxPath = componentRxPath.ToString();
// //strComponentRxPath.LastIndexOf("[");
// //strComponentRxPath.LastIndexOf("/");
// string strComponentType = strComponentRxPath.Substring(
// strComponentRxPath.LastIndexOf("/")+1,strComponentRxPath.LastIndexOf("[") - strComponentRxPath.LastIndexOf("/") - 1);
//
// MessageBox.Show(componentRxPath.ToString() + " " + strComponentType);
// continue;
// }
if (piCom.Name.Length > 4 && piCom.Name.Substring(piCom.Name.Length - 4, 4) == "Info")
{
objComponetInfo = piCom.GetValue(objWindows, null);
PropertyInfo rxPath = objComponetInfo.GetType().GetProperty("AbsolutePath");
RxPath componentRxPath = (RxPath)rxPath.GetValue(objComponetInfo, null);
string strComponentRxPath = componentRxPath.ToString();
string strComponentType = "";
if (strComponentRxPath.LastIndexOf("[") - strComponentRxPath.LastIndexOf("/") > 0)
{
strComponentType = strComponentRxPath.Substring(
strComponentRxPath.LastIndexOf("/") + 1, strComponentRxPath.LastIndexOf("[") - strComponentRxPath.LastIndexOf("/") - 1);
}
else
{
strComponentType = strComponentRxPath.Substring(strComponentRxPath.LastIndexOf("/") + 1, strComponentRxPath.Length - strComponentRxPath.LastIndexOf("/") - 1);
}
string comName = piCom.Name.Substring(0, piCom.Name.Length - 4);
if (strComponentType == "button")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "text")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "InputKeys";
xSheetActions.Cells[3, iActionsCol] = "SetTextValue";
xSheetActions.Cells[4, iActionsCol] = "Exists";
xSheetActions.Cells[5, iActionsCol] = "NotExists";
xSheetActions.Cells[6, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[7, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "combobox")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Select";
xSheetActions.Cells[3, iActionsCol] = "SetTextValue";
xSheetActions.Cells[4, iActionsCol] = "Exists";
xSheetActions.Cells[5, iActionsCol] = "NotExists";
xSheetActions.Cells[6, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[7, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "treeitem")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "menuitem")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "tabpage")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "indicator")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "menubar")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "list")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "ClickItem";
xSheetActions.Cells[3, iActionsCol] = "DoubleClickItem";
xSheetActions.Cells[4, iActionsCol] = "MoveTo";
xSheetActions.Cells[5, iActionsCol] = "Exists";
xSheetActions.Cells[6, iActionsCol] = "NotExists";
xSheetActions.Cells[7, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[8, iActionsCol] = "VerifyToolTips";
xSheetActions.Cells[9, iActionsCol] = "VerifyContains";
xSheetActions.Cells[10, iActionsCol] = "VerifyNotContains";
}
else if (strComponentType == "table")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "ClickItem";
xSheetActions.Cells[3, iActionsCol] = "DoubleClickItem";
xSheetActions.Cells[4, iActionsCol] = "ClickCell";
xSheetActions.Cells[5, iActionsCol] = "InputKeys";
xSheetActions.Cells[6, iActionsCol] = "MoveTo";
xSheetActions.Cells[7, iActionsCol] = "Exists";
xSheetActions.Cells[8, iActionsCol] = "NotExists";
xSheetActions.Cells[9, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[10, iActionsCol] = "VerifyToolTips";
xSheetActions.Cells[11, iActionsCol] = "VerifyContains";
xSheetActions.Cells[12, iActionsCol] = "VerifyNotContains";
}
else if (strComponentType == "tree")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "ClickItem";
xSheetActions.Cells[3, iActionsCol] = "DoubleClickItem";
xSheetActions.Cells[4, iActionsCol] = "MoveTo";
xSheetActions.Cells[5, iActionsCol] = "Expand";
xSheetActions.Cells[6, iActionsCol] = "Collapse";
xSheetActions.Cells[7, iActionsCol] = "Exists";
xSheetActions.Cells[8, iActionsCol] = "NotExists";
xSheetActions.Cells[9, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[10, iActionsCol] = "VerifyToolTips";
xSheetActions.Cells[11, iActionsCol] = "VerifyContains";
xSheetActions.Cells[12, iActionsCol] = "VerifyNotContains";
}
else if (strComponentType == "listitem")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "DoubleClick";
xSheetActions.Cells[3, iActionsCol] = "Exists";
xSheetActions.Cells[4, iActionsCol] = "NotExists";
xSheetActions.Cells[5, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[6, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "checkbox")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Set";
xSheetActions.Cells[3, iActionsCol] = "Clear";
xSheetActions.Cells[4, iActionsCol] = "Exists";
xSheetActions.Cells[5, iActionsCol] = "NotExists";
xSheetActions.Cells[6, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[7, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "cell")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "DoubleClick";
xSheetActions.Cells[3, iActionsCol] = "InputKeys";
xSheetActions.Cells[4, iActionsCol] = "SetTextValue";
xSheetActions.Cells[5, iActionsCol] = "CellContentClick";
xSheetActions.Cells[6, iActionsCol] = "Exists";
xSheetActions.Cells[7, iActionsCol] = "NotExists";
xSheetActions.Cells[8, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[9, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "datetime")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "InputKeys";
xSheetActions.Cells[3, iActionsCol] = "Exists";
xSheetActions.Cells[4, iActionsCol] = "NotExists";
xSheetActions.Cells[5, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[6, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "radiobutton")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "row")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "picture")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "slider")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "Exists";
xSheetActions.Cells[3, iActionsCol] = "NotExists";
xSheetActions.Cells[4, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[5, iActionsCol] = "VerifyToolTips";
}
else if (strComponentType == "container")
{
xSheetActions.Cells[1, iActionsCol] = "Click";
xSheetActions.Cells[2, iActionsCol] = "RightClick";
xSheetActions.Cells[3, iActionsCol] = "Exists";
xSheetActions.Cells[4, iActionsCol] = "NotExists";
xSheetActions.Cells[5, iActionsCol] = "VerifyProperty";
xSheetActions.Cells[6, iActionsCol] = "VerifyToolTips";
}
else
{
MessageBox.Show(comName.ToString() + " " + strComponentType);
}
Excel.Range rmgForAction = (Excel.Range)xSheetActions.Cells[1, iActionsCol];
rmgForAction.EntireColumn.Name = piLev2.Name + comName;
iActionsCol++;
// MessageBox.Show(comName.ToString() + " " + strComponentType);
}
else
{
opXls.writeCell(iComponentRow++, iComponentCol, piCom.Name);
}
}
if (piLev2.Name == "FormManaged_Devices")
{
opXls.writeCell(iComponentRow++, iComponentCol, "Add_Device");
opXls.writeCell(iComponentRow++, iComponentCol, "Del_Device");
}
iComponentRow = 1;
iComponentCol++;
}
}
opXls.writeCell(iFormRow++, 1, "Pause");
for (int iPuaseNum = 1; iPuaseNum <= 60; iPuaseNum++)
{
opXls.writeCell(iComponentRow++, iComponentCol, iPuaseNum.ToString());
}
opXls.writeCell(iFormRow++, 1, "VerifyTxtfileValues");
opXls.writeCell(iFormRow++, 1, "VerifyExcelfileValues");
opXls.writeCell(iFormRow++, 1, "SendCommandToSimulator");
xSheet.Name = "Form DataValDepend";
xSheetActions.Name = "Action DataValDepend";
Excel.Range rmg = (Excel.Range)xSheet.Cells[1, 1];
rmg.EntireColumn.Name = "Forms";
int countRows = 1;
string formName = opXls.readCell(countRows, 1);
while (formName != "")
{
rmg = (Excel.Range)xSheet.Cells[1, countRows + 1];
rmg.Name = formName;
rmg.EntireColumn.Name = formName + "Col";
formName = opXls.readCell(++countRows, 1);
}
opXls.close();
}
/// <summary>
/// 复制行（在指定WorkSheet指定行下面复制指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int sheetIndex, int rowIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void butexcel_Click(object sender, EventArgs e)
{
try
{
if (this.dataGridView1.Rows.Count < 1)
{
return;
}
DataTable tb = (DataTable)this.dataGridView1.DataSource;
this.butprint_pos.Enabled = false;
this.Cursor = PubStaticFun.WaitCursor();
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
if (chkbkrq.Checked == true)
{
swhere = " 办卡日期从:" + this.startTjrq.Value.ToString() + " 到 " + this.endTjrq.Value.ToString();
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = "银医诊疗卡办卡数统计";
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
//if (myDataGrid1.TableStyles[0].GridColumnStyles[j].Width>0)
//{
if (tb.Columns[j].Caption == "门诊号")
{
objData[i + 2, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
//}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
this.butprint_pos.Enabled = true;
}
catch (System.Exception err)
{
this.butprint_pos.Enabled = true;
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
/// <summary>
/// 复制列（在指定WorkSheet指定列右边复制指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++)
{
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void butprint_pos_Click(object sender, EventArgs e)
{
Save();
String ExcelFile = Application.StartupPath + "\\门诊预交金收支情况统计.xls";
//Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xlApp.Visible = true;
//object oMissing = System.Reflection.Missing.Value;
//Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(ExcelFile, 0, true, 5, oMissing, oMissing, true, 1, oMissing, false, false, oMissing, false, oMissing, oMissing);
//Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[1];
//xlWorksheet.PrintPreview(null);
//xlApp.Visible = false;
//xlWorksheet = null;
//xlWorksheet.PrintOut(1, 1, 1, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value);
//return;
Excel.Application xlsApp = new Excel.Application();
//xlsApp.Visible = true;
Excel.Workbooks xlsWbs = xlsApp.Workbooks;
Excel.Workbook xlsWb = xlsWbs.Open(
ExcelFile, Missing.Value, true, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
Excel.Worksheet xlsWs = (Excel.Worksheet)xlsWb.Worksheets[1];
//if (checkBox1.Checked)
// xlsWs.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//页面方向横向
//else
xlsWs.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; //页面方向横向
//objsheet.PageSetup.Zoom = 75;//打印时页面设置,缩放比例
//objsheet.PageSetup.TopMargin = 0; //上边距为0
//objsheet.PageSetup.BottomMargin = 0; //下边距为0
//objsheet.PageSetup.LeftMargin = 0; //左边距为0
//objsheet.PageSetup.RightMargin = 0; //右边距为0
//objsheet.PageSetup.CenterHorizontally = true;//水平居中
//PaperSize p = null;
//PrintDialog pd = new PrintDialog();
//foreach (PaperSize ps in pd.PrinterSettings.PaperSizes)
//{
// if (ps.PaperName.Equals("A4 Plus"))
// p = ps;
//}
//xlsWs.PageSetup.PaperSize = (Excel.XlPaperSize)p.RawKind;
//使excel可见
xlsApp.Visible = true;
//预览
xlsWb.PrintPreview(false);
//保存后退出，并释放资源
xlsApp.DisplayAlerts = false;
// xlsWb.Save();
// xlsWb.SaveAs(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWb);
xlsWs = null;
xlsWb = null;
xlsApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
xlsApp = null;
//xlsApp.Visible = true;
//打印
//xlsWb.PrintOut(1, 1, 1, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value);
//保存后退出，并释放资源
// xlsApp.DisplayAlerts = false;
// xlsWb.Save();
//xlsWb.SaveAs(ExcelFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlsWs = null;
xlsWb = null;
//xlsApp.Quit();
xlsApp = null;
GC.Collect();
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int sheetIndex, int columnIndex, int count)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++)
{
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
protected void ExportExcel(DataTable dt, string table)
{
if (dt == null || dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
//Excel.Workbook workbook = xlApp.Workbooks.Open(table, 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.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet; //第一个sheet页
worksheet.Name = "武汉市公费"; //这里修改sheet名称
try
{
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
//range.NumberFormat = "0.00";
}
DataTable dtDec = DecCol();
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
string strValue = dt.Rows[r][i].ToString();
decimal dm = 0M;
if (dtDec.Columns.Contains(dt.Columns[i].ColumnName))
{
((Excel.Range)worksheet.Cells[r + 2, i + 1]).NumberFormat = "0.00";
worksheet.Cells[r + 2, i + 1] = strValue;
}
else
{
worksheet.Cells[r + 2, i + 1] = "'" + strValue;
//worksheet.Cells[r + 2, i + 1] = strValue;
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}
catch (Exception ex)
{
}
finally
{
workbook.Saved = true;
if (System.IO.File.Exists(table))
{
System.IO.File.Delete(table);
}
workbook.SaveCopyAs(table);
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
xlApp.Quit();
xlApp = null;
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range1 = workSheet.get_Range(startCell, endCell);
range2 = workSheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
/// <summary>
/// Export data table to Excel
/// </summary>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public void ExportToExcel(DataView view, DataGridTableStyle grdStyle, int startRow, int startCol, Excel.Worksheet sheet)
{
clsCommon common = new clsCommon();
string[] headers = null;
int[] indexes = null;
common.GetExportInfo(view, grdStyle.GridColumnStyles, ref headers, ref indexes);
ExportToExcel(view, headers, indexes, startRow, startCol, sheet);
}
private void Dispose()
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
this.KillExcelProcess();
}//end Dispose
/// <summary>
/// Export data table to Excel
/// </summary>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public void ExportToExcel(DataView view, string[] headers, int[] indexes, int startRow, int startCol, Excel.Worksheet sheet)
{
Excel.Range range = null;
object obj = null;
int i = 0;
int j = 0;
DataColumnCollection cols = view.Table.Columns;
//DataRowCollection rows = dt.Rows;
DataView rows = view;
int rowCout = rows.Count;
int colCount = indexes.Length;
for (i = 0; i < rowCout; i++)
{
for (j = 0; j < colCount; j++)
{
obj = rows[i][indexes[j]];
sheet.get_Range(COL_NAME[j + startCol] + (i + startRow + EXCEL_COL_SPACE), missing).NumberFormat = "@";
sheet.get_Range(COL_NAME[j + startCol] + (i + startRow + EXCEL_COL_SPACE), missing).Value2 = obj.ToString();
}
}
//Export header
colCount = headers.Length;
for (i = 0; i < colCount; i++)
{
range = sheet.get_Range(COL_NAME[i + startCol] + (startRow + 1), missing);
range.Font.Bold = true;
range.Value2 = headers[i];
range.EntireColumn.AutoFit();
}
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(int sheetIndex, string[,] arr, int top, int left)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
// 改变当前工作表
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}//end ArrayToExcel
private void butexcel_Click(object sender, EventArgs e)
{
try
{
DataTable tb = (DataTable)this.myDataGrid1.DataSource;
// 创建Excel对象 --LeeWenjie 2006-11-29
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= myDataGrid1.TableStyles[0].GridColumnStyles.Count - 1; i++)
{
if (myDataGrid1.TableStyles[0].GridColumnStyles[i].Width > 0)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = _chineseName + "一览表";
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= myDataGrid1.TableStyles[0].GridColumnStyles.Count - 1; i++)
{
if (myDataGrid1.TableStyles[0].GridColumnStyles[i].Width > 0)
{
objData[0, colIndex++] = myDataGrid1.TableStyles[0].GridColumnStyles[i].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= myDataGrid1.TableStyles[0].GridColumnStyles.Count - 1; j++)
{
if (myDataGrid1.TableStyles[0].GridColumnStyles[j].Width > 0)
{
if (myDataGrid1.TableStyles[0].GridColumnStyles[j].HeaderText == "品名" || myDataGrid1.TableStyles[0].GridColumnStyles[j].HeaderText == "商品名" || myDataGrid1.TableStyles[0].GridColumnStyles[j].HeaderText == "规格")
{
objData[i + 1, colIndex++] = "'" + tb.Rows[i][j].ToString().Trim();
}
else
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString().Trim();
}
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}//end ArrayToExcel
#endregion
#region WorkSheet Methods
/// <summary>
/// 改变当前工作表
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
public void ChangeCurrentWorkSheet(int sheetIndex)
{
//若指定工作表索引超出范围，则不改变当前工作表
if (sheetIndex < 1)
return;
if (sheetIndex > this.WorkSheetCount)
return;
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
DataTable tb = (DataTable)this.dgvgqyp.DataSource;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= dgvgqyp.Columns.Count - 1; i++)
{
if (dgvgqyp.Columns[i].Width > 0)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
//查询条件
string bz = "";
bz = bz + "药剂科室:" + cmbyjks.Text.Trim() + " 入库日期：从" + dtpshrqB.Value.ToString("yyyy-MM-dd") + " 到 " + dtpshrqE.Value.ToString("yyyy-MM-dd");
bz = bz + " 失效日期：" + dtpXQ.Value.ToString("yyyy-MM-dd");
string swhere = " " + bz;
// 设置条件
Excel.Range rangeT = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
rangeT.MergeCells = true;
object[,] objDataT = new object[1, 1];
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
objDataT[0, 0] = swhere;
range.Value2 = objDataT;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= dgvgqyp.Columns.Count - 1; i++)
{
if (dgvgqyp.Columns[i].Width > 0)
{
objData[0, colIndex++] = dgvgqyp.Columns[i].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= dgvgqyp.Columns.Count - 1; j++)
{
if (dgvgqyp.Columns[j].Width > 0)
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Select();
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
/// <summary>
/// 在指定名称的工作表后面拷贝指定个数的该工作表的副本，并重命名
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="sheetCount">工作表个数</param>
public void CopyWorkSheets(string sheetName, int sheetCount)
{
try
{
Excel.Worksheet sheet = null;
int sheetIndex = 0;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null)
{
for (int i = sheetCount; i >= 1; i--)
{
sheet.Copy(this.missing, sheet);
}
//重命名
for (int i = sheetIndex; i <= sheetIndex + sheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
}
}
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void butexcel_Click(object sender, EventArgs e)
{
try
{
DataTable tb = null;
string ss = "";
tb = (DataTable)this.dataGridView1.DataSource;
ss = this._chineseName;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
colCount = colCount + 1;
}
//查询条件
string swhere = "";
swhere = " 记费日期从:" + dtpBjksj.Value.ToString() + " 到 " + dtpEjksj.Value.ToString();
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ss;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
objData[1, colIndex++] = tb.Columns[i].Caption;
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
/// <summary>
/// 根据名称删除工作表
/// </summary>
/// <param name="sheetName"></param>
public void DeleteWorkSheet(string sheetName)
{
try
{
Excel.Worksheet sheet = null;
//找到名称位sheetName的工作表
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
{
sheet = workSheet;
}
}
if (sheet != null)
{
sheet.Delete();
}
else
{
this.KillExcelProcess();
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e)
{
this.KillExcelProcess();
throw e;
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
DataTable tb = (DataTable)this.dataGridView1.DataSource;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dataGridView1.Columns[tb.Columns[i].ColumnName].Width > 0)
{
colCount = colCount + 1;
}
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = this.Text;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
//查询条件
string bz = "";
bz = "累计DDD数:" + txtDDD.Text + " 出院人数:" + txtoutPatient.Text + " 平均住院天数:" + txtAvgOutpatient.Text +
" 同期收治患者人天数:" + txtpatientCount.Text;
string swhere = "抗菌药物使用强度:" + txtqiangdu.Text +
"在床使用抗菌药物人数:" + txtkjywrc.Text + " 在床人数:" + txtzcrs.Text.Trim() + " 抗菌药物使用率:" + txtksssyl.Text;
// 设置条件
Excel.Range rangeT = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
rangeT.MergeCells = true;
object[,] objDataT = new object[1, 1];
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
objDataT[0, 0] = bz;
rangeT.Value2 = objDataT;
rangeT = xlSheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[3, colCount]);
rangeT.MergeCells = true;
object[,] objDataT2 = new object[1, 1];
objDataT2[0, 0] = swhere;
rangeT.Value2 = objDataT2;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dataGridView1.Columns[tb.Columns[i].ColumnName].Width > 0)
{
objData[0, colIndex++] = dataGridView1.Columns[tb.Columns[i].ColumnName].HeaderText;
}
}
// 获取数据
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
if (dataGridView1.Columns[tb.Columns[j].ColumnName].Width > 0)
{
objData[i + 1, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Select();
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[4, 1], xlApp.Cells[RowCount + 4, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.Cursor = Cursors.Arrow;
}
}
/// <summary>
/// 向指定文本框写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="textboxName">文本框名称</param>
/// <param name="text">要写入的文本</param>
public void SetTextBox(string textboxName, string text)
{
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
}
}
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
dt.Columns.Remove("id");
dt.Columns.Remove("year");
dt.Columns.Remove("month");
dt.Columns.Remove("jobflowid");
dt.AcceptChanges();
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行
range.Merge(0);
worksheet.Cells[1, 1] = this.biaoti.InnerText;
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号"; //加入序号列
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];
}
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (i == 0)//序号列宽度设为自动调整
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0) //增加序号
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString();
}
else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列
{
worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日");
}
else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
range.NumberFormat = "#,##0.00";
}
else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列
{
if (dt.Rows[r][c - 1].ToString() == "1")
{
worksheet.Cells[r + 3, c + 1] = "已支付";
}
else
{
worksheet.Cells[r + 3, c + 1] = "未支付";
}
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
//设置样式
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
//设置合计那一行
range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString());
range.Font.ColorIndex = 41;
//range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Font.Size = 10;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
range.EntireColumn.AutoFit(); //自动调整列宽
worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:"; //合计那一行的第一列
if (dt.Rows.Count == 0)
{
worksheet.Cells[3 + dt.Rows.Count, 7] = 0;
}
else
{
worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")";
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
System.Windows.Forms.Application.DoEvents();
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
/// <summary>
/// 向文本框写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
public void SetTextBoxes(Hashtable ht)
{
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht)
{
try
{
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch
{
this.KillExcelProcess();
throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
}
}
}
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对每个WorkSheet操作
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int columnIndex, int beginRowIndex, int endRowIndex)
{
if (endRowIndex - beginRowIndex < 1)
return;
for (int i = 1; i <= this.WorkSheetCount; i++)
{
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
for (int j = beginRowIndex; j <= endRowIndex; j++)
{
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(int sheetIndex, Hashtable ht)
{
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (ht.Count == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht)
{
try
{
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch
{
this.KillExcelProcess();
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int sheetIndex, int columnIndex, int beginRowIndex, int endRowIndex)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (endRowIndex - beginRowIndex < 1)
return;
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int j = beginRowIndex; j <= endRowIndex; j++)
{
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2)
{
++count;
}
else
{
if (count > 0)
{
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
private void 导出EXCELToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
DataView dv;
DataTable tb;
System.Windows.Forms.DataGridView dgv;
if (tabControl1.SelectedTab == tabPage1)
{
dgv = dgvyjsq;
dv = (DataView)dgv.DataSource;
tb = dv.Table;
}
else
{
dgv = dataGridView2;
tb = (DataTable)dgv.DataSource;
}
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("Excel无法启动");
return;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 列索引，行索引，总列数，总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = 0;
int RowCount = tb.Rows.Count + 1;
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
colCount = colCount + 1;
}
}
//查询条件
string swhere = "";
if (rdosqrq.Checked == true)
{
swhere = " 申请日期从:" + dtpsqrq1.Value.ToString() + " 到 " + dtpsqrq2.Value.ToString();
}
else
{
swhere = " 确认日期从:" + dtpqrrq1.Value.ToString() + " 到 " + dtpqrrq2.Value.ToString();
}
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = "医技确认情况表(" + InstanceForm.BCurrentDept.DeptName + ")";
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 设置条件
Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
range1.MergeCells = true;
//xlApp.ActiveCell.FormulaR1C1 = swhere;
//xlApp.ActiveCell.Font.Size = 20;
//xlApp.ActiveCell.Font.Bold = true;
//xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount + 1];
// 获取列标题
for (int i = 0; i <= tb.Columns.Count - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objData[1, colIndex++] = dgv.Columns[i].HeaderText;
}
}
// 获取数据
objData[0, 0] = swhere;
for (int i = 0; i <= tb.Rows.Count - 1; i++)
{
colIndex = 0;
for (int j = 0; j <= tb.Columns.Count - 1; j++)
{
if (dgv.Columns[j].Visible == true)
{
if (tb.Columns[j].Caption == "住院号")
{
objData[i + 2, colIndex++] = "'" + tb.Rows[i][j].ToString();
}
else
{
objData[i + 2, colIndex++] = "" + tb.Rows[i][j].ToString();
}
}
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]);
range.Value2 = objData;
//
xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
//设置报表表格为最适应宽度
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Select();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Columns.AutoFit();
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, colCount]).Font.Size = 9;
xlApp.Visible = true;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void Main(string[] args)
{
Console.WriteLine("**********************************************");
Console.WriteLine("*************** INICI *********************");
Console.WriteLine("******* ACTUALITZACIO DE DOCUMENTS ***********");
Console.WriteLine("**********************************************");
Console.WriteLine("**********************************************");
string configPath = AppDomain.CurrentDomain.BaseDirectory + "\\config.ini";
PropertiesUtility properties = new PropertiesUtility();
properties.LoadProperties(configPath);
string timeoutStr = properties.GetProperty("timeout");
Console.Write("Espera per document: " + timeoutStr + " millisegons");
Console.WriteLine();
int timeout = Int32.Parse(timeoutStr);
string pathList = properties.GetProperty("pathlist");
Console.WriteLine("Llista ubicada a: " + pathList + "Lista.xlsx");
string pathExcels = properties.GetProperty("pathexcels");
Console.WriteLine("Excels ubicats a: " + pathExcels + "*.*");
Excel.Application application = new Excel.Application();
string path = pathList + "Lista.xlsx";
Excel.Workbook lista = application.Workbooks.Open(path);
Excel.Worksheet mySheet = (Excel.Worksheet)lista.Sheets["Hojas"];
string[] terms = new string[400];
int numTerms = 0;
Excel.Range dataRange = null;
for (int row = 1; row < mySheet.Rows.Count; row++)
{
dataRange = (Excel.Range)mySheet.Cells[row, 1];
if (dataRange.Value2 == null)
{
break;
}
if (row > 400)
{
break;
}
terms[row - 1] = String.Format(dataRange.Value2.ToString());
numTerms++;
}
Console.WriteLine();
lista.Close(false, path, null);
for (int i = 0; i < numTerms; i++)
{
Excel.Workbook workbook = application.Workbooks.Open(pathExcels + terms[i]);
workbook.RefreshAll();
Console.Write(i + 1 + ". Actualitzant... " + pathExcels + terms[i]);
System.Threading.Thread.Sleep(timeout);
Console.Write(" ok");
Console.WriteLine();
application.DisplayAlerts = false;
workbook.Save();
workbook.Close(false, pathExcels + terms[i], null);
workbook = null;
}
Console.WriteLine("**********************************************");
Console.WriteLine("*************** FI *********************");
Console.WriteLine("******* ACTUALITZACIO DE DOCUMENTS ***********");
Console.WriteLine("**********************************************");
Console.WriteLine("**********************************************");
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
}